ST_GeometryN — Return the 1-based Nth geometry if the geometry is a GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING, MULTICURVE or MULTIPOLYGON. Otherwise, return NULL.
geometry ST_GeometryN(
geometry geomA, integer n)
;
Return the 1-based Nth geometry if the geometry is a GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING, MULTICURVE or MULTIPOLYGON. Otherwise, return NULL.
Index is 1-based as for OGC specs since version 0.8.0. Previous versions implemented this as 0-based instead. |
If you want to extract all geometries, of a geometry, ST_Dump is more efficient and will also work for singular geoms. |
This method implements the OpenGIS Simple Features Implementation Specification for SQL.
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
--Extracting a subset of points from a 3d multipoint SELECT n, ST_AsEWKT(ST_GeometryN(the_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(the_geom) CROSS JOIN generate_series(1,100) n WHERE n <= ST_NumGeometries(the_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(the_geom, n) FROM sometable CROSS JOIN generate_series(1,100) n WHERE n <= ST_NumGeometries(the_geom);