ST_GeometryN
Name
ST_GeometryN — Return an element of a geometry collection.
Synopsis
geometry
ST_GeometryN
(
geometry
geomA
, integer
n
)
;
Description
Return the 1-based Nth element geometry of an input geometry which is a GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING, MULTICURVE, MULTI)POLYGON, or POLYHEDRALSURFACE. Otherwise, returns NULL.
Index is 1-based as for OGC specs since version 0.8.0. Previous versions implemented this as 0-based instead. |
To extract all elements of a geometry, ST_Dump is more efficient and works for atomic geometries. |
Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.
Changed: 2.0.0 Prior versions would return NULL for singular geometries. This was changed to return the geometry for ST_GeometryN(..,1) case.
This method implements the OGC Simple Features Implementation Specification for SQL 1.1.
This method implements the SQL/MM specification. SQL-MM 3: 9.1.5
This function supports 3d and will not drop the z-index.
This method supports Circular Strings and Curves
This function supports Polyhedral surfaces.
This function supports Triangles and Triangulated Irregular Network Surfaces (TIN).
Standard Examples
--Extracting a subset of points from a 3d multipoint SELECT n, ST_AsEWKT(ST_GeometryN(geom, n)) As geomewkt FROM ( VALUES (ST_GeomFromEWKT('MULTIPOINT((1 2 7), (3 4 7), (5 6 7), (8 9 10))') ), ( ST_GeomFromEWKT('MULTICURVE(CIRCULARSTRING(2.5 2.5,4.5 2.5, 3.5 3.5), (10 11, 12 11))') ) )As foo(geom) CROSS JOIN generate_series(1,100) n WHERE n <= ST_NumGeometries(geom); n | geomewkt ---+----------------------------------------- 1 | POINT(1 2 7) 2 | POINT(3 4 7) 3 | POINT(5 6 7) 4 | POINT(8 9 10) 1 | CIRCULARSTRING(2.5 2.5,4.5 2.5,3.5 3.5) 2 | LINESTRING(10 11,12 11) --Extracting all geometries (useful when you want to assign an id) SELECT gid, n, ST_GeometryN(geom, n) FROM sometable CROSS JOIN generate_series(1,100) n WHERE n <= ST_NumGeometries(geom);
Polyhedral Surfaces, TIN and Triangle Examples
-- Polyhedral surface example -- Break a Polyhedral surface into its faces SELECT ST_AsEWKT(ST_GeometryN(p_geom,3)) As geom_ewkt FROM (SELECT ST_GeomFromEWKT('POLYHEDRALSURFACE( ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)), ((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)), ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)), ((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)), ((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)), ((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1)) )') AS p_geom ) AS a; geom_ewkt ------------------------------------------ POLYGON((0 0 0,1 0 0,1 0 1,0 0 1,0 0 0))
-- TIN -- SELECT ST_AsEWKT(ST_GeometryN(geom,2)) as wkt FROM (SELECT ST_GeomFromEWKT('TIN ((( 0 0 0, 0 0 1, 0 1 0, 0 0 0 )), (( 0 0 0, 0 1 0, 1 1 0, 0 0 0 )) )') AS geom ) AS g; -- result -- wkt ------------------------------------- TRIANGLE((0 0 0,0 1 0,1 1 0,0 0 0))