PostGIS Migration – How to Migrate Spatial Tables from MSSQL to PostGIS

migrationpostgissql-server-spatial

I'd like to migrate my spatial data from MS SQL Server to PostGIS.

Originaly the data was stored with SDE under MS SQL. Then I migrated with an ArcGIS tool.
That created a spatial data column with WKB data.

I've tried to migrate with ogr2ogr. All the data come through correctly except the geometry. I don't know why.

Update:

  • SDE 10.0
  • MS SQL Server 2008 R2
  • ArcGIS 10.2
  • PostgreSQL 9.3 (with PostGIS)

I've tried to convert the fields with ogr2ogr:

ogr2ogr –config PGCLIENTCODING ISO-8859-2 -f "PostgreSQL PG: "host=localhost user=postgres dbname=test password=user" -sql "SELECT *, Geometry::STGeomFromWKB([Shape].STAsBinary(),23700).STAsText() AS [geom] FROM [test].[dbo].[BARLANG_PONT]" -nln barlang_pont ODBC:intra -a_srs "EPSG:23700"

With this code the spatial field translated to WKT (varchar) text to the PostGIS. The default spatial is the wkt_geom (geometry) column. I could use the -lco GEOMETRY_NAME=geom code to rename the default spatial but the two columns have different types.

Best Answer

The following steps works in nearly every case for this procedure. It may be a good idea to repair geometry if you find this process is not producing your desired results. There is the condition of truncated field names should they be greater than a certain length. Generally this is not as severe a problem as the actual transfer, though.

  • Open ArcGIS.
  • Open your MS SQL table through ArcGIS.
  • Save this table out to a shapefile.
  • Close ArcGIS.
  • Open QGIS
  • Open the saved shapefile
  • Export the shapefile to PostGIS
Related Question