st_geometryn function

Applies to: Databricks Runtime 17.1 and above

Returns the 1-based n-th element of the input geometry as a GEOMETRY value.

Syntax

st_geometryn ( geo, n )

Arguments

• geo: A GEOMETRY value.
• n: The 1-based index of the element to retrieve.

Returns

A value of type GEOMETRY, representing the n-th element of the input geometry.

If the input is a multipoint, a multilinestring, a multipolygon, or a geometry collection, the function returns the n-th element, or returns an error if the element does not exist.

If the input is a non-empty point, linestring, or polygon and the value of the index is equal to 1, the function returns the input as a GEOMETRY value, otherwise returns an error.

The SRID value of the output geometry is the same as that of the input value.

Note that indices smaller than 1 are not supported, and will result in an error.

The function returns NULL if the input is NULL.

Error conditions

• If the element at index n does not exist, the function returns ST_INVALID_INDEX_VALUE.
• If the index is smaller than 1, the function returns ST_INVALID_INDEX_VALUE.
• If the input is a non-empty point, linestring, or polygon and the index is not equal to 1, the function returns ST_INVALID_INDEX_VALUE.

Examples

-- Returns the second element of a geometry collection.
> SELECT st_astext(st_geometryn(st_geomfromtext('GEOMETRYCOLLECTION(POINT(4 5),LINESTRING(10 3,24 37,44 85))'), 2))
  LINESTRING(10 3,24 37,44 85)
-- Returns the first element of a multipoint.
> SELECT st_astext(st_geometryn(st_geomfromtext('MULTIPOINT((1 2),(3 4),(5 6))'), 1))
  POINT(1 2)

Related functions

• st_numgeometries function
• st_geometrytype function