PostGIS Geometry type not matching column type in QGIS

Question

I'm trying to import some polygon shapefiles to Postgis in QGIS via SPIT. One of them can't be imported and returns this error:

ERROR: Geometry type (Polygon) does not match column type (MultiPolygon)

I've tried to change attribute "Feature Class" in that table in SPIT widnow, from MULTIPOLYGON to POLYGON, but nothing happened.

Is there any way to make PostGIS to accept both types (polygon and multipolygon) or can I convert shapefile geometry from polygon to multipolygon?

I've tried QGIS 2.0.1 in Windows and QGIS 2.3 in ArchLinux.

Answer 1

I use ogr2ogr to automate ingest of shapefiles into a PostGIS database. Specifically with regard to the question, use the option:

-nlt PROMOTE_TO_MULTI

This will force ogr2ogr to promote POLYGON geometries to MULTIPOLYGON, avoiding the error. A very simple example:

ogr2ogr -f "PostgreSQL" PG:"dbname='<my_db>'" -nlt PROMOTE_TO_MULTI <shapefile>

I have omitted pgsql host/auth details. To batch multiple shapefiles you might do something like:

find ./ -name *.shp | xargs -n1 ogr2ogr <ogr2ogr args omitting the shapefile>

Answer 2

This appears to be a known issue that won't be fixed: see http://hub.qgis.org/issues/5328

If you need a workaround, try changing your table's geometry column type to a generic 'geometry':

ALTER TABLE my_table ALTER COLUMN geom SET DATA TYPE geometry;

After you've done your import, you can revert back to MultiPolygon:

ALTER TABLE my_table ALTER COLUMN geom 
    SET DATA TYPE geometry(MultiPolygon) USING ST_Multi(geom);

Alternatively, try loading your data using ogr2ogr.

Answer 3

Spit is unmaintained and not recommended anymore. I'd suggest using the processing toolbox and choosing the "Import into PostGIS" algorithm. I've had much more luck using that routine. A few things to note:

• The database (connection name) parameter must match what you've named your database connection from the "Add PostGIS layer" dialog.
• The schema must already exist - it won't be created automatically

Answer 4

Although I did try to solve it with "Import to PostGis" algorithm, I didn't success (my database wasn't appearing on the list - I am using QGIS 2.10).

Instead, I used shp2pgsql, which was a simple Command Prompt task as described below:

• Open Command Prompt (as administrator)
• Get to PostgreSQL/bin folder which should look something like this: C:\Program Files\PostgreSQL\9.4\bin>
• Simply copy & paste your .shp files to this directory. ( I copied my entire folder with my .shp files.
• On Command Prompt type the following: shp2pgsql -s 4326 MyShpDir/MySHPFile.shp> MYSQLFile.sql where MyShpDir your directory, 4326 is WGS84's SRID so change if you are using different SSID, MySHPFile.shp your particular file (MYSQLFile will be created automatically). Obviously do this for all of the files you want to convert. The files will be saved in your current directory (C:\Program Files\PostgreSQL\9.4\bin in my case)

Then simply copy & paste the SQL files in your PLSQL DataBase.

Moreover, one additional cool thing about shp2pgsql is the fact that you can instantly create an index in your table by just adding the I parameter in the command, like this:

shp2pgsql -s 4326 -I MyShpDir/MySHPFile.shp> MYSQLFile.sql

Answer 5

Yes, you can tell PostGIS to accept any geometry type (polygon, multipolygon, point, linestring, etc) by adding the following constraint to your table.

For PostGIS 2.x (using generic Geometry typemod)

ALTER TABLE my_table ALTER COLUMN geom TYPE geometry(Geometry,4326);

Previous Answer (for PostGIS 1.x using Constraints)

CONSTRAINT enforce_geometry_type CHECK (geometrytype(geom) = 'MULTIPOLYGON'::text OR geometrytype(geom) = 'POLYGON'::text OR geom IS NULL)

Answer 6

I tried the 'Import into PostGIS' algorithm solution but found that that did not work either. The easiest solution I found was to go to Database > DB Manager, navigate to your database, and click the Import layer/file (the down arrow) button.