ST_Collect_Agg

Introduction: Collects all non-null spatial values in a group without dissolving their boundaries. Homogeneous Point, LineString, or Polygon values produce the corresponding multi-object; mixed types produce a GeometryCollection.

Format:

ST_Collect_Agg (A: Geometry)

ST_Collect_Agg (A: Geography)

Return type: Geometry or Geography, matching the input type

Since: v1.9.1

Null values are ignored and duplicates are preserved. The function returns NULL when a group has no non-null values. All Geography values in a group must have the same SRID.

ST_Collect_Aggr is also registered as an alias.

Geometry example:

SELECT ST_Collect_Agg(geom)
FROM (
    VALUES
        (ST_GeomFromWKT('POINT(1 2)')),
        (ST_GeomFromWKT('POINT(3 4)')),
        (ST_GeomFromWKT('POINT(1 2)'))
) AS observations(geom)

Output:

MULTIPOINT ((1 2), (3 4), (1 2))

Geography example:

SELECT region, ST_Collect_Agg(geog) AS geographies
FROM observations
GROUP BY region