Using MS SQL Server Spatial database in PostgreSQL using foreign data wrapper?

Question

How to use MS SQL Server 2012 spatial database in postgresql using foreign data wrapper?

Answer 1

You can use Spatial Manager Desktop to export from SQL Server 2012 Spatial to PostGIS.

There is currently a free beta version ,you can download it here: http://www.spatialmanager.com/downloads/

If you need help on this feel free to tell me.

Disclaimer: I work for Opencartis (Spatial Manager developers)

Answer 2

I tried using OGR_FDW but I didn't manage to make It to read the spatial data correctly. Then I tried using TDS_FDW which is a non-spatial Foreign Data Wrapper.

I'm using MS SQL Server 2008 R2 on a Windows Server and PostgreSQL 9.5 + PostGIS on a Ubuntu Server.

Install FreeTDS and UnixODBC

sudo apt install unixodbc unixodbc-dev freetds-dev sqsh freetds-bin tdsodbc libsybdb5 freetds-dev freetds-common -y

Set the env

Updated ~/.profile
~$ export ODBCINI=/etc/odbc.ini
~$ export ODBCSYSINI=/etc
~$ export FREETDSCONF=/etc/freetds/freetds.conf

Configure TDS files

sudo vim /etc/freetds/freetds.conf
  [sqlserver_win]
  host = 192.168.0.10
  port = 1433
  tds version = 7.1

sudo vim /etc/odbcinst.ini
  [FreeTDS]
  Description = TDS driver
  Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

sudo vim /etc/odbc.ini
  [sqlserver_win]
  Driver = FreeTDS
  Server = mssql_host
  Database = dbname
  Port = 1433
  TDS_Version = 7.1

Install TDS_FDW

sudo apt install git
git clone https://github.com/tds-fdw/tds_fdw.git
cd tds_fdw
PATH=$PGHOME:$PATH make USE_PGXS=1
sudo PATH=$PGHOME/bin:$PATH make USE_PGXS=1 install

Enable the extension

psql -U postgres -h localhost
     CREATE EXTENSION tds_fdw;

Create the Foreign Server

CREATE SERVER mssql_db FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'mssql_host', port '1433', database 'dbname');

CREATE USER MAPPING FOR postgres server mssql_db OPTIONS (username 'username', password 'userpassword');

here is the trick

Create the foreign table, get the geometry as string, copy It to your local database, add a primary key and spatial index

CREATE  FOREIGN TABLE ms_table_name(
col1 varchar (64), col2 date, geometry geometry
) SERVER mssql_db_server OPTIONS (query 'SELECT col1, col2, cast(geometry.STAsText() as varchar(max)) "geometry" FROM ms_table_name', 
row_estimate_method 'showplan_all');

 CREATE TABLE pg_table_name AS (select * from ms_table_name);

 ALTER TABLE pg_table_name ADD oid serial PRIMARY KEY;

 CREATE INDEX pg_table_name_gix ON pg_table_name USING GIST (GEOMETRY);

You have to copy It locally otherwise you won't be able to use your spatial table in any GIS client (ie. QGIS). You may create a job to regularly update the local table.

The only problem I have found with that approach is that I can't read geometries with long chains of coordinates, there is string limitation somewhere I can't find