ST_Collect function to collect geometry objects - PolarDB

Combines geometry objects into a MULTI or GeometryCollection object.

Syntax

geometry ST_Collect(geometry set g1Field);
geometry ST_Collect(geometry g1, geometry g2);
geometry ST_Collect(geometry[] g1Array);

Parameters

Parameter	Description
`g1Field`	The geometry fields in the input dataset. Used when ST_Collect runs as an aggregate function.
`g1`	The first geometry object.
`g2`	The second geometry object.
`g1Array`	An array of geometry objects.

Usage notes

Return type

ST_Collect has two versions:

Version 1: When called with two geometry objects, ST_Collect returns a MULTI or GeometryCollection object.
Version 2: When used as an aggregate function, ST_Collect accepts a collection of geometry objects and returns the geometry object returned by the ST_Geometry function.

CircularString and CurveGeometry support

ST_Collect supports CircularString and CurveGeometry objects, but may not return a MultiCurve or MULTI object as expected in all cases.

ST_Collect vs ST_Union

Both functions combine geometry objects, but behave differently:

Behavior	ST_Collect	ST_Union
Output type	Returns a MULTI or GeometryCollection	May return a geometry object by dissolving boundaries
LineString handling	Returns a MultiLineString	Splits LineStrings at node intersections
Performance	Faster — no boundary dissolving or intersection checks	Slower — dissolves boundaries and checks for intersecting parts in MultiPolygon

To prevent ST_Collect from wrapping MULTI objects inside a GeometryCollection, use ST_Dump to decompose MULTI objects into individual geometries first, then pass them to ST_Collect.

Examples

Return a MULTIPOINT from two points

SELECT ST_AsText(ST_Collect('POINT(0 0)'::geometry, 'POINT(0 1)'::geometry));

Output:

      st_astext
---------------------
 MULTIPOINT(0 0,0 1)
(1 row)

Return a GeometryCollection from mixed geometry types

SELECT ST_AsText(ST_Collect('POINT(0 0)'::geometry, 'LINESTRING(0 2,0 3)'::geometry));

Output:

                     st_astext
----------------------------------------------------
 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 2,0 3))
(1 row)

Use as an aggregate function over a dataset

SELECT ST_AsText(ST_Collect(t.geom))
FROM (
  SELECT (ST_DumpPoints(st_buffer('POINT(0 0)'::geometry, 1, 'quad_segs=2'))).geom AS geom
) AS t;

Output:

                               st_astext
----------------------------------------------------------------
 MULTIPOINT(1 0,0.707106781186548 -0.707106781186547,0 -1,-0.7
 07106781186546 -0.707106781186549,-1 0,-0.70710678118655 0.707
 106781186545,0 1,0.707106781186544 0.707106781186551,1 0)
(1 row)