I am trying to load a shapefile into a PostgreSQL/PostGIS databases with the following statement run within the OSGeo4W shell

ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 dbname=testing user=postgres password=12345" "D:/TEST_RUBBISH/mains_topo.shp"

I get the following warning which effectively preempts the failure of the insert operation

Warning 1: Geometry to be inserted is of type Multi Line String, whereas the layer geometry type is Line String. Insertion is likely to fail

I have been all over the internet and into the 'PostGIS in Action' book to try and work out how to resolve this but no joy so far

Could anyone out there suggest a solution

  • are you loading into an existing postgres table? or brand new?
    – ziggy
    Sep 15 '20 at 19:37
  • Brand new. After much searching and hair-pulling and typically not long after posting this question I found a solution in # gis.stackexchange.com/questions/195172/import-a-shapefile-to-postgis-with-ogr2ogr-gives-unable-to-open-datasource that has solved my issue with the inclusion of -nlt GEOMETRY in my ogr2ogr command
    – TrevP
    Sep 15 '20 at 19:43

Your Shapefile contains a mix of LineStrings and MultiLineStrings. When OGR creates the table it looks at the first record to determine the geometry type and happens to see a LineString. Later on it finds a MultiLineString which PostGIS considers to be materially different to a LineString and thus won't accept into a LineString column.

The solution is to use -ntl PROMOTE_TO_MULTI when importing legacy data formats like shapefiles and attempt to remove them from your workflow in the future.

  • This works (-nlt, though), but one should note that if you tried to import once without the option, and you get the error listed above, then you have to remember to drop the table by hand before running ogr2ogr again, or you just keep getting the error. Oct 6 at 8:09

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