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