ST_UNION_AGG()

All functions > GEOSPATIAL > ST_UNION_AGG()

Aggregate function that dissolves all geometries in a group into a single geometry.

Signatures

Union aggregate

Returns: Dissolved union of all input geometries

ST_UNION_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:

• Dissolves all geometries in the group, merging shared boundaries
• On Trino: transpiled to geometry_union_agg()
• Disjoint inputs are not merged into one connected polygon: the result is often a MULTIPOLYGON (or similar multi-part type) with one part per separate island
• NULL inputs are ignored
• Returns NULL if all inputs are NULL
• Typical doc pattern: build rows with ZIP(ARRAY[...] AS col) then .TRANSFORM(SELECT ST_ASTEXT(ST_UNION_AGG(col))) (same shape as other aggregates)

Examples

SELECT
    f1 := ZIP(ARRAY[ST_GEOMFROMTEXT('POLYGON ((0 0, 1 0, 1 1, 0 1, 0 0))'), ST_GEOMFROMTEXT('POLYGON ((1 1, 2 1, 2 2, 1 2, 1 1))')] AS geom).TRANSFORM(SELECT ST_ASTEXT(ST_UNION_AGG(geom))).UNWRAP_ONE() -- Two non-touching squares: result is one MULTIPOLYGON with two parts (ZIP + TRANSFORM)
;