sqlpostgresqlpostgisshapefileogr

Cannot convert multipolygon to polygon in shape file: no geometry column OGR_GEOMETRY?

The attempt is to change from multipolygon to polygon (shapefile) using:

ogrinfo tmpem122.shp -sql "alter table tmpem122 alter column OGR_GEOMETRY type geometry(polygon, 4326) using st_geometryn(geometry,1)"

I get this error message:

ERROR 1: alter table tmpem122 alter column OGR_GEOMETRY type geometry(polygon, 4326) using st_geometryn(geometry,1) failed, no such field as `OGR_GEOMETRY'.

but according to ogrinfo -sql "select * from tmpem122" tmpem122.shp, the geometry column is OGR_GEOMETRY (or _ogr_geometry_):

Layer name: tmpem122
Geometry: Polygon
Feature Count: 17
Extent: ...
...
...
Geometry Column = _ogr_geometry_
gid: String (10.0)
...

What would it be wrong? Please find the file sample here for your testing: https://file.io/U4XnvYSb69lp

Solution

  • PostgreSQL / PostGIS

    Inside PostgreSQL this is the correct syntax to change the data type of a geometry column and fill it with the first geometry of a collection using ST_GeometryN:

    ALTER TABLE tmpem122 ALTER COLUMN _ogr_geometry_ 
TYPE geometry(polygon, 4326) USING ST_GeometryN(_ogr_geometry_,1);

    Demo: db<>fiddle

    ogrinfo

    Since you're only interested in modifying the existing geometry, you just need to modify the geometry instead of the column data type:

    $ ogrinfo -dialect sqlite -sql "UPDATE tmpem122 SET geometry = ST_GeometryN(geometry,1)" tmpem122.shp