[GIS] Error exporting shapefile from QGIS into PostGIS

postgisqgis

I know this error below has been posted a couple times on this forum, but none of the solutions seem to work for me.

I am trying to export a shapefile from QGIS to a PostGIS server using DB Manager.

If I check "create single-part geometries instead of multi-part" it works but obviously not as a multi-part geometry type. Everything uploads fine in PG Admin III, but I want to be able to do everything from QGIS.

If I try to edit a layer already on the PostGIS server I get the same error (second error message below). What's the source of this problem?

If it's the shapefile why does it work in PG Admin III?

Also if I sometimes take the same file as a shapefile, I can upload it through DB Manager and work with it fine. No problems. Makes all sorts of multipolygons.

But if I try the same thing with a file uploaded through PG Admin III an they work on th layer through DB Manager it doesn't work.

Note: some layers nothing works.

Error 7
Feature write errors:
Creation error for features from #0 to #0. Provider errors was: 
PostGIS error while adding features: ERROR:  Geometry type (LineString)
does not match column type (MultiLineString)`

Errors: ERROR: 1 feature(s) not added

Provider errors:
PostGIS error while adding features: ERROR:  Geometry type (LineString)
does not match column type (MultiLineString)

Key Question: Why does the exportation of a shapefile from QGIS to PostGIS using DB Manager create errors with multi-part polygons and polylines?

Best Answer

The main reason is that shapefile can contain both linestrings and multilinestrings but if the PostGIS table is initialized to have linestrings it does not accept multilinestrings or vice versa. Probably the first feature in the shapefile was a linestring and error occurred when the first multilinestring appeared in the data. What should be done is to initialize PostGIS table to hold multilinestrings and while exporting data to convert all linestrings into multilinestrings. That's OK because multilinestrings can have only one part.

Ogr2ogr http://www.gdal.org/ogr2ogr.html has a switch -promote_to_multi for that purpose but DB manager seems to miss that useful option.

Starting with GDAL 1.10, PROMOTE_TO_MULTI can be used to automatically promote layers that mix polygon or multipolygons to multipolygons, and layers that mix linestrings or multilinestrings to multilinestrings. Can be useful when converting shapefiles to PostGIS and other target drivers that implement strict checks for geometry types.

Related Question