migration - How to migrate spatial tables from MSSQL to PostGIS

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.

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