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)

  • Does this do a full data copy of the MSSQL DB to PostGIS, or does it work via foreign data wrappers? – til_b Apr 16 '14 at 11:47
  • 1
    You 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 – Paul Ramsey Apr 18 '14 at 1:17
  • Dear @Borja Arrue Is there any tutorial to connect postgres and mssql using spatial manager desktop? – Vikram May 6 '14 at 4:12

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
  host =
  port = 1433
  tds version = 7.1

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

sudo vim /etc/odbc.ini
  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
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.