Name
ST_CollectionExtract — Extracts a particular type of geometry from a collection and converts them to MULTI*. The values for the type of geometry are 1=Point, 2=Line, 3=Polygon.
Synopsis
geometry
ST_CollectionExtract(bytea
Geometry, integer
geomDimension);
Description
Extracts a particular type of geometry from a collection and converts them to MULTI*. The values for geomDimension are 1=Point, 2=Line, 3=Polygon
Coordinate Dimensions
| 2D | 3D | M |
|---|---|---|
 | | |
Spatial Standards Support
| OGC SFS for SQL. 1.1 (1999) | OGC SFS for SQL. 1.1.0 (2005) | OGC SFS for SQL. 1.2.0 (2006) | SQL-MM Part 3 |
|---|---|---|---|
| - | - | - | - |
Examples
SELECT ST_AsText(ST_CollectionExtract(ST_GeomFromText('MULTIPOINT ((1 1), (2 3), (4 1))'),1));
--Result
MULTIPOINT ((1 1), (2 3), (4 1))
SELECT ST_AsText(CollectionExtract(ST_GeomFromText(
'GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(LINESTRING (0 0 15, 10 10 25, 20 10 35),POINT(0 0 10)))'
),2));
--Result
MULTILINESTRING ((0 0 15, 10 10 25, 20 10 35))
SELECT ST_AsText(ST_CollectionExtract(ST_GeomFromText(
'GEOMETRYCOLLECTION(POLYGON ((0 0 1 1, 10 10 2 2, 10 0 3 3, 0 0 4 4)),POINT(0 0 0 0))')
,3));
--Result
MULTIPOLYGON (((0 0 1 1, 10 10 2 2, 10 0 3 3, 0 0 4 4)))![[Note]](images/note.png) | |
This function if there are no sub-geometries of the right type, returns the full geometry. SELECT ST_AsText(ST_CollectionExtract(ST_GeomFromText('LINESTRING(1 1,2 3,4 1)'),1));
SELECT ST_AsText(ST_CollectionExtract(ST_GeomFromText('LINESTRING(1 1,2 3,4 1)'),2));
SELECT ST_AsText(ST_CollectionExtract(ST_GeomFromText('LINESTRING(1 1,2 3,4 1)'),3));
--Result:
st_astext
----------------------------
LINESTRING (1 1, 2 3, 4 1)
(1 row) |