ST_GeometryN

Returns the n^th geometry within a geometry object.

If n is out of range of the index, then NULL is returned.

Behavior type

Syntax

ST_GeometryN( g , n )

Arguments

g

Spatial object of type GEOMETRY.

n

The geometry's index number, 1-based.

Returns

GEOMETRY

Supported data types

Examples

The following examples show how to use ST_GeometryN.

Return the second geometry in a multipolygon:

=> CREATE TABLE multipolygon_geom (gid int, geom GEOMETRY(1000));
CREATE TABLE
=> COPY multipolygon_geom(gid, gx FILLER LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>9|MULTIPOLYGON(((2 6, 2 9, 6 9, 7 7, 4 6, 2 6)),((0 0, 0 5, 1 0, 0 0)),((0 2, 2 5, 4 5, 0 2)))
>>\.
=> SELECT gid, ST_AsText(ST_GeometryN(geom, 2)) FROM multipolygon_geom;
 gid |           ST_AsText
-----+--------------------------------
   9 | POLYGON ((0 0, 0 5, 1 0, 0 0))
(1 row)

Return all the geometries within a multipolygon:

=> CREATE TABLE multipolygon_geom (gid int, geom GEOMETRY(1000));
CREATE TABLE
=> COPY multipolygon_geom(gid, gx FILLER LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>9|MULTIPOLYGON(((2 6, 2 9, 6 9, 7 7, 4 6, 2 6)),((0 0, 0 5, 1 0, 0 0)),((0 2, 2 5, 4 5, 0 2)))
>>\.
=> CREATE TABLE series_numbers (numbs int);
CREATE TABLE
=> COPY series_numbers FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1
>> 2
>> 3
>> 4
>> 5
>> \.
=> SELECT numbs, ST_AsText(ST_GeometryN(geom, numbs))
   FROM multipolygon_geom, series_numbers
   WHERE ST_AsText(ST_GeometryN(geom, numbs)) IS NOT NULL
   ORDER BY numbs ASC;
 numbs |                ST_AsText
-------+------------------------------------------
     1 | POLYGON ((2 6, 2 9, 6 9, 7 7, 4 6, 2 6))
     2 | POLYGON ((0 0, 0 5, 1 0, 0 0))
     3 | POLYGON ((0 2, 2 5, 4 5, 0 2))
(3 rows)

See also