search

CartoDB / odbc_fdw

PostgreSQL Foreign-data Wrapper for ODBC
Other
67 stars 22 forks source link

Spatial data in SQL Server through TDSFree ODBC driver #58

Open jgoizueta opened 7 years ago

jgoizueta commented 7 years ago

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 commented 7 years ago

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