名称
ST_DumpPoints — 返回几何图形中坐标的一组 geometry_dump 行。
大纲
geometry_dump[] ST_DumpPoints(geometry geom);
描述
提取几何图形坐标（顶点）的集合返回函数 (SRF)。它返回一组geometry_dump行，每行包含一个几何图形（geom字段）和一个整数数组（path字段）。
-
geom字段POINT表示所提供几何图形的坐标。 -
path字段（integer[]）是枚举所提供几何图形元素中的坐标位置的索引。索引是从 1 开始的。例如，对于LINESTRING，路径为{i}，其中i是LINESTRING中的第n个坐标。 对于POLYGON，路径为{i,j}，其中i是环编号（1 是外环；后面是内环），j是环中的坐标位置。
要获取包含坐标的单个几何图形，请使用 ST_Points。
增强：2.1.0 速度更快。重新实现为原生C语言。
增强功能：引入了2.0.0 对多面体曲面、三角形和三角网的支持。
可用性：1.1.0
此方法支持圆形字符串和曲线。
该函数支持多面体曲面。
此函数支持三角形和不规则三角网面 (TIN)。
该函数支持 3d 并且不会丢失 z-index。
示例
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;
栅格输出
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)
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;
栅格输出
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;
栅格输出
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))
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;
栅格输出
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;
栅格输出
path | wkt
------+------------
{1,1} | POINT(0 0)
{1,2} | POINT(0 9)
{1,3} | POINT(9 0)
{1,4} | POINT(0 0)
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;
栅格输出
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)
Figure