DuckDB function ST_Collect

Collects a list of geometries into a collection geometry.

• If all geometries are POINT's, a MULTIPOINT is returned.

• If all geometries are LINESTRING's, a MULTILINESTRING is returned.

• If all geometries are POLYGON's, a MULTIPOLYGON is returned.

• Otherwise if the input collection contains a mix of geometry types, a GEOMETRYCOLLECTION is returned.

Empty and NULL geometries are ignored. If all geometries are empty or NULL, a GEOMETRYCOLLECTION EMPTY is returned.

Usage

ST_Collect(geoms)

Arguments

geoms

GEOMETRY[]

Value

GEOMETRY

Provided by

The spatial extension (LOAD spatial;).

SQL examples

-- With all POINT's, a MULTIPOINT is returned
SELECT ST_Collect([ST_Point(1, 2), ST_Point(3, 4)]);
-- MULTIPOINT (1 2, 3 4)
--
-- -- With mixed geometry types, a GEOMETRYCOLLECTION is returned
-- SELECT ST_Collect([ST_Point(1, 2), ST_GeomFromText('LINESTRING(3 4, 5 6)')]);
-- GEOMETRYCOLLECTION (POINT (1 2), LINESTRING (3 4, 5 6))
--
-- -- Note that the empty geometry is ignored, so the result is a MULTIPOINT
-- SELECT ST_Collect([ST_Point(1, 2), NULL, ST_GeomFromText('GEOMETRYCOLLECTION EMPTY')]);
-- MULTIPOINT (1 2)
--
-- -- If all geometries are empty or NULL, a GEOMETRYCOLLECTION EMPTY is returned
-- SELECT ST_Collect([NULL, ST_GeomFromText('GEOMETRYCOLLECTION EMPTY')]);
-- GEOMETRYCOLLECTION EMPTY
--
-- -- Tip: You can use the `ST_Collect` function together with the `list()` aggregate function to collect multiple rows of geometries into a single geometry collection:
--
-- CREATE TABLE points (geom GEOMETRY);
--
-- INSERT INTO points VALUES (ST_Point(1, 2)), (ST_Point(3, 4));
--
-- SELECT ST_Collect(list(geom)) FROM points;
-- MULTIPOINT (1 2, 3 4)