ST_GeometryN
Returns the nth 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
Data Type | GEOMETRY | GEOGRAPHY (Perfect Sphere) | GEOGRAPHY (WGS84) |
Point | Yes | Yes | Yes |
Multipoint | Yes | Yes | Yes |
Linestring | Yes | Yes | Yes |
Multilinestring | Yes | Yes | Yes |
Polygon | Yes | Yes | Yes |
Multipolygon | Yes | Yes | Yes |
GeometryCollection |
No |
No | No |
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)