[GIS] How to use ST_Union with polygon and multipolygon geometry types

geometrypostgispostgresql

I am attempting to combine the geometries for multiple records using the following:

INSERT INTO schema.boundary (name, geom)  
SELECT 'Name' ,  ST_Union(geom) FROM schema.boundary WHERE id = 0;

This returns the error:

ERROR:  Geometry type (Polygon) does not match column type (MultiPolygon)
********** Error **********

So I then tried the following:

INSERT INTO schema.boundary (name, geom)  
SELECT 'Name' ,  ST_Union(ST_Multi(geom)) FROM schema.boundary WHERE id = 0;

This returns the same error. I managed to combine all of the target geometry fields except for one. Checking the geometry type using ST_GeometryType(geom) for the all records returns 'ST MultiPolygon'. I've also tried updating the single record using ST_Multi(geom).

Is there another PostGIS function I can use to combine incompatible geometry types?

Best Answer

My guess is that the union-ed polygon is a single part polygon and not a multipart polygon, hence failing on the insert, not on the union.

Could you try something like:

INSERT INTO schema.boundary (name, geom)  
SELECT 'Name' ,  ST_Multi(ST_Union(ST_Multi(geom))) FROM schema.boundary WHERE id = 0;

If that doesn't work, could you post the schema and some sample data that you've used to re-create the problem?

Related Question