6

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.

Improve this question
4
  • Please update your question to include the version of SQL-Server, the version of ArcSDE, the version or ArcGIS, the version of PostgreSQL, and the exact process you used in data transition.
    – Vince
    Jul 3, 2014 at 15:43
  • Is your data in the SDE binary format, or in MSSQL Spatial wkb format?
    – DPierce
    Jul 9, 2014 at 0:02
  • I would think scripting with OGR2OGR/GDAL would handle this without the extra conversion to SHP...
    – DPSSpatial_BoycottingGISSE
    Sep 3, 2014 at 18:53
  • Foreign data wrappers could be a valid approach for this. wiki.postgresql.org/wiki/Foreign_data_wrappers
    – Alexandre Neto
    Feb 3, 2016 at 22:45

2 Answers 2

Reset to default
4

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
Improve this answer
2

If you are doing a 1-off migration, you could do it in QGIS.

  1. Add the MSSQL table as a layer. enter image description here

  2. Open your DB Manager (I had to add my PostGIS connection via the Layer->Add Layer-> Add PostGIS Layer... in order for the connection to appear in the DB Manager) enter image description here

  3. Navigate to the Postgres database that you want to import the data into.

  4. Press the Import Layer button:
    enter image description here

  5. Specify your import options and press OK. enter image description here

  6. If all goes well, you get a success message:
    enter image description here

  7. At this point, it is in the database but not QGIS. You can add it from the DB Manager. enter image description here

With this (or the direct OGR2OGR/GDAL) you don't end up with the truncated column names that you would have by the conversion to shapefile:
enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.