28

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 maybe somehow I can convert shapefile geometry from polygon to multipolygon? Any ideas?

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

14

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
  • It works perfectly. Thanks. Also I loaded this shapefile with DB Manager. First I'd loaded other layer with Multipolygon by SPIT, then I overwrote it with POLYGON layer in DBM (Import layer/file). It works too. – dmh126 Apr 30 '14 at 22:54
  • The database won't show (even if it has the same name in QGIS 2.10 on Windows) – Menelaos Kotsollaris Sep 16 '15 at 20:24
19

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.

  • upvoting your answer @dbaston. it's better than mine. :) – sfletche Apr 30 '14 at 16:29
17

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>
  • This works and is easier than other answers. It deserves upvotes. – Joe Germuska Jun 21 '18 at 18:47
5

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

Profit! :)

3

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)
  • Syntax error at or near CONSTRAINT. I've tried SET CONSTRAINTS but instead that I got same error near CHECK. – dmh126 Apr 30 '14 at 15:26
  • what version of postgis are you using? – sfletche Apr 30 '14 at 15:35
  • Postgis 2.1 for PostgreSQL 9.3 – dmh126 Apr 30 '14 at 15:40
  • try using enforce_geotype_geom rather than enforce_geometry_type. let me know if that works, and I'll edit my response if it does. – sfletche Apr 30 '14 at 15:44
  • It doesn't work. The same error. – dmh126 Apr 30 '14 at 15:54
2

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.

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.