17

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?

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
MC5
  • 1,901
  • 1
  • 14
  • 23

2 Answers2

23

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.

dbaston
  • 13,048
  • 3
  • 49
  • 81
  • 4
    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 03 '15 at 15:58
18

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;
muzaffar
  • 1,073
  • 10
  • 27
  • 1
    Although the accepted answer gives detailed info on what is to be done, a good example is always worth a +1! (EDIT: which I gave to the accepted answer as well obviously) – umbe1987 Jul 10 '20 at 09:44