Skip to content
Advertisement

How to split linestrings into their individual segments?

I am new to PostGIS. I have a set of (thousands) of Linestrings and associated multiple points along the line. I want to divide each line into a set of line segments consisting of only 2 points. I find a few answers, But none of the answers I want. for example https://gis.stackexchange.com/questions/21648/explode-multilinestring-into-individual-segments-in-postgis-1-5

My question:

My question picture

Advertisement

Answer

Just dump the points of your large LineString and create small ones using ST_MakeLine with a single point and the next one in the sequence using the window function LEAD(). Then finally create a MultiLinestring with the small LineStrings using ST_Collect:

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;

Demo: 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)
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement