how to split multilinestring into overlapping 2-point subsets (2-point linestrings)?

I want to split a multilinestring or linestring like LINESTRING(0 0, 1 0, 1 1, 0 1) into linestrings consisting of two points.

So the linestring above would become:

• LINESTRING(0 0, 1 0)
• LINESTRING(1 0, 1 1)
• LINESTRING(1 1, 0 1)

I would also like to be able to do the same with multilinestrings.

Answer

Use a LATERAL with ST_DumpPoints to dump the linestring’s points and in the SELECT clause use ST_MakeLine with the point of the current row and the preceding row using the window function LAG():

SELECT * FROM (
  SELECT ST_MakeLine(LAG(j.geom) OVER w,j.geom) AS line
  FROM t,LATERAL ST_DumpPoints(geom) j(path,geom)
  WINDOW w AS (PARTITION BY gid ORDER BY j.path 
               ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)) i
WHERE line IS NOT NULL;

Note: the frame ROWS BETWEEN 1 PRECEDING AND CURRENT ROW is pretty much optional in this case, but it is considered a good practice to keep window functions as explicit as possible, so that you always know exactly what is going on without having to rely on your memory or to look it up in the documentation. This should also work:

SELECT * FROM (
  SELECT ST_MakeLine(LAG(j.geom) OVER w,j.geom) AS line
  FROM t,LATERAL ST_DumpPoints(geom) j(path,geom)
  WINDOW w AS (PARTITION BY gid ORDER BY j.path)) i
WHERE line IS NOT NULL;

Demo: db<>fiddle