6

I have a table with a few invalid geometries (empty rings, self-intersections). I'd like to fix them up something like this:

update mytable
set geomcolumn = st_makevalid(geomcolumn)
where id = 123;

and I get "ERROR: Geometry type (GeometryCollection) does not match column type (MultiPolygon)"

is there some other function I need to wrap the st_makevalid() in to get a compatible type, or something else?

12

ST_CollectionExtract will pull out a particular type of component from a GeometryCollection. For example, ST_CollectionExtract(geom, 3) will return a Polygon or MultiPolygon.

Since your column type is MultiPolygon, you may need to wrap ST_CollectionExtract with yet another function, ST_Multi, to coerce Polygon geometries into single-component MultiPolygons.

Beware ST_MakeValid - it generally does what you'd expect it to, but it's not magic and it can occasionally "correct" your geometry in unexpected ways. It's a good idea to run a SELECT query first, to see what it has in mind.

  • 2
    that's the ticket. I used the following to 1) find the invalid rows in PGAdmin, 2) preview them in the QGIS DB Manager, and 3) update them in PGAdmin:select geomcol, id, st_isvalid(geomcol), st_isvalidreason(geomcol) from mylayer where st_isvalid(geomcol)) = false; select id, st_multi(st_collectionextract(st_makevalid(geomcol), 3)) from mylayer where id in (123, 456); update mylayer set geomcol = st_multi(st_collectionextract(st_makevalid(geomcol), 3)) where id in (123, 456); – MC5 Oct 3 '15 at 15:58
3

You need to update your geometry column by extracting Multipolygons from GeometryCollection. Simply run below query and you are good to go

update table set geomcol = st_multi(st_collectionextract(st_makevalid(geomcol),3))
    where st_isvalid(geomcol) = false;

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.