Loading data using QGIS to PostgreSQL database

Question

I am trying to import a shapefile using desktop QGIS to postgreSQL database using the import shape file to postgresql database tool but it raises the following error:

Problem inserting features from file: E:/MOA/sample point identification/EthioWoredaandZones_csa2007/EthioWoredas_csa2007.shp

The database gave an error while executing this SQL:

INSERT INTO "public"."EthioWoredas_csa2007"("REGIONNAME","ZONENAME","WOREDANO_","WOREDANAME","the_geom") VALUES ('Addis Ababa','Region 14','140104','B... (rest of SQL trimmed)

The error was:

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

How can I import this shapefile into postgeSQL and manipulate it?

Answer 1

SPIT don't allow import Multipolygon geometry in PostGIS database (QGIS, Postgis: Geometry type does not match column type). Use DB Manager tool to import MultiPolygonal geometry.

Answer 2

The easiest way to solve this is to save a copy of your shapefile using QGIS and set the Geometry type as Polygon.

These are the steps:

• add shapefile layer to QGIS
• right-click layer on layers panel
• save as..
• select Polygon in the geometry type dropdown list
• try again using DB Manager

Answer 3

I had the exact same issue and its an error whereby it loads the first record into PostGIS and determines its type based on that therefore if the first record is a normal polygon it will return an error when it eventually hits a multipolygon record.

The workround for this is to copy and paste a multipolygon record as your first record in the table and then it imported fine for me into PostGIS.

Answer 4

I've come across the same problem and manage to get around it by transforming my geometries with shapely. In my case I was using psycopg2 and ST_GeomFromWKB({geom!s}::geometry, srid)...format(my_wkb_geometry)

from shapely.geometry.multipolygon import MultiPolygon
from shapely import wkt
from shapely import wkb

#With w being a wkt expression of the geometry obtained by feature.geometry().exportToWkt()
def poly2multi(w):
    p = wkt.loads(w)
    m = MultiPolygon([p])
    gw = wkb.dumps(m).encode('hex')
    return gw

It took me some time to figure out so I hope this helps !

Answer 5

I think it has something to do with fact that your geometry is MultiPolygon instead of Polygon that is used in your layer. I remember there is a conversion tool for handling this problem during import.