ST_COLLECT_AGG()

All functions > GEOSPATIAL > ST_COLLECT_AGG()

Aggregate function that collects all geometries in a group into a single GeometryCollection without dissolving boundaries.

Signatures

Collect aggregate

Returns: A GeometryCollection or Multi* containing all input geometries

ST_COLLECT_AGG(geo: T) → GEOMETRY or GEOGRAPHY

With:

• T : Custom types: GEOM_POINT | GEOM_LINESTRING | GEOM_POLYGON | GEOM_MULTIPOINT | GEOM_MULTILINESTRING | GEOM_MULTIPOLYGON | GEOM_MULTIANY | GEOG_POINT | GEOG_LINESTRING | GEOG_POLYGON | GEOG_MULTIPOINT | GEOG_MULTILINESTRING | GEOG_MULTIPOLYGON | GEOG_MULTIANY

Signature notes:

• Unlike ST_UNION_AGG, does not dissolve shared boundaries; sub-geometries are kept separate
• On BigQuery and Trino: transpiled to ST_UNION_AGG / geometry_union_agg (boundaries may dissolve)
• NULL inputs are ignored
• Returns NULL if all inputs are NULL

Examples

SELECT
    f1 := ZIP(ARRAY[ST_GEOMPOINT(0.0, 0.0), ST_GEOMPOINT(1.0, 1.0)] AS geom).TRANSFORM(SELECT ST_ASTEXT(ST_COLLECT_AGG(geom))).UNWRAP_ONE() -- Collect aggregate over two points (ZIP + TRANSFORM)
;