ST_GEOMETRYN()

All functions > GEOSPATIAL > ST_GEOMETRYN()

Returns the Nth sub-geometry from a geometry collection (1-indexed).

Signatures

Geometry N

Returns: The Nth sub-geometry

ST_GEOMETRYN(geo: T, n: INT) → GEOM_MULTIANY or GEOG_MULTIANY

With:

• T : Custom types: GEOM_POINT | GEOM_LINESTRING | GEOM_POLYGON | GEOM_MULTIPOINT | GEOM_MULTILINESTRING | GEOM_MULTIPOLYGON | GEOM_MULTIANY | GEOG_POINT | GEOG_LINESTRING | GEOG_POLYGON | GEOG_MULTIPOINT | GEOG_MULTILINESTRING | GEOG_MULTIPOLYGON | GEOG_MULTIANY

Signature notes:

• Index is 1-based
• For singular geometries (Point, LineString, Polygon): returns the geometry itself when n=1, NULL otherwise
• Out of range index returns NULL
• Returns NULL if any input is NULL

Examples

SELECT
    f1 := ST_ASTEXT(ST_GEOMETRYN(ST_GEOMFROMTEXT('MULTIPOINT ((0 0), (1 1), (2 2))'), 2)), -- Second point of a MultiPoint
    f2 := ST_ASTEXT(ST_GEOMETRYN(ST_GEOMPOINT(5.0, 5.0), 1)) -- Singular geometry: n=1 returns itself
;