1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.