ST_GeometryN

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

If the given geometry is null or is empty, then null is returned.

Syntax

Parameter

geometry: A value of type ST_MultiLineString, ST_MultiPolygon, or ST_MultiPoint that represents the geometry type to locate the nth geometry within.
index: A value of type INTEGER that identifies the nth geometry that is to be returned from geometry.
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 type.


SET CURRENT FUNCTION PATH = CURRENT FUNCTION PATH, db2gse 
 
CREATE TABLE sample_geomcollections (id INTEGER, 
  geometry ST_Geometry)
 
INSERT INTO sample_geomcollections(id, geometry)
VALUES
    (4001, ST_Geometry( ST_MultiPoint('multipoint(1 2, 4 3)', 1)) ),
INSERT INTO sample_geomcollections(id, geometry)
VALUES
    (4002, ST_Geometry( ST_MultiLineString('multilinestring(
                           (33 2, 34 3, 35 6),
                           (28 4, 29 5, 31 8, 43 12),
                           (39 3, 37 4, 36 7))', 1)) ),
INSERT INTO sample_geomcollections(id, geometry)
VALUES
    (4003, ST_Geometry( ST_MultiPolygon('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)) 
    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))