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