Description

Returns the 1-based Nth element of a GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING, MULTICURVE, or MULTIPOLYGON. For a unitary geometry, including a TIN or PolyhedralSurface, an index of 1 returns the input geometry and any other index returns NULL. Use ST_PatchN and ST_NumPatches to access and count the faces of a TIN or PolyhedralSurface.

	L'index commence à 1 pour respecter les spécificarions OGC depuis la version 0.8.0. Dans les versions antérieures, l'index commençait à 0.

	Pour extraire tous les éléments d'une géométrie, ST_Dump est plus efficace et fonctionne pour les géométries atomiques.

Amélioration : 2.0.0 introduction du support TIN, Triangles et surfaces polyédriques.

Changement : 2.0.0. Les versions antérieures renvoient NULL pour les géometries simples (un seul objet). Renvoie désormais la géométrie pour le cas ST_GeometryN(..,1).

Changed: 3.6.0 TIN and PolyhedralSurface are treated as unitary geometries and are not decomposed into patches; use ST_PatchN to access faces.

Cette méthode implémente la spécification OGC Simple Features Implementation Specification for SQL 1.1.

Cette méthode implémente la spécification SQL/MM. SQL-MM 3 : 9.1.5

Cette fonction prend en charge la 3D et ne supprime pas l'indice z.

Cette méthode prend en charge les types Circular String et Curve.

Cette fonction prend en charge les surfaces Polyhedral.

Cette fonction prend en charge les triangles et les réseaux irréguliers triangulés (TIN).

Exemples standard

Extracting a subset of points from a 3D multipoint.

SELECT n, ST_GeometryN(geom, n) As geomewkt
FROM (
VALUES
    (1, 'MULTIPOINT((1 2 7), (3 4 7), (5 6 7), (8 9 10))'::geometry ),
    (2, 'MULTICURVE(CIRCULARSTRING(2.5 2.5,4.5 2.5, 3.5 3.5), (10 11, 12 11))'::geometry )
    ) As foo(id, geom)
    CROSS JOIN generate_series(1,100) n
WHERE n <= ST_NumGeometries(geom)
ORDER BY id, n;

 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)

1 | 0101000080000000000000F03F00000000000000400000000000001C40
2 | 0101000080000000000000084000000000000010400000000000001C40
3 | 0101000080000000000000144000000000000018400000000000001C40
4 | 0101000080000000000000204000000000000022400000000000002440
1 | 01080000000300000000000000000004400000000000000440000000000000124000000000000004400000000000000C400000000000000C40
2 | 0102000000020000000000000000002440000000000000264000000000000028400000000000002640

Figure

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);

Exemples TIN, Triangle et Surfaces Polyédriques

A PolyhedralSurface or TIN is treated as a single geometry, not as a collection of faces. ST_GeometryN does not decompose the surface into patches, so an index greater than 1 returns NULL. Use ST_PatchN and ST_NumPatches to access individual faces.

This example requests the third element of a PolyhedralSurface.

SELECT ST_GeometryN(p_geom, 3) As geom_ewkt
  FROM (SELECT '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))
)'::geometry  AS p_geom )  AS a;

NULL

Figure

This example requests the second element of a TIN.

SELECT ST_GeometryN(geom, 2) as wkt
  FROM
    (SELECT
       '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
            ))
            )'::geometry  AS geom
    ) AS g;

NULL

Figure

Voir aussi

ST_Dump, ST_NumGeometries, ST_PatchN, ST_NumPatches