ST_GeometryN function

The ST_GeometryN function takes a geometry collection and an index as input parameters and returns the geometry in the collection that is identified by the index. The resulting geometry is represented in the spatial reference system of the specified geometry collection.

If the specified geometry collection is null or is empty, or if the index is smaller than 1 or larger than the number of geometries in the collection, then null is returned and a warning condition is raised (01HS0).

Syntax

Read syntax diagramSkip visual syntax diagramST_GeometryN(collection,index )

Parameter

collection
A value of type ST_GeomCollection or one of its subtypes that represents the geometry collection to locate the nth geometry within.
index
A value of type INTEGER that identifies the nth geometry that is to be returned from collection.

If index is smaller than 1 or larger than the number of geometries in the collection, then null is returned and a warning is returned (SQLSTATE 01HS0).

Return type

ST_Geometry

Example

The following code illustrates how to choose the second geometry inside a geometry collection. 
 
CREATE TABLE sample_geomcollections (id INTEGER, 
  geometry ST_GEOMCOLLECTION)
 
INSERT INTO sample_geomcollections(id, geometry)
VALUES
    (4001, ST_GeomCollection('multipoint(1 2, 4 3)', 1) ),
    (4002, ST_GeomCollection('multilinestring(
                           (33 2, 34 3, 35 6),
                           (28 4, 29 5, 31 8, 43 12),
                           (39 3, 37 4, 36 7))', 1) ),
    (4003, ST_GeomCollection('multipolygon(((3 3, 4 6, 5 3, 3 3),
                              (8 24, 9 25, 1 28, 8 24),
                              (13 33, 7 36, 1 40, 10 43, 13 33)))', 1))

SELECT id, cast(ST_AsText(ST_GeometryN(geometry, 2)) AS varchar(110)) 
    AS second_geometry
FROM   sample_geomcollections
Results: 

ID          SECOND_GEOMETRY
----------- ----------------------------------------------------------
       4001 POINT (4.000000 3.000000)
                                               
       4002 LINESTRING (28.000000 4.000000, 29.000000 5.000000, 
              31.000000 8.000000, 43.000000 12.000000)
              
       4003 POLYGON ((8.000000 24.000000, 9.000000 25.000000, 
              1.000000 28.000000, 8.000000 24.000000))