A very quick tip for those scratching their heads over trying to import a 3D shapefile (which doesn’t actually have any 3D data, or at least none you care about) into a flat (2D) PostGIS data set and you keep getting a geometry constraint error.
with a command like:
ogr2ogr -update -append -f PostgreSQL PG:"dbname=postGISdatbase" my_shapefile.shp -nln testTable
You might find you get an error like:
Warning 1: Geometry to be inserted is of type 3D Polygon, whereas the layer geometry type is Multi Polygon. Insertion is likely to fail ERROR 1: INSERT command for new feature failed. ERROR: new row for relation "testtable" violates check constraint "enforce_geotype_wkb_geometry" Command: INSERT INTO "testTable" ("wkb_geometry" , "zip7", "lat", "long", "attribute") VALUES ('01030000A0AD100000010000002A000000CB5ADB86511155C0315A402C9BE34040000000000000000020095C8E511155 ... ...400000000000000000'::GEOMETRY, 3000210, 33.77697326990, -84.27247267360, 31) RETURNING "ogc_fid" ERROR 1: Terminating translation prematurely after failed translation of layer my_shapefile (use -skipfailures to skip errors)
The important indicator of this error is the: “check constraint “enforce_geotype_wkb_geometry” ” line. This tells us that the PostGIS table constraint has been broken and there is a disconnect between the shapefile you want to import and the table you have, or want to have in PostGIS. In the case above, it might seem like you could just add the “MULTIPOLYGON” as the geometry type, but its not that easy. Often a shapefile can end up with 3D geometry unintentionally. This can be frustrating if you really don’t care that much about 3D features and the shapefile “just happened” to be created that way.
Luckily, this is easily fixed. If you define the target geometry type, like this:
ogr2ogr -update -append -f PostgreSQL PG:"dbname=postGISdatbase" my_shapefile.shp -nln testTable -nlt MULTIPOLYGON25D
& you might just be in luck 🙂
What’s happening here? Well go check out the docs and you can see that you can add “25D” to the name of a specific geometry to get a 2.5D version.