Import SQL Server Spatial Data into PostGIS

Is there a free tool to import SQL Server spatial data into PostGIS?

Specifically I want to be able to provide connection strings to my SQL Server and PostgreSQL instances, specify the table(s) I want to import, and click a button to do the import.

Answer

You can also use ogrfdw foreign data wrapper which piggy backs on GDAL. That's what I use for my SQL Server / to PostGIS import needs.

https://github.com/pramsey/pgsql-ogr-fdw

It's installed as part of the Windows PostGIS stackbuilder installers.

Here is how I connect to SQL Server. You can also use MSSQLSpatial driver, but I've found just using ODBC more predictable if you don't have spatial data.

1) If you haven't already, install the ogr fdw extension in your PostgreSQL database with -

CREATE EXTENSION ogr_fdw;

2) CREATE a Foreign server:

2a) If you want to use the ODBC type, Register a System DSN for your SQL Server database. In my example I'm just going to call it MYSS. Note that MSSQLSpatial supports DSNless connections, so this part is not necessary if you use that.

I tend to use ODBC since most of my sql server data is non-spatial and the MSSQlSPatial has a quirky behavior of renaming the primary key to fid. ODBC just always adds another fid I ignore.

-- this is how you do ODBC (it can be sql server or oracle or whatever)

 CREATE SERVER svr_myss
   FOREIGN DATA WRAPPER ogr_fdw
    OPTIONS (datasource 'ODBC:myuser/mypassword@MYSS',format 'ODBC');

-- this is how you do MS SQL Spatial. Note by default MSSPatial driver will only list tables with geometry (and I think geography type). You have to set an environment variable: MSSQLSPATIAL_LIST_ALL_TABLES=yes

for it to list all tables.

Refer to this for details: http://www.gdal.org/drv_mssqlspatial.html

-- replace localhost with the sql server name if it's not on same server. The 1433 is not necessary, but if you are running on a non-standard port (which I do), then you'd need to specify the port.

CREATE SERVER svr_myss
   FOREIGN DATA WRAPPER ogr_fdw
   OPTIONS (datasource 'MSSQL:server=localhost,1433;database=mygisdb;user=myuser;password=mypassword',format 'MSSQLSpatial');

You can also do trust and so forth, but that only works if you have postgresql running under a windows account that has rights to the database.

4) CREATE a schema to house your foreign tables:

CREATE SCHEMA myss;

5) Now I can link in all the tables in my dbo. schema like this:

IMPORT FOREIGN SCHEMA "dbo." FROM SERVER svr_myss INTO myss;

6) You should see all the tables listed as foreign tables and can then query them like any other table in PostgreSQL and even join them with PostgreSQL tables to your hearts content. Sweet.

One caveat is if you have a ton of tables, like over 200 in your SQL Server database, you may want to specify them as part of your CREATE SERVER. GDAL ODBC is a little dumb in querying tables, and specifying the tables before hand prevents it from trying to always query the whole catalog on each query, so your queries against the foreign tables will be much faster.

To do that, you'd set your CREATE SERVER like this:

CREATE SERVER svr_myss
   FOREIGN DATA WRAPPER ogr_fdw
        OPTIONS (datasource 'ODBC:myuser/mypassword@MYSS,dbo.User,dbo.AnotherTable',
format 'ODBC');

7) For copying data over I do a bulk insert like

SELECT * INTO myss.orders FROM myss.dbo_orders;

Alternatively, you can wrap your foreign table in a materialized view and setup a schedule to refresh your materialized view with pgagent for example.

CREATE MATERIALIZED VIEW myss.vw_orders