3

I have a table in PostGIS (2.1.7) containing adjacent multipolygons, and I'd like to create a view containing polygons which are merged versions of the multipolygons based on some field value.

I tried the following query:

CREATE VIEW results AS
SELECT somefield, ST_Union(geom)::geometry(Polygon, 2056) AS geom FROM somelayer GROUP BY somefield;

and I got the following error:

Geometry type (MultiPolygon) does not match column type (Polygon)

I thus tried to change the result type to multipolygon:

CREATE VIEW results AS    
SELECT somefield, ST_Union(geom)::geometry(MultiPolygon, 2056) AS geom FROM somelayer GROUP BY somefield;

But it didn't help since I got the following error:

Geometry type (Polygon) does not match column type (MultiPolygon)

The following query finally worked but it is not optimal since I'd like to have polygons instead of multipolygons (it should be possible since the resulting polygons are all singlepart):

CREATE VIEW results AS    
SELECT somefield, ST_Multi(ST_Union(geom))::geometry(MultiPolygon, 2056) AS geom FROM somelayer GROUP BY somefield;

How can I get the desired results?

3
CREATE VIEW results AS ( 
SELECT somefield, (ST_Dump(ST_Union(geom))).geom AS geom FROM somelayer GROUP BY somefield);
  • I have the same problem using your suggestion @MichalZimmermann... The query runs OK but the geom column geometry is geometry, not geometry(MultiPolygon). This causes display issues in QGIS with DB Manager... – wiltomap Sep 22 '16 at 12:17

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.