Spatialite: Convert polygon to multi-polygons based on common field

Question

I have a list of polygons which should be multipolygons:

PK_UID | PolygonID | Geometry
------------------------------
1      | 1         | polygon
2      | 2         | polygon
3      | 1         | polygon

How do I convert them to multipolygons in Spatialite based on the common field (PolygonID)? (e.g So they look like this):

PK_UID | PolygonID | Geometry
-----------------------------------
1      | 1         | multi-polygon
2      | 2         | polygon

Micha · Accepted Answer · 2013-09-04T12:20:03.117

This should do it:

SELECT PolygonID, Collect(Geometry) 
FROM polygons
GROUP BY PolygonID

If you want this as a new MULTIPOLYGON layer, then first create the table:

CREATE TABLE multipolys (PolygonID integer);
SELECT AddGeometryColumn('multipolys','Geometry',<SRID>,'MULTIPOLYGON',2);

Now do an INSERT from the above SELECT:

INSERT INTO multipolys (PolygonID, geometry)
SELECT PolygonID, Collect(Geometry) 
FROM polygons
GROUP BY PolygonID

1 Answers1