postgis update multipolygon with st_makevalid() gives error
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.
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;