By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE t (
gid integer primary key,
geom geometry(Linestring, 4326)
);
INSERT INTO t VALUES
(1, 'SRID=4326;LINESTRING(0 0, 1 0, 1 1, 0 1)'),
(2, 'SRID=4326;LINESTRING(3 4, 5 6, 7 8, 9 10)');
2 rows affected
SELECT * FROM (
SELECT gid,ST_AsText(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)) l
WHERE line IS NOT NULL;
| gid | line |
|---|---|
| 1 | LINESTRING(0 0,1 0) |
| 1 | LINESTRING(1 0,1 1) |
| 1 | LINESTRING(1 1,0 1) |
| 2 | LINESTRING(3 4,5 6) |
| 2 | LINESTRING(5 6,7 8) |
| 2 | LINESTRING(7 8,9 10) |
SELECT * FROM (
SELECT gid,ST_AsText(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)) l
WHERE line IS NOT NULL;
| gid | line |
|---|---|
| 1 | LINESTRING(0 0,1 0) |
| 1 | LINESTRING(1 0,1 1) |
| 1 | LINESTRING(1 1,0 1) |
| 2 | LINESTRING(3 4,5 6) |
| 2 | LINESTRING(5 6,7 8) |
| 2 | LINESTRING(7 8,9 10) |
SELECT * FROM (
SELECT gid,
ST_AsText(ST_MakeLine(LAG(j.geom) OVER (PARTITION BY gid ORDER BY j.path),j.geom)) AS line
FROM t,LATERAL ST_DumpPoints(geom) j(path,geom)) i
WHERE line IS NOT NULL;
| gid | line |
|---|---|
| 1 | LINESTRING(0 0,1 0) |
| 1 | LINESTRING(1 0,1 1) |
| 1 | LINESTRING(1 1,0 1) |
| 2 | LINESTRING(3 4,5 6) |
| 2 | LINESTRING(5 6,7 8) |
| 2 | LINESTRING(7 8,9 10) |