Similarly to points in strings you can get a number of individual geometries in collections (types ST_MultiPoint
, ST_MultiLineString
, ST_MultiPolygon
) using ST_NumGeometries()
. And then access each of them using ST_GeometryN()
method.
SELECT
ST_UnionAggr("SHAPE").ST_GeometryType() AS "GEOM_TYPE",
ST_UnionAggr("SHAPE").ST_NumGeometries() AS "NUM_GEOMS"
FROM "TUTORIAL_GEO"."SPATIALSHAPES";
The query above aggregates all different geometries from the SPATIALSHAPES
table. The result is ST_GeometryCollection
with 5 different geometries in it. Some of overlapping polygons got aggregated into new single polygons, so the total number of geometries in the collection is smaller than a number of single geometries in the table.
SELECT
"SHAPE".ST_GeometryType() AS "GEOM_TYPE",
ST_UnionAggr("SHAPE").ST_GeometryType() AS "AGGR_GEOM_TYPE",
ST_UnionAggr("SHAPE").ST_NumGeometries() AS "AGGR_GEOM_NUM",
ST_UnionAggr("SHAPE").ST_GeometryN(1).ST_asWKT() AS "AGGR_GEOM_1_WKT"
FROM "TUTORIAL_GEO"."SPATIALSHAPES"
GROUP BY "SHAPE".ST_GeometryType();
This query now produced 3 different collections. Each of them contains only geometries of the same types thanks to GROUP BY
statement.
You will learn more about different kinds of spatial aggregations in a separate tutorial.