Skip to main content
Last updated on

st_collect function

Applies to: check marked yes Databricks Runtime 18.3 and above

Preview

This feature is in Public Preview.

Collects an array of GEOGRAPHY or GEOMETRY values into a single multipoint, multilinestring, multipolygon, or geometry collection.

Syntax​

st_collect ( geoArray )

Arguments​

  • geoArray: An array of GEOGRAPHY values, or an array of GEOMETRY values.

Returns​

A value of type GEOGRAPHY or GEOMETRY, representing a multipoint, multilinestring, multipolygon, or geometry collection.

Any NULL values in the input array are ignored. The type of the output depends on the types of the non-NULL input geometries:

  • If all non-NULL elements are points, returns a multipoint.
  • If all non-NULL elements are linestrings, returns a multilinestring.
  • If all non-NULL elements are polygons, returns a multipolygon.
  • Otherwise, returns a geometry collection.

Each output contains one element per non-NULL array element.

Multi-typed inputs (multipoint, multilinestring, multipolygon) and geometry collection inputs are preserved as elements of the resulting geometry collection; they are not flattened.

The SRID value of the output is the common SRID value of the non-NULL input geometries.

The dimension of the output is the maximum common dimension of the non-NULL input geometries.

If the input array is empty or contains only NULL values, the 2D empty geometry collection is returned. In this case, the SRID of the output is determined as follows:

  • If the input array's element type is GEOGRAPHY(ANY), the SRID of the output is 4326.
  • If the input array's element type is GEOMETRY(ANY), the SRID of the output is 0.
  • Otherwise, the SRID of the output is that of the input array's element type.

The function returns NULL if the input is NULL.

Error conditions​

Examples​

SQL
-- Collects an array of points into a multipoint.
> SELECT st_astext(st_collect(array(st_geomfromtext('POINT(1 2)'),st_geomfromtext('POINT(3 4)'))));
  MULTIPOINT((1 2),(3 4))
-- Collects an array of polygons into a multipolygon.
> SELECT st_astext(st_collect(array(st_geomfromtext('POLYGON((0 0,10 0,10 10,0 10,0 0))'))));
  MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)))
-- Collects an array of mixed geometry kinds into a geometry collection.
> SELECT st_astext(st_collect(array(st_geomfromtext('POLYGON((0 0,10 0,10 10,0 10,0 0))'),st_geomfromtext('LINESTRING(1 2,3 4)'))));
  GEOMETRYCOLLECTION(POLYGON((0 0,10 0,10 10,0 10,0 0)),LINESTRING(1 2,3 4))
-- Returns the 2D empty geometry collection for an empty input array.
> SELECT st_astext(st_collect(array()));
  GEOMETRYCOLLECTION EMPTY
-- Returns NULL for a NULL input.
> SELECT st_collect(NULL);
  NULL
On this page