postgis update multipolygon with st_makevalid() gives error

Question

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?

Answer 1

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.

Answer 2

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;