How to use MS SQL Server 2012 spatial database in postgresql using foreign data wrapper?
2 Answers
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
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)
-
Does this do a full data copy of the MSSQL DB to PostGIS, or does it work via foreign data wrappers?– til_bApr 16, 2014 at 11:47
-
1You could also use ogr2ogr or FME from safe.com to do data import/export operations. There isn't even a non-spatial FDW wrapper for SQL server, let alone a spatial one. wiki.postgresql.org/wiki/Foreign_data_wrappers Apr 18, 2014 at 1:17
-
Dear @Borja Arrue Is there any tutorial to connect postgres and mssql using spatial manager desktop?– VikramMay 6, 2014 at 4:12