How to use non-native spatial data from MS SQL Server with ogr2ogr?

Question

I have some tables in MS SQL Server (2012) with WKT string columns. I want to export these data to a MapInfo tab file.

Currently I am using the following command:

ogr2ogr -f "MapInfo File" testsql.tab "MSSQL:Server=etc." -sql "select geography::STGeomFromText(FACE,4326),NAME from TABLE" -a_srs "EPSG:4326"

Where FACE is a string column containing WKT geometries.

This gives some problems, because the coordinates are switched. This is supposedly due to a change between SQL Server 2008 and 2012. Outputting it to WKB works better.

I was wondering if it is possible that ogr2ogr uses the WKT column directly without converting it to the MS SQL Spatial type first?

Answer 1

Have you tried accessing the database directly from MapInfo Professional or do you not have access to MapInfo Professional?

You could try:

• create a view that selects all the columns from your table, uses the STGeomFromText to convert the WKT to a SQL Server spatial type
• create an ODBC data source that connects to the database
• create the MapCatalog in the database using EasyLoader (you'll find this tool in the Tool Manager in MapInfo Professional)
• use MapInfo Pro to make the DBMS table in this case your new view) mappable
• open the view into MapInfo Professional

I have shared the slides from a session on how to use SQL Server 2008 with MapInfo Pro. You can find the slides here: MI Pro and SQL Server 2008 or on Slideshare