add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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 pipes (id int, geom geometry(multilinestring));
CREATE TABLE structures (id int, geom geometry(multipoint));

INSERT INTO pipes VALUES
(1,'0105000020950B00000100000001020000000200000046243EC3282608418D28242D6C1B3D4128531BE88A2608418284B3EF561B3D41'),
(2,'0105000020950B0000010000000102000000020000004ABF2CBC86B108413B93650696323D413C487924CCB10841925D490495323D41');

INSERT INTO structures VALUES
(1,'0104000020950B00000100000001010000001026FA48113B07410D6A8412CF1D3D41'),
(2,'0104000020950B0000010000000101000000BA1BF246E6DD0741D064CB58C2E43C41');
CREATE TABLE

CREATE TABLE

INSERT 0 2

INSERT 0 2
SELECT p.id,
(dump_line).geom,
ST_EndPoint((dump_line).geom) AS downstream,
geom_closest_downstream,
ST_StartPoint((dump_line).geom) AS upstream,
geom_closest_upstream
FROM pipes p,
LATERAL ST_Dump(p.geom) dump_line,
LATERAL (SELECT s.geom
FROM structures s
ORDER BY ST_EndPoint((dump_line).geom)<->s.geom
LIMIT 1) j (geom_closest_downstream),
LATERAL (SELECT s.geom
FROM structures s
ORDER BY ST_StartPoint((dump_line).geom)<->s.geom
LIMIT 1) i (geom_closest_upstream);
id geom downstream geom_closest_downstream upstream geom_closest_upstream
1 0102000020950B00000200000046243EC3282608418D28242D6C1B3D4128531BE88A2608418284B3EF561B3D41 0101000020950B000028531BE88A2608418284B3EF561B3D41 0104000020950B00000100000001010000001026FA48113B07410D6A8412CF1D3D41 0101000020950B000046243EC3282608418D28242D6C1B3D41 0104000020950B00000100000001010000001026FA48113B07410D6A8412CF1D3D41
2 0102000020950B0000020000004ABF2CBC86B108413B93650696323D413C487924CCB10841925D490495323D41 0101000020950B00003C487924CCB10841925D490495323D41 0104000020950B00000100000001010000001026FA48113B07410D6A8412CF1D3D41 0101000020950B00004ABF2CBC86B108413B93650696323D41 0104000020950B00000100000001010000001026FA48113B07410D6A8412CF1D3D41 
SELECT 2
SELECT p.id,
ST_AsText((dump_line).geom) AS dump_line,
ST_AsText(ST_EndPoint((dump_line).geom)) AS downstream,
ST_AsText(geom_closest_downstream) AS geom_closest_downstream,
ST_AsText(ST_StartPoint((dump_line).geom)) AS upstream,
ST_AsText(geom_closest_upstream) AS geom_closest_upstream
FROM pipes p,
LATERAL ST_Dump(p.geom) dump_line,
LATERAL (SELECT s.geom
FROM structures s
ORDER BY ST_EndPoint((dump_line).geom)<->s.geom
LIMIT 1) j (geom_closest_downstream),
LATERAL (SELECT s.geom
FROM structures s
ORDER BY ST_StartPoint((dump_line).geom)<->s.geom
LIMIT 1) i (geom_closest_upstream);
id dump_line downstream geom_closest_downstream upstream geom_closest_upstream
1 LINESTRING(197829.09533336962 1907564.1763329834,197841.36333336798 1907542.9363329713) POINT(197841.36333336798 1907542.9363329713) MULTIPOINT(190306.16063337075 1908175.0723329813) POINT(197829.09533336962 1907564.1763329834) MULTIPOINT(190306.16063337075 1908175.0723329813)
2 LINESTRING(202288.84188222355 1913494.0249874133,202297.51780945237 1913493.0167444688) POINT(202297.51780945237 1913493.0167444688) MULTIPOINT(190306.16063337075 1908175.0723329813) POINT(202288.84188222355 1913494.0249874133) MULTIPOINT(190306.16063337075 1908175.0723329813) 
SELECT 2