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

Question

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

Cross-posted as https://gis.stackexchange.com/q/407181/115 – PolyGeo Aug 01 '21 at 10:51 — PolyGeo, Aug 01 '21 at 10:51

Jim Jones · Accepted Answer · 2021-07-31T22:28:19.800

1

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

Using -dialect sqlite the geometry column is named geometry.

edited Jul 31 '21 at 22:28

answered Jul 30 '21 at 13:09

Jim Jones

18,404
3
35
44

Thanks for the answer but it throws the same error message, weird that it works inside PostgreSQL. I am going to upload the file sample. – Gery Jul 30 '21 at 19:18
@Gery "The file you requested has been deleted" the file is gone – Jim Jones Jul 31 '21 at 06:55
Thanks Jim for it, I re-uploaded it please find it here https://tmpfiles.org/dl/83316/tests.tar.gz. – Gery Jul 31 '21 at 07:17
@Gery now the link says "404 Not Found" :-D – Jim Jones Jul 31 '21 at 09:07
1

@Gery see my last edit, I added a command that might do what you want – Jim Jones Jul 31 '21 at 09:48
1

the last call did the trick, thanks a ton Jim! – Gery Jul 31 '21 at 15:29

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

1 Answers1

PostgreSQL / PostGIS

ogrinfo