Spatial data in SQL Server through TDSFree ODBC driver

[jgoizueta]

Using the TDSFree ODBC driver, SQL Server spatial data types (geometry, geography) are currently mapped to bytea but they appear as NULL values.

It the geometry columns are converted to WKB format with the SQL Server STAsBinary() function (by means of the sql_query option), they will appear as ODBC type -3 (VARBINARY) which we currently don't support and it will cause an error.

As a workaround, the sql_query option can be used to cast the data and represent it as text. We can either use the WKT form using the SQL Server STAsText() function or we can render WKB as hexadecimal text.

There's also a caveat when using STAsText(): its result is of type Varchar(max), which is presented by the driver as Varchar(0) which isn't currently supported because of the zero length.

So, to import a geometry column geom as WKT we can use this expression in sql_query:

CAST(geom.STAsText() AS text) AS geom_wkt

Or, to obtain hex WKB:

CONVERT([text],REPLACE(CONVERT([varchar](max), geom.STAsBinary(), 1),'0x',''),1) as geom_wkbhex

Note that quotes in the expression will need to be doubled to be passed in a OPTION.

For comparison, note that PostGIS geometries are mapped to text type by the PG driver, and they are represented as hexadecimal WKB.

[jgoizueta]

Note that point geometries can be imported simply by selecting the coordinates in sql_query with: geom.STX As X, geom.STY As Y.

[robe2]

Might be too late to be commenting on this, but for any one else reading this if you are looking to read spatial geometries from sql server as PostGIS geometries, ogr_fdw https://github.com/pramsey/pgsql-ogr-fdw might be a better option. ogr_fdw is a wrapper around the GDAL spatial data abstraction library that can expose spatial columns as PostGIS geometry (if you have postgis installed) or bytea if you don't have postgis installed.

To use with SQL Server you would do something like this:

CREATE EXTENSION IF NOT EXISTS ogr_fdw;
CREATE SERVER mssql_test FOREIGN DATA WRAPPER ogr_fdw OPTIONS ( datasource 'MSSQL:server=myserver,1433;database=mydb;UID=user;PWD=password;driver=FreeTDS;Tables=Clientdb,Table2,Table3', format 'MSSQLSpatial');

CREATE SCHEMA IF NOT EXISTS staging;
IMPORT FOREIGN SCHEMA ogr_all FROM SERVER mssql_test INTO staging;

Also note that now that Microsoft provides ODBC drivers for Linux, you might be better off using those drivers especially for newer versions of PostgreSQL e.g. driver=ODBC Driver 13 for SQL Server