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.
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.Sign up to join this community
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.
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
One solution involving commercial software is FME from Safe software. You simply select a source database (here MSSQL) and a destination database (postgis). The whole thing is done visually and involves a minimum of parameter entries.
This is what it looks like in the visual editor:
The tool also provides flexibility in changing attribute names and data types.
The tool is not free, but a trial license will give you an option to try it out. As such if this is a one time challenge it would solve your problem for free - this time around.
Using QGIS, load your MSSQL spatial layer into your map canvas.
Ensure you have a connection established to your PostGIS database
Using the QGIS DB Manager, simply connect to your PostGIS database and use the Import Vector Layer tool to import the QGIS layer - the MSSQL spatial layer - into your PostGIS database.