How to migrate spatial tables from MSSQL to PostGIS

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

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