Pulling Spatial Data in Geography Format From MS SQL Server 2008 Using ogr2ogr

Question

Can the MSSQLSpatial driver support geography fields queried from MS SQL Server 2008 as spatial data?

Background

I have been using unixODBC on an Ubuntu 16.04 machine with ODBC Driver 17 for SQL Server installed. When I enter this command to test my connection:

ogr2ogr -f "GeoJSON" output.json "MSSQL:server=[SERVER_NAME];database=[DB_NAME];tables=dbo.[TABLE](Shape);driver=ODBC Driver 17 for SQL Server;uid=NodeClient;pwd=NodeClient" -overwrite -sql "SELECT TOP 10 ID, Shape.STAsBinary() from dbo.NfhlFloodHazardAreas"

The resulting GeoJSON file contains a normal field called Shape filled with a truncated binary string, and yet the feature's geometry is null.

The following output.json file demonstrates the issue:

{
"type": "FeatureCollection",
"features": [
{ "type": "Feature", "properties": { "": "010300000001000000A1000000B4F99DDAC31A58C020F5AAEC98B03C403C4F588CC31A58..." }, "geometry": null },
{ "type": "Feature", "properties": { "": "01030000000100000078000000080DF4D1EE1E58C090CB64C983B43C408C96E101EF1E58..." }, "geometry": null },

Past Questions

I have read an almost identical issue by another user:

Creating Shape Files from SQL Server using Ogr2ogr

But mine differs from theirs in that my data is stored purely as one datatype, POLYGON, and as a BLOB. I tried applying the top answer's ogr2ogr query, but it resulted in the same problem.

I presume that answer was intended to be used against geometry fields only, but then why does the official documentation for the MSSQLSpatial driver mention support for MS SQL Server's spatial fields, which include both geometry and geography data?

Is there any way to make the MSSQLSpatial driver recognize the geography data as spatial data, and not an ordinary column on the table?

Full Debug Output

root@775a08d4796b:/opt/sample# ogr2ogr -f "GeoJSON" output.json "MSSQL:server=[SERVER];database=[DATABASE];tables=dbo.[TABLE];driver=ODBC Driver 17 for SQL Server;uid=NodeClient;pwd=NodeClient" -overwrite -sql "SELECT TOP 10 ID, Shape.STAsBinary() from dbo.[TABLE]" ODBC: SQLDriverConnect(server=[SERVER];database=[DATABASE];driver=ODBC Driver 17 for SQL Server;uid=NodeClient;pwd=NodeClient) GDAL: GDALOpen(MSSQL:server=[SERVER];database=[DATABASE];tables=dbo.[TABLE];driver=ODBC Driver 17 for SQL Server;uid=NodeClient;pwd=NodeClient, this=0x13aeac0) succeeds as MSSQLSpatial. GDAL: GDALDriver::Create(GeoJSON,output.json,0,0,0,Unknown,(nil)) MSSQLSpatial: ExecuteSQL(SELECT TOP 10 ID, Shape.STAsBinary() from dbo.[TABLE]) called. OGR_MSSQLSpatial: Using column ID as FID for table SELECT. Warning 1: The output driver does not natively support Binary type for field . Misconversion can happen. -mapFieldType can be used to control field type conversion. GDALVectorTranslate: 10 features written in layer 'SELECT' OGR_MSSQLSpatial: 10 features read on layer 'SELECT'. ODBC: SQLDisconnect() GDAL: GDALClose(MSSQL:server=[SERVER];database=[DATABASE];tables=dbo.[TABLE];driver=ODBC Driver 17 for SQL Server;uid=NodeClient;pwd=NodeClient, this=0x13aeac0) GDAL: GDALClose(output.json, this=0x1427970) GDAL: In GDALDestroy - unloading GDAL shared library.

Answer 1

Telling ogr the tables in the connection string made no positive effect for me.

The solution for my issue, which looks to be the similar to yours, was to make an explicit cast of the shape field.

    SELECT name, GEOMETRY::STGeomFromWKB(shape.STAsBinary(), 26912) from db.owner.table

The geometry field was then picked up by ogr and I was able to export to shapefile and to a postgis database.

Edit

I ended up removing this double conversion as it felt wrong. I think my solution was to switch dialects to OGRSQL setting the GEOMETRY_NAME and ogr started to pick it up.