MapInfo Pro

Expand all | Collapse all

SQL server tables struggling to map

  • 1.  SQL server tables struggling to map

    Posted 12-20-2019 05:38
    Hi, I now have Mapinfo (17.0.4) connected to SQL server. Mapcatalog is there, thought everything was resolved.

    But I'm finding that tables are only mapping intermittently. I can browse the tables (about 97k records each), but when I put them on a map, 80%+ of the time it will only map perhaps the first 100 rows.

    Oddly, if I produce a query of all records with SQL select, that seems to map reliably- but that takes about 5 minutes.

    I'm on a new Win 10 high spec laptop, with an ethernet connection, so can't see any issues there.

    I presume that something isn't set up efficiently in either MI or SQL. Any suggestions appreciated!

    Martin

    ------------------------------
    Martin Burroughs
    Oldham Council
    ------------------------------


  • 2.  RE: SQL server tables struggling to map

    Posted 12-20-2019 05:50
    Hi Martin,

    Are you creating 'Live' or 'Linked' tables?  If you create the .tab file as a linked table then Pro should create the .dat, .map, .id files like a 'normal' mapinfo table.  'Live' may also be trying to only render the records in the current map view so possibly try zooming out to a larger area. maybe.

    HTH

    Nick

    ------------------------------
    Nick Hall
    Mapchester LTD
    nick.hall@mapchester.co.uk
    ------------------------------



  • 3.  RE: SQL server tables struggling to map

    Posted 12-20-2019 06:47
    Thanks Nick,
    I was going live cached. Just tried linked, takes a couple of minutes to set up but does work.

    For one of the tables I have, it's very stable so that's fine as a solution (thanks!). The other though will change completely every time it's accessed, so not ideal but it'll do as a stopgap.

    The map view zoom doesn't seem to affect the behaviour, just always shows the first few records.

    I suspect SQL server is throttling somehow, hopefully someone will have more info.

    ------------------------------
    Martin Burroughs
    COUNTY BOROUGH OF OLDHAM
    .
    ------------------------------



  • 4.  RE: SQL server tables struggling to map

    Posted 12-23-2019 04:34
    You typically experience this when some of your data has been marked as invalid by SQL Server. An invalid spatial record isn't added to the spatial index and therefor MapInfo Pro won't pick it up when you access the table using Live Access.

    You can ask MS SQL Server to make these objects valid again using the method MakeValid():
    Update dbo.mytable Set SP_GEOMETRY = SP.GEOMETRY.MakeValid()

    You should also be able to visually inspect the records with invalid spatial objects using a SQL Statement like this which you can execute via the Expert Mode interface when opening a DBMS table:
    SELECT * FROM dbo.mytable WHERE SP_GEOMETRY.STIsValid() = 0

    Remember to open the table as linked.

    There is a longer discussion on StackExchange about alternatives to using MakeValid() as it might change your spatial objects in a way you don't like.

    ------------------------------
    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data
    ------------------------------