7

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.

3
  • GeoKettle is the opensource alternative spatialytics.org/projects/geokettle (postgres and SQL Server 2008 compatible. – Mapperz Feb 3 '17 at 19:33
  • There is no such free tool that I know of. The closest thing in terms of 'button click' tools is FME workbench. – DPSSpatial_BoycottingGISSE Feb 3 '17 at 19:33
  • Thanks @Mapperz. So far this seems like my best bet, although it seems like it may be more than I need. As I commented below, I'm basically just looking for a simple import of a SQL table into PostgreSQL, but have it include the geometry column. – FunkMonkey33 Feb 8 '17 at 16:31
4

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
8
  • Thanks, @LR1234567. Would you happen to have any sample code you can share? – FunkMonkey33 Feb 8 '17 at 16:32
  • I updated my answer. I hit submit too fast so didn't finish the materialized view example, but hopefully you get the idea. – LR1234567 Feb 8 '17 at 19:49
  • Oh I forgot to mention, that ogr_fdw launders table names and column names. This is so you don't have to spend your life quoting the columns and table names. If you really want your tables and columns to have the same exact name as in sql server, you can set the launder options as described here - postgresonline.com/journal/archives/… – LR1234567 Feb 8 '17 at 19:54
  • Thanks very much! I followed your process (using ODBC) and it did indeed create a foreign table which I can query. Nice! My only issue at this point is that my SQL Server source table uses a Geography column, and I guess PostGIS needs Geometry. Strangely, it creates a geometry column called 'geom', but its contents are null. The original Geography column came over as a byte array. I wonder if using SQL Spatial instead of ODBC will alleviate this. – FunkMonkey33 Feb 9 '17 at 22:36
  • And it does! SQL Spatial renames my primary key (Id in my case) to fid. Weird, but workable. It then converts my Geography column to a geometry column called geom. Of course it renames a bunch of things to lower case, but oh well! Dude, you're a lifesaver! – FunkMonkey33 Feb 9 '17 at 23:26
3

Ogr2ogr can do this for you, but it is a command line tool.

Here's the reference for MS SQL Server:

http://www.gdal.org/drv_mssqlspatial.html

and Postgres / PostGIS:

http://www.gdal.org/drv_pg.html

2
  • I appreciate this. It seems a bit complicated, but I'll be trying it out if GeoKettle doesn't work out for me. Also, it seems like it's more into the individual features than I need to be. I basically just need to copy rows from one DB to another, but have it include the geometry fields. Thanks for the info. – FunkMonkey33 Feb 8 '17 at 16:29
  • Also, if anyone has any sample code that does this, that would be hugely helpful. Thanks. – FunkMonkey33 Feb 8 '17 at 16:31
1

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:

enter image description here

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.

1
  • 1
    FME is pretty expensive, so I think I'm going to try the open source options first. Thanks for the info though. – FunkMonkey33 Feb 8 '17 at 16:27
1

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.

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.