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