ST_CollectionExtract-PolarDB(PolarDB)-阿里云帮助中心

Extracts geometry objects of a specified type from a GeometryCollection and returns them as a single geometry.

Syntax

geometry ST_CollectionExtract(geometry collection, integer type);

Parameters

Parameter	Type	Description
`collection`	geometry	The input GeometryCollection.
`type`	integer	The geometry type to extract. Valid values: `1` (Point), `2` (LineString), `3` (Polygon).

Usage notes

This function supports Point objects, LineString objects, and Polygon objects.
If no geometry objects of the specified type exist in the collection, an empty geometry object is returned.
When type is 3 (Polygon), ST_CollectionExtract returns a MultiPolygon even if the edges of polygons in the collection are shared.

Note In most cases, the returned MultiPolygon is invalid. For example, when you apply ST_CollectionExtract to a collection returned by ST_Split, the result is an invalid MultiPolygon.

Example

Extract all LineString geometries (type 2) from a GeometryCollection:

SELECT ST_AsText(
  ST_CollectionExtract(
    ST_GeomFromText('GEOMETRYCOLLECTION(LINESTRING(1 1,2 1),LINESTRING(2 1,2 2))'),
    2
  )
);

Output:

              st_astext
--------------------------------------
 MULTILINESTRING((1 1,2 1),(2 1,2 2))
(1 row)