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 given geometry collection.

If the given 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).

This function can also be called as a method.

Syntax

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

db2gse.ST_Geometry

Example

The following code illustrates how to choose the second geometry inside a geometry collection.


SET CURRENT FUNCTION PATH = CURRENT FUNCTION PATH, db2gse 
 
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_GeometryN(geometry, 2)..ST_AsText AS varchar(110)) 
    AS second_geometry
FROM   sample_geomcollections

Results:


ID          SECOND_GEOMETRY
----------- ----------------------------------------------------------
       4001 POINT ( 4.00000000 3.00000000) 

       4002 LINESTRING ( 28.00000000 4.00000000, 29.00000000 5.00000000, 
   31.00000000 8.00000000, 43.00000000 12.00000000)

       4003 POLYGON (( 8.00000000 24.00000000, 9.00000000 25.00000000, 
   1.00000000 28.00000000, 8.00000000 24.00000000))