如何将线串分割成各自的线段？

问题描述

我是PostGIS的新手。我有一组(数千)线串和沿线关联的多个点。我想把每条线分成一组只有两个点的线段。我找到了几个答案，但没有一个是我想要的。例如https://gis.stackexchange.com/questions/21648/explode-multilinestring-into-individual-segments-in-postgis-1-5

我的问题：

推荐答案

只需转储大型LineString点并使用ST_MakeLine创建小点，然后使用窗口函数LEAD()创建序列中的下一个点。最后使用ST_Collect创建一个包含小LineStrings的多行字符串：

WITH j AS (
  SELECT gid, ST_MakeLine(j.geom,LEAD(j.geom) OVER w) AS line
  FROM t, ST_DumpPoints(geom) j (path,geom)
  WINDOW w AS (PARTITION BY gid ORDER BY j.path
               ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
)
SELECT ST_Collect(line) FROM j
GROUP BY gid;

演示：db<>fiddle

CREATE TABLE t (gid int, geom geometry(linestring,4326));
INSERT INTO t VALUES 
(1,'LINESTRING(1 1,2 2,3 3,4 4)'),
(2,'LINESTRING(3 3,7 7,4 4)'),
(3,'LINESTRING(5 5,6 6)');


WITH j AS (
  SELECT gid, ST_MakeLine(j.geom,LEAD(j.geom) OVER w) AS line
  FROM t, ST_DumpPoints(geom) j (path,geom)
  WINDOW w AS (PARTITION BY gid ORDER BY j.path
               ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
)
SELECT ST_AsText(ST_Collect(line)) FROM j
GROUP BY gid;
                   st_astext                    
------------------------------------------------
 MULTILINESTRING((1 1,2 2),(2 2,3 3),(3 3,4 4))
 MULTILINESTRING((3 3,7 7),(7 7,4 4))
 MULTILINESTRING((5 5,6 6))
(3 rows)

这篇关于如何将线串分割成各自的线段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！