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

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.

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