New to PostGIS. I have a few large multipolygon datasets with errors that PostGIS should be able to fix quite efficiently. However, when in QGIS I try to import the layers into PostGIS, it will error out and stop importing when it reaches an invalid geometry.

The data currently is spread among a series of geopackages that load fine in QGIS, but the layers contain many errors. My desire is to use PostGIS to fix the errors. There are too many to manually fix.

How do I get invalid geometry into PostGIS so that I can use the various ST_ wrappers to fix it?

  • are they shapefiles? did you look into ogr2ogr? – ziggy Nov 28 '17 at 17:06
  • See OP: they are geopackages. I searched google for "import layers from geopackage to postgis using ogr2ogr" and it did not turn up any CLI examples for doing this. – auslander Nov 28 '17 at 17:40
  • How are you importing the layers into PostGIS? – DPSSpatial Nov 28 '17 at 17:41
  • @DPSSpatial QGIS DB Manager. I load the GeoPackage file in QGIS, load the DB Manager, and attempt to import the layer. – auslander Nov 28 '17 at 17:42
  • @auslander do the Topology Checker plugin tools work on geopackages? – DPSSpatial Nov 28 '17 at 17:51

GDAL is an abstarction library and there is no need to have documentation for each possible import-export combination. It is usually enough to read the documentation of ogr2ogr http://www.gdal.org/ogr2ogr.html and the output driver that is in your case http://www.gdal.org/drv_pg.html. Your command will be about

ogr2ogr -f PostgreSQL PG:"dbname='databasename' host='addr' port='5432' user='x' password='y'" my_geopackage.gpkg

Ogr2ogr does not try to validate the geometries during conversion and you have good possibilities to get also the invalid geometries into PostGIS.

  • this seemed to work, but resulted in tables in postgis that would not load into qgis ("not a valid layer and can not be added to the map.") I reviewed the GDAL driver documentation for gpkg, and do not see any examples that point me where I need to go (they all focus on converting TO geopackage, not from it). I tried doing ogr2ogr -f PostgreSQL PG:"pg connection string here" -lco GEOMETRY_NAME=geom my_file.gpkg and this wrote the tables to the db, but they were also rejected as invalid. – auslander Nov 28 '17 at 20:39
  • Nevermind - it turns out I was trying to drag the layer from the QGIS DB Manager into QGIS, and it was failing (probably a separate bug). When I loaded the layer directly from PostGIS, it worked. Thanks! – auslander Nov 28 '17 at 21:43

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.