MapInfo, MapExtreme, EasyLoader and Spatial Database

[Alex Leith]

Hi all

I'd like to confirm what I think is a limitation of the EasyLoader's interactions with a spatial database.

If I upload a dataset to SQL Server 2008 R2 using EasyLoader in the GDA94 lat/lon coordinate system, I specify using EasyLoader that I want it to 'auto select' geography/geometry, hoping that it will select geography. Invariably it doesn't and I end up with lat/lon in a geometry datatype.

My real issue is, though, that these tables cannot be visualised in Workspace Manager, and there fore Exponare. In further testing today, it turns out that if you try to open this table with mapInfo as 'Live Access' without a cache, it doesn't display any geometry either, though it does load the table (you can explore it in browser).

Does anyone else have a different understanding of the facts above?

I have had success uploading data with FME and making it mappable with MapInfo then loading it as a Live Access table in MapInfo, this is presumably the method that Workspace Manager is using by default?

Regards,

Alex

[Mike Osbourn]

Alex,

 

This is partially dependent on which version of Exponare you are trying this on as well as using live vs. linked tables.  I have found that “live” tables (unless they are extremely small) put a significant performance hit on both Exponare and MI Pro .  Try linking the tables and see if they work for you.

 

Mike

--
You received this message because you are subscribed to the
Google Groups "MapInfo-L" group.To post a message to this group, send
email to mapi...@googlegroups.com
To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en

[Alex Leith]

Hi Mike

We are on Exponare 5. I would like to use live tables, so that I'm not duplicating data everywhere! I am confident that using linked tables would work, though I should start testing it.

My questions are also around using everything together, i.e., what is the deal with easyloader not uploading geography? And is that actually necessary? Do others have success reading geometry datatypes in Exponare (I suppose that all that just works if you use linked tables)?

Last question: performance is pretty poor currently using Exponare 5 and live tables. These tables don't have a spatial index, is this the issue, or is it always going to be slower than flat files?

Regards,

Alex

[Eric Blasenheim]

Alex,

I think I can explain some of this but maybe not all without more information.

 

First of all, SQL Server 2008 and above offers these two types, geography for just Long/Lat data and Geometry for anything.  We found that Geography had some interesting contstraints. If the "IsValid" check they do whenever you create or modify a geography fails, the insert/update fails. We found that much data from MapInfo Native TAB files failed. This did not occur with Geometry.

 

Our implementation therefore biased towards geometry. In Professional, if the data is Long/Lat you can choose and it may be that Easyloader choices are a bit different.

 

Later on we found some other disturbing things which was that Geometry creation/edit also silently called IsValid() and if that failed, might not add that object to the spatial index. THis can have many confusing issues for a Professional customer because if we access the record by key, you get the geometry whereas if we access via the spatial index you don't. To help solve this issue, easyloader() will itself call IsValid() and if that fails will make the MakeValid() function. This is arguable but seemed to solve most of these issues.

 

LInked tables generally access the data for download by key. Live access, when using the map window, access spatially. So that may explain what you are seeing.

Some of these changes were incremental so may be different based on which version of Easyloader you are using.

Also R2 has changed some things. The errors are more informative and you can call IsValid, MakeValid on Geographies but we have not updated Easyloader yet for that .

Also as a Professional or MapXtreme user, storing in Geogrpraphy offers no value. You may have server side reasons but not for our software.

 

I don't have access to a server right now but can check when I return to the office.  Again, versions may be important.

I hope that explains a few things.

 

Eric Blasenheim

Pitney Bowes Software.

[Alex Leith]

Hi Eric

Firstly, thanks for your detailed response. I look forward to fleshing this out and getting to the bottom of it all.

It is interesting to read the technical issues with EasyLoader, and it raises the question of how OGR2OGR or FME (not sure if they use OGR or in-house implementation) do it, as they don't seem to have the same issues.

I'm very interested to know that live access and linked access use different methods of retrieving the data, since I think that there are clear times when live access fails, i.e., when I have stored lat/lon in a geometry datatype. I really need to do more rigorous testing of the multitude of combinations to confirm this. It doesn't explain why MapXTreme (workspace manager and exponare) and sometimes MapInfo wont display any data ... even though SQL Server and QGIS will display the dame data...

I had troubles uploading some layers at all with the version of EasyLoader bundled with MapInfo 11.5.1. I tried EasyLoader 11 and 11.5 and that would upload all data, but generally would NOT upload it as geography (maybe the error I was receiving was due to the issue you mention regarding SQL Server's IsValid() check).

The reason why I was storing data in geography was that this was the advice I received from Insight GIS via Tonkin. The consensus from them was (from my understanding) that geometry wasn't handled well by MapXTreme, leading to Tonkin using geography always.

For versions, we are using:

• SQL Server 2008 R2 on Windows Server 2008
• Exponare 5, so MapXTreme 7.0.0 and related Workspace Manager
• MapInfo 11.5.1
• Easyloader bundled with the above, not sure of the version, and 11 and 11.5
• FME 2012 SP3 and FME 2013 beta
• QGIS 1.9 (viewing only).

For clarification, I have had the most success by getting data into SQL Server as follows:

1. Use FME to create MI_PRINX and MI_STYLE and upload data in GDA94 to SQL Server, geography datatype, no indexes (yet!)
2. Use MapInfo to 'make table mappable'

Data can be viewed, edited etc. in either of the three methods MI uses. Performance in Exponare is extremely poor, even using the recommended settings in the MWS (which need to be manually added after each change to the workspace...) Next step is to experiment with manually creating a spatial index to see whether that impacts performance.

Thanks for looking into this for us! Let me know what specific questions I can answer to further clarify.

Regards,

Alex

[Alex Leith]

Hi Morgan

Thanks for the response and clarification.

Isn't it essentially wrong to store lat/lon in geometry? Especially if one is intending on using built in sql server functions such as distance and area calculations. I'm not meaning to be accusational here, I'm new to spatial databases, but it seems to make sense and these guys essentially agree: http://social.msdn.microsoft.com/Forums/en/sqlspatial/thread/9cd22aaa-1a93-4139-b4cf-c93cdf7ca0ba.

Storing lat/lon as geometry seems to be done only because SQL Server can do the makevalid() functions, right?

What is the reason that you use GDA/WGS, Morgan? Is it because your data spans MGA zones or for a more technical reason?

Would it be possible for you to provide more detail regarding further 'tweaks and customisations', in particular for the MI/Exponare environment this forum is interested in?

Regards,

Alex

On Thu, Oct 25, 2012 at 4:48 PM, Morgan Ellingham <ellm...@internode.on.net> wrote:

Hey Guys,

Morgan from Tonkin Consulting here. Just a quick one and a minor correction, we have had the best results with SQL Server and Exponare by using data that is in Lat/Long on either GDA94 or WGS84 and storing it as a Geometry data type.

There are numerous other tweaks and customisations which need to be undertaken to get it all working correctly and optimally, but ultimately, they are dependant on the environment that you are implementing within.

Hope this helps somewhat.

Cheers...

Morgan

--

You received this message because you are subscribed to the
Google Groups "MapInfo-L" group.To post a message to this group, send
email to mapi...@googlegroups.com
To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en

--
Alex Leith
e: alexg...@gmail.com
m: 0419189050

[Morgan Ellingham]

Hi Alex,

No you're absolutely right. Having Lat/Long with Geometry doesn't make any geodetic sense and it will make spatial operations nigh on impossible, but that is what worked best for us at the time speed wise with Exponare. Those clients were only using viewing and attribute query functionality from SQL at the time so it didn't really matter for those projects. MapInfo Pro, esri and QGIS etc seem to cope with any innumerate number of peculiar datums, data types and coordinate systems but Exponare was fussy. I guess that is a difference between a serious desktop application and a corporate GIS. We probably made an assumption (we all know the saying about them!) that it was the MapXtreme engine struggling with it as we have had similar geodesy issues with tile layers such as Bing and Google - some more direction here from Pitney Bowes would be great.

We just finished implementing a SQL Server site here in Adelaide that is storing all of their data in the traditional MGA/GDA set up. We used Geometry and successfully implemented numerous spatial operative stored procedures and views. It all works quite well and the speed in Exponare is OK but it will need to be managed carefully. We think a mix of live reads, cached .TAB files and linked tables will work best in their case. Provided there is a business rule in place that SQL is the "god" then the duplication of data is negligible.

We use GDA and/or WGS as the datum as that is what the vast majority our clients data is based on - contrary to some popular thought those datums are so close they're almost the same, I think there are a couple of decimals different in the flattening.  It's the coordinate system, not the datum, that is seemingly throwing some oddities with the MapXtreme workspace manager. Using MGA does slow Exponare down in our preliminary testing. As do a couple of other Cartesian systems. Maybe MapXtreme is just old school and prefers using angles rather than numbers. Then again the most recent two sites using MGA has worked OK. Not sure but there are many, many things that go into how a enterprise system behaves and responds. Would be keen to see some of your findings once you get it up and running.

We did send InsightGIS some more in depth information on how to get this to work. There are some variables for the MARS and DLG settings in the MWS workspace that we think are required as well as some security settings in SQL Server itself. Sorry I am being so vague but there is a whole bunch of other stuff that really is environment dependent. A bit like a mechanic trying to diagnose a funny noise on a car remotely.

At a really high level here are some quick points that I posted on ALGMUG a while back which might help:

SQL can be a bit particular with field names when up loading using the Easyloader from Pro – you may discover some naming convention errors

·         Make sure there is an ODBC connection to the SQL Server Spatial database

·         In the workspace manager, open the tables through the ODBC connection

·         If not working also ensure the MWS workspace file has the appropriate DNS connection strings

·         Ensure Exponare has the appropriate levels of security to SQL Server – including machine names and the like

·         Make sure all tables in SQL have been made mappable via the MAPINFO.MAPCATLOG ta ble (usually done automatically with the Easyloader) but can be skipped if using other methodology

·         General syntax for joint data binds and queries et al need to be understood by SQL Server

Cheers Alex, hope ti goes well. The rewards at the end are worth it!

Cheers...

Morgan

On Wednesday, October 24, 2012 10:19:07 AM UTC+10:30, Alex Leith wrote:

[Alex Leith]

Hi Morgan

some more direction here from Pitney Bowes would be great.

Very true! 

We think a mix of live reads, cached .TAB files and linked tables will work best in their case. Provided there is a business rule in place that SQL is the "god" then the duplication of data is negligible.

I see... I guess I'm going to have to get over my fear of data duplication! At least I can store cached/linked tables in a location only accessible to exponare, and not on the corporate server. It seems that linked tables are the way to go for MapInfo users too, for performance reasons (though cached live tables are ok).

Using MGA does slow Exponare down in our preliminary testing.

Interesting. We have a large Exponare installation, with a lot of data in it, and all of it MGA projected (with appropriate bounds set, of course). The only reason I started using GDA/WGS lat/lon (and I appreciate they are essentially the same, I was testing to see if MapXtreme favoured one over the other!) was that I couldn't, well, can't get Workspace Manager and therefore Exponare to display data when it is stored with geometry using live tables. This is true of GDA lat lon or MGA projected coordinates.

There are some variables for the MARS and DLG settings 

Yep, I did read to documentation ;-) and we also have your comments in our doco from ALGMUG, so I felt well prepared when starting out! As I said earlier, I got stuck at trying to view live tables in Workspace Manager... So I started using GDA, and encountered EasyLoader issues trying to convince it to write geography...

Now, the end result of my research is that I can:

• write to SQL Server with FME including MI_PRINX and MI_STYLE (let me know if you want to know how I got the styles in per-feature!) in either MGA (geometry) or GDA (geography)
• make the tables mappable using MapInfo
• add tables to Workspace Manager and have them be visible if they are geography (live) or geometry (linked)
• maintain acceptable performance (must be linked!)

It's a bit of madness, really. And now I have a further question: how frequently do linked tables update in Exponare? Is there a way to force it? And also, the connection strings are definitely incorrect in some of my test layers, and they work fine ... so how do I know when they fail to update?

Thanks again for your replies, and, if I could reiterate, a little guidance from PBS would be nice, including documenting the inability for MapXtreme 7.0 to view geometry from live tables.

Regards,

Alex

[Eric Blasenheim]

Alex,

 

I will be happy to try and provide the answers to your questions. It would be easier if you could create a list. 

As for MapXtreme, I have tried both 7.0 and 7.1 on a geometry table with live access and was able to display the map in Workspace Manager. As I think I said, geometry is a bit preferred. Are you sure it was geometry you had issues with?  I have fewer of those tables.

 

If you have some sample of data that you find fails, please feel free to pass it along or contact support. Our offices are closed due to Hurricane/post-tropical storm Sandy and my server is there and live access from home is well, slow.  But it displayed.

From your earlier post it did sound like something went wrong with the upload but I don't know what. 

 

Eric

 

On Tuesday, October 23, 2012 7:49:07 PM UTC-4, Alex Leith wrote:

[Alex Leith]

Hi Eric

I have tested a lot, and I definitely have issues displaying geometry with live access with MapXtreme 7.0. In fact, I have issues displaying the features in MapInfo, too, though the table comes through and can be viewed in Browser.

Anyhow, it is very strange. I just checked this morning and have an issue in MapInfo opening a geometry table. That table opens fine in QGIS and displays fine in SQL Management Studio...

On testing again this morning, I can't view any MGA projected, geometry layers in Workspace Manager. If you'd like to test with our data, send an email to me at ale...@gcc.tas.gov.au and I'll send you a zip.

I'll consider some concise questions and reply soon. Thanks for your time.

Regards,

Alex

--

You received this message because you are subscribed to the
Google Groups "MapInfo-L" group.To post a message to this group, send
email to mapi...@googlegroups.com
To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en

[Eric Blasenheim]

Alex, a few quick things to try.

First of all make a very simple Native TAB file (a few records with simple geometries and upload via Easyloader chossing Geometry type. See if that works fine in Pro and MXT Workspace Manager. It should.

 

From both the description and the error message, the issue definitely seems to be the spatial index was not successfully created because some objects are not "valid". If you wish, inside Management Console you can run a query on the table for the value of ST_IsValid . It should show you which geometries are valid and which are not.  As I said, if they are not valid I believe SQL Server will not create the spatial index on the geometry.

 

This corresponds with the behavior you mentioned:

1) not seeing geometries in Workspace Manager. MapXtreme ALWAYS does a spatial query in the map (except maybe in one case). So not seeing the geometries makes sense with the index being bad.

2) Pro has the same behavior BUT sometimes you start seeing data after opening the browser with cache on. The reason for this is that while the spatial query fails, the by key query that the browser does succeeds and Pro adds these to the cache. Then they show up in the map.

3) The error your posted specifically says that MapXtreme attempted to create a .NET instance of SQL geometry (we use their .NET objects client side) and that failed. The error says you (or us)  should call MakeValid().   Note that in the current version of Easyloader if the ST_ISValid() check fails, we do attempt to call MakeValid(). Maybe that is also failing. 

 

I would send you an email but it looks like my Google Groups viewer is hiding your email address. Mine is eric.bl...@pb.com  (That's my name(s) separated by a dot at pb.com if Google hides it again)

 

Eric Blasenheim

Pitney Bowes Software

[Alex Leith]

Hi Eric

Apologies for the delay.

    Make a simple table and upload it.

That did work. I can view a simple table. But that doesn't seem to have anything to do with spatial indexes. An illustration of this is:

1. That simple MGA table loads with or without a spatial index
   
2. A different MGA table with 100% valid geometry and no spatial index doesn't load
   
3. The same table as above with lat/lon geography and no spatial index does load.
   

I'm going to do more research.

Regards,

[Eric Blasenheim]

Alex,

 

I did forget that a "simple table" needs to have more than 50 records.  Long story but Professional does read the first 50 by key. So you need more than 50 for the spatial index to really kick in. Sorry for that.

 

How do you know the geometries are "valid"?

What is the coordinate system of the non-LatLon table?

 

Eric

[Alex Leith]

Hi Eric

Ah, I see.

So I created a simple table with more than 50 entries (60 or so). It loads fine in workspace manager. It also loaded fine when I deleted the spatial index. It also worked in MI without a spatial index.

I know that the geometries are valid because I did a select on the table in SQL Management Studio with [SP_GEOMETRY].STIsValid() and inspected the results.

The coordinate system I use is GDA94 MGA Zone 55 with bounds of xmin: -500000, xmax: 1500000, ymin: 5000000, ymax: 7000000. I have also been testing with GDA94 (lat, lon).

It gets more confusing. For an example table I have here, with 29 entries (the LGAs of Tasmania) I get the following results:

1. uploaded with FME, made mappable with MI 11.5, no spatial index, MGA coordinate system = can't view in workspace manager
2. uploaded with FME, made mappable with MI 11.5, no spatial index, GDA coordinate system = can view
3. uploaded with EasyLoader 11.5, spatial index, MGA coordinate system = can view
4. same table as 3 with spatial index removed = can view

All of the above tables work in MI, which agrees with what you say, i.e., you don't need a spatial index in MI. That doesn't explain the workspace manager (and therefore exponare) differences. In terms of differences, I can't really say what they may be in the above tables, most importantly 2 and 4 that make one visible and the other not. The MapCatalog entries for the three are attached. (LGA_GDA is ll, LGA_MGA is MGA, LGA_MGA_el is MGA uploaded with easy loader.

So, since EasyLoader seems to be creating tables that can be read, part of my issue is resolved. What is left is the reading of tables that have been made mappable with MI, rather than by EasyLoader...

Regards,

Alex

[Alex Leith]

Hi all

Ok, another question.

Is it possible to convince EasyLoader to add an SRID when it uploads data? All the tables that it uploads for me get a 0 for SRID. I know that it stores a coordinate system string in the MAPCATALOG table, but this isn't read by other software.

Regards,

Alex

[Peter Horsbøll Møller]

Alex,

Can you check if the EasyLoader projection file has an EPSG code for your projection?

At least that's where EasyLoader will search for the EPSG code to insert with the spatial data.

The projection file is stored in the Tools folder and is named MapInfow.prj

Peter Horsbøll Møller
Pitney Bowes Software

2012/11/8 Alex Leith <alexg...@gmail.com>

[Alex Leith]

Hi Peter

We use custom bounds, not sure if that has anything to do with it. Coorsys Bounds Manager says the following:

And in the MAPINFOW.PRJ file there is a line:

"MGA Zone 55 (GDA 94)\p28355", 8, 116, 7, 147, 0, 0.9996, 500000, 10000000

But in SQL Server if I do a SELECT [SP_GEOMETRY].STSrid FROM [GIS_TEST_DB].[dbo].[Property] then I get 0. (I know I can set the Srid, and I have, and it fixes things... It'd be nice for this to get put in in the first place, though!)

Regards,

Alex

[Alex Leith]

Hi All

Finally I have a reproducible bug report!

Using this simple table: https://dl.dropbox.com/u/2326842/GCC/testTable.zip I can upload with EasyLoader and it is visible Workspace Manager as a live table. It is NOT visible in MapInfo.

If I fix the SRID by setting it to 28355, like this:

update GIS_TEST_DB.dbo.testTable set SP_GEOMETRY.STSrid = 28355;

then it can no longer be rendered by Workspace Manager. but it CAN be viewed in MapInfo!

Very Strange!

Can anyone else reproduce this?

Software I'm using is:

• SQL Server 2008 R2
• MI 11.5.1
• Workspace Manager MapXtreme 7.0.0.200

Regards,

Alex

[Eric Blasenheim]

Alex,

Sorry for the delay.

I got your data, opened it fine in Professional, used the Easyloader from versions 11.5(1), uploaded to SQL Ser ver 2008 (not r2).  The SRID was there, 28355  and live access worked fine but it does not view in Workspace Manager 7.0. I will be trying 7.1 soon.

So sort of the opposite of your experience except that the SRID was installed by Easyloader. I cannot produce the no SRID case but I can reproduce the not rendering in MXT.

 

Eric Blasenheim

Pitney Bowes Software

[Alex Leith]

Hi Eric

I think you are experiencing the same result as me. To be sure, set the srid to 0 and I reckon it will be viewable in MXT and not in MI.

What version of EasyLoader were you using? Does it need to be run out of a certain location? 

I was using version 11.5 of EasyLoader, run out of a folder on my desktop. This version has been more reliable than the one shipped with MI 11.5.1.

Regards,

Alex

[Eric Blasenheim]

I was using the Easyloader that is installed with 11.5 but was surely upgraded to 11.51. When you say you run out of a folder on your desktop, does that folder have the MapInfow.prj file in it? That could explain the lack of SRID. Easyloader gets all the EPSG codes from that file which normally exists in the Easyloader install file under tools.

 

Eric

[Alex Leith]

Hi Eric

No, it does not! That does explain it then... So the SRID issue is sorted.

I should track down the issue I was having with the installed EasyLoader, which I believe was doing things differently that the 11.5 one from the web... Not sure now.

Still, I'm glad that you are able to reproduce my inability to view geometry in workspace manager.

Regards,

Alex

[Mathew Linnane]

Hi Guys,

 

Is Easyloader placing a value for your problematic tables in mapinfo.mapinfo_mapcatalog.symbol?

 

If the SYMBOL column for your problematic table is vacant you may not be able to read the layer from the DB with the Workspace Manager.

 

Copy paste the value from the MI_STYLE of your source (problematic) table (something like “Pen (1, 1, 6316287) Brush (2, 16777168, 16777215)”

 

Refresh the DB and let me know if the layer can now be read by Workspace Manager.

 

Cheers.

 

Regards,

 

Mathew Linnane 

Pitney Bowes Software

And in the MAPINFOW.PRJ file there is a line:

 

2012/11/8 Alex Leith <alexg...@gmail.com>

Hi all

 

 

On Wed, Oct 31, 2012 at 2:49 AM, Eric Blasenheim <eric.bl...@pb.com> wrote:

[Alex Leith]

Hi Matthew

What happened when you tried it?

It didn't change anything for me. I have a style set per-feature already, if that makes a difference.

Regards,

Alex

[Alex Leith]

Hi Eric

Did anything come of the issue with MXT not rendering layers? I have spoken with Morgan from Tonkin Consulting, and they can render MGA projected layers, albeit in a different zone...

It would be great to get a solution to this and enable us to add live tables to Exponare.

Regards,

Alex

[Alex Leith]

Hi all

This is a thread conclusion email with a nice, reasonable workaround to fix my final issue.

The issue was that tables wont render in Workspace Manager, and therefore
Exponare (both use MapExtreme) if they are MGA projected and have an SRID set.
This is due, I believe, to a check being undertaken to compare the coordinate
string from the table's MAPCATALOG entry with the MapExtreme conversino of the
SRID to a coordinate string. Apparently, sometime in the late 2000s, MI changed
the datum in the coordinate string for MGA projections from 33 (GRS80) to 116
(GDA94). Anyhow, it turns out MapExtreme still uses 33...

So, the fix is to open up the MAPCATALOG and change the 116 to 33, like the
following:

Earth Projection 8, 116, "m", 147, 0, 0.9996, 500000, 10000000 Bounds
(-500000, 5000000) (1500000, 7000000)
to
Earth Projection 8, 33, "m", 147, 0, 0.9996, 500000, 10000000 Bounds
(-500000, 5000000) (1500000, 7000000)

Hopefully this may help some folks in the future!