ST_DumpPoints

ST_DumpPoints
Föregående	7.4. Geometriåtkomst	Nästa

Namn

ST_DumpPoints — Returnerar en uppsättning geometry_dump-rader för koordinaterna i en geometri.

Synopsis

geometry_dump[] ST_DumpPoints(geometry geom);

Beskrivning

En set-returning function (SRF) som extraherar koordinaterna (hörnen) för en geometri. Den returnerar en uppsättning geometry_dump -rader, som var och en innehåller en geometri (fältetgeom) och en array av heltal (fältet path).

geom-fältet POINTsrepresenterar koordinaterna för den medföljande geometrin.
path-fältet (ett integer[]) är ett index som räknar upp koordinatpositionerna i elementen i den angivna geometrin. Indexen är 1-baserade. Till exempel för en LINESTRING är banorna {i} där i är den n:te koordinaten i LINESTRING. För en POLYGON är banorna {i,j} där i är ringnumret (1 är den yttre ringen; de inre ringarna följer) och j är koordinatpositionen i ringen.

För att få en enda geometri som innehåller koordinaterna, använd ST_Points.

Förbättrad: 2.1.0 Snabbare hastighet. Återimplementerad som native-C.

Förbättrad: 2.0.0 stöd för polyhedrala ytor, trianglar och TIN infördes.

Tillgänglighet: 1.5.0

Denna metod stöder cirkulära strängar och kurvor.

Denna funktion stöder polyedriska ytor.

Denna funktion stöder trianglar och triangulerade oregelbundna nätverksytor (TIN).

Denna funktion stöder 3d och kommer inte att tappa z-index.

Exempel

Explode a table of LineStrings into their vertices.

Code

SELECT edge_id, (dp).path[1] As index,(dp).geom As wktnode
FROM (SELECT 1 As edge_id,
    ST_DumpPoints(ST_GeomFromText('LINESTRING(1 2,3 4,10 10)')) AS dp
     UNION ALL
     SELECT 2 As edge_id,
    ST_DumpPoints(ST_GeomFromText('LINESTRING(3 5,5 6,9 10)')) AS dp
   ) As foo;

Rasterutdata

edge_id | index |    wktnode
---------+-------+--------------
       1 |     1 | POINT(1 2)
       1 |     2 | POINT(3 4)
       1 |     3 | POINT(10 10)
       2 |     1 | POINT(3 5)
       2 |     2 | POINT(5 6)
       2 |     3 | POINT(9 10)

1 | 1 | 0101000000000000000000F03F0000000000000040
1 | 2 | 010100000000000000000008400000000000001040
1 | 3 | 010100000000000000000024400000000000002440
2 | 1 | 010100000000000000000008400000000000001440
2 | 2 | 010100000000000000000014400000000000001840
2 | 3 | 010100000000000000000022400000000000002440

Figure

Dump every vertex from a mixed GeometryCollection.

Code

SELECT path,geom
FROM (
  SELECT (ST_DumpPoints(g.geom)).*
  FROM
    (SELECT
       'GEOMETRYCOLLECTION(
          POINT ( 0 1 ),
          LINESTRING ( 0 3,3 4 ),
          POLYGON (( 2 0,2 3,0 2,2 0 )),
          POLYGON (( 3 0,3 3,6 3,6 0,3 0 ),
                   ( 5 1,4 2,5 2,5 1 )),
          MULTIPOLYGON (
                  (( 0 5,0 8,4 8,4 5,0 5 ),
                   ( 1 6,3 6,2 7,1 6 )),
                  (( 5 4,5 8,6 7,5 4 ))
          )
        )'::geometry AS geom
    ) AS g
  ) j;

Rasterutdata

path    | st_astext
-----------+------------
 {1,1}     | POINT(0 1)
 {2,1}     | POINT(0 3)
 {2,2}     | POINT(3 4)
 {3,1,1}   | POINT(2 0)
 {3,1,2}   | POINT(2 3)
 {3,1,3}   | POINT(0 2)
 {3,1,4}   | POINT(2 0)
 {4,1,1}   | POINT(3 0)
 {4,1,2}   | POINT(3 3)
 {4,1,3}   | POINT(6 3)
 {4,1,4}   | POINT(6 0)
 {4,1,5}   | POINT(3 0)
 {4,2,1}   | POINT(5 1)
 {4,2,2}   | POINT(4 2)
 {4,2,3}   | POINT(5 2)
 {4,2,4}   | POINT(5 1)
 {5,1,1,1} | POINT(0 5)
 {5,1,1,2} | POINT(0 8)
 {5,1,1,3} | POINT(4 8)
 {5,1,1,4} | POINT(4 5)
 {5,1,1,5} | POINT(0 5)
 {5,1,2,1} | POINT(1 6)
 {5,1,2,2} | POINT(3 6)
 {5,1,2,3} | POINT(2 7)
 {5,1,2,4} | POINT(1 6)
 {5,2,1,1} | POINT(5 4)
 {5,2,1,2} | POINT(5 8)
 {5,2,1,3} | POINT(6 7)
 {5,2,1,4} | POINT(5 4)
(29 rows)

Collect the dumped vertices to visualize them over the source geometry.

Code

WITH source(geom) AS (VALUES (
  'GEOMETRYCOLLECTION(
     POINT (0 1),
     LINESTRING (0 3,3 4),
     POLYGON ((2 0,2 3,0 2,2 0)),
     POLYGON ((3 0,3 3,6 3,6 0,3 0),(5 1,4 2,5 2,5 1)),
     MULTIPOLYGON (((0 5,0 8,4 8,4 5,0 5),(1 6,3 6,2 7,1 6)),
                   ((5 4,5 8,6 7,5 4))))'::geometry
))
SELECT ST_Collect((dp).geom ORDER BY (dp).path) AS vertices
FROM source
CROSS JOIN LATERAL ST_DumpPoints(source.geom) AS dp;

Rasterutdata

MULTIPOINT((0 1),(0 3),(3 4),(2 0),(2 3),(0 2),(2 0),(3 0),(3 3),(6 3),(6 0),(3 0),(5 1),(4 2),(5 2),(5 1),(0 5),(0 8),(4 8),(4 5),(0 5),(1 6),(3 6),(2 7),(1 6),(5 4),(5 8),(6 7),(5 4))

01040000001D00000001010000000000000000000000000000000000F03F01010000000000000000000000000000000000084001010000000000000000000840000000000000104001010000000000000000000040000000000000000001010000000000000000000040000000000000084001010000000000000000000000000000000000004001010000000000000000000040000000000000000001010000000000000000000840000000000000000001010000000000000000000840000000000000084001010000000000000000001840000000000000084001010000000000000000001840000000000000000001010000000000000000000840000000000000000001010000000000000000001440000000000000F03F01010000000000000000001040000000000000004001010000000000000000001440000000000000004001010000000000000000001440000000000000F03F0101000000000000000000000000000000000014400101000000000000000000000000000000000020400101000000000000000000104000000000000020400101000000000000000000104000000000000014400101000000000000000000000000000000000014400101000000000000000000F03F0000000000001840010100000000000000000008400000000000001840010100000000000000000000400000000000001C400101000000000000000000F03F0000000000001840010100000000000000000014400000000000001040010100000000000000000014400000000000002040010100000000000000000018400000000000001C40010100000000000000000014400000000000001040

Figure

This example uses a cube represented as a PolyhedralSurface.

Code

SELECT (g.gdump).path,(g.gdump).geom as wkt
  FROM
    (SELECT
       ST_DumpPoints('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 gdump
    ) AS g;

Rasterutdata

path   |     wkt
---------+--------------
 {1,1,1} | POINT(0 0 0)
 {1,1,2} | POINT(0 0 1)
 {1,1,3} | POINT(0 1 1)
 {1,1,4} | POINT(0 1 0)
 {1,1,5} | POINT(0 0 0)
 {2,1,1} | POINT(0 0 0)
 {2,1,2} | POINT(0 1 0)
 {2,1,3} | POINT(1 1 0)
 {2,1,4} | POINT(1 0 0)
 {2,1,5} | POINT(0 0 0)
 {3,1,1} | POINT(0 0 0)
 {3,1,2} | POINT(1 0 0)
 {3,1,3} | POINT(1 0 1)
 {3,1,4} | POINT(0 0 1)
 {3,1,5} | POINT(0 0 0)
 {4,1,1} | POINT(1 1 0)
 {4,1,2} | POINT(1 1 1)
 {4,1,3} | POINT(1 0 1)
 {4,1,4} | POINT(1 0 0)
 {4,1,5} | POINT(1 1 0)
 {5,1,1} | POINT(0 1 0)
 {5,1,2} | POINT(0 1 1)
 {5,1,3} | POINT(1 1 1)
 {5,1,4} | POINT(1 1 0)
 {5,1,5} | POINT(0 1 0)
 {6,1,1} | POINT(0 0 1)
 {6,1,2} | POINT(1 0 1)
 {6,1,3} | POINT(1 1 1)
 {6,1,4} | POINT(0 1 1)
 {6,1,5} | POINT(0 0 1)
(30 rows)

This example uses a Triangle geometry.

Code

SELECT (g.gdump).path,(g.gdump).geom as wkt
  FROM
    (SELECT
       ST_DumpPoints('TRIANGLE ((
                0 0,
                0 9,
                9 0,
                0 0
            ))' ) AS gdump
    ) AS g;

Rasterutdata

path |    wkt
------+------------
 {1,1} | POINT(0 0)
 {1,2} | POINT(0 9)
 {1,3} | POINT(9 0)
 {1,4} | POINT(0 0)

{1,1} | 010100000000000000000000000000000000000000
{1,2} | 010100000000000000000000000000000000002240
{1,3} | 010100000000000000000022400000000000000000
{1,4} | 010100000000000000000000000000000000000000

Figure

This example uses a TIN geometry.

Code

SELECT (g.gdump).path,(g.gdump).geom as wkt
  FROM
    (SELECT
       ST_DumpPoints('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 gdump
    ) AS g;

Rasterutdata

path   |     wkt
---------+--------------
 {1,1,1} | POINT(0 0 0)
 {1,1,2} | POINT(0 0 1)
 {1,1,3} | POINT(0 1 0)
 {1,1,4} | POINT(0 0 0)
 {2,1,1} | POINT(0 0 0)
 {2,1,2} | POINT(0 1 0)
 {2,1,3} | POINT(1 1 0)
 {2,1,4} | POINT(0 0 0)
(8 rows)

{1,1,1} | 0101000080000000000000000000000000000000000000000000000000
{1,1,2} | 010100008000000000000000000000000000000000000000000000F03F
{1,1,3} | 01010000800000000000000000000000000000F03F0000000000000000
{1,1,4} | 0101000080000000000000000000000000000000000000000000000000
{2,1,1} | 0101000080000000000000000000000000000000000000000000000000
{2,1,2} | 01010000800000000000000000000000000000F03F0000000000000000
{2,1,3} | 0101000080000000000000F03F000000000000F03F0000000000000000
{2,1,4} | 0101000080000000000000000000000000000000000000000000000000

Figure

Se även

geometry_dump, Avsnitt 13.6, ”PostGIS Geometri / Geografi / Raster Dumpfunktioner”, ST_Dump, ST_DumpRings, ST_Points

Föregående	Upp	Nästa
ST_Dump	Hem	ST_DumpSegments