How to use MS SQL Server 2012 spatial database in postgresql using foreign data wrapper?
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
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
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)