Using ST_ClosestPoint, ST_StartPoint, and ST_EndPoint to find closest object to beginning and end points of lines

I need to find the closest points to either end of a line using geometry from two separate tables. So far I was able to use the following to find the geometry of the end points:

SELECT "id", ST_StartPoint(dmp.geom) AS upstream,
       ST_EndPoint(dmp.geom) AS downstream
FROM sewers.pipes,
        LATERAL ST_Dump("geom") AS dmp
;

Using this, I want to use ST_ClosestPoint to find structures that are the closest to the end points of pipes. This is what I’ve come up with so far:

SELECT ST_ClosestPoint('POINT(SELECT ST_StartPoint(dmp.geom) AS upstream
FROM sewers.pipes,
        LATERAL ST_Dump("geom") AS dmp)',
                      ('LINESTRING(SELECT geom from sewers.pipes)'))

However, this gives the following error:

ERROR: parse error - invalid geometry
Line 1: SELECT ST_ClosestPoint('POINT(SELECT ST_STartPoint(dmp.geom)...

HINT: "POINT(SE" <-- parse error at position 8 within geometry
SQL state: XX000
Character: 24

I have provided some sample data below.

sewers.pipes <– these are the lines

| id       | geom |
| -------- | -------------- |
| 822      | 0105000020950B00000100000001020000000200000046243EC3282608418D28242D6C1B3D4128531BE88A2608418284B3EF561B3D41        |
| 6660     | 0105000020950B0000010000000102000000020000004ABF2CBC86B108413B93650696323D413C487924CCB10841925D490495323D41            |

sewers.structures <– these are points

| id        | geom |
| --------  | -------------- |
| 2014      | 0104000020950B00000100000001010000001026FA48113B07410D6A8412CF1D3D41            |
| 22979     | 0104000020950B0000010000000101000000BA1BF246E6DD0741D064CB58C2E43C41            |

I know that there may be an error that comes from selecting multiple data points, eg. more than one row returned by a subquery used as an expression, so that may be an issue as well. Any help would be greatly appreciated.

Answer

Try with distinct LATERALs for upstream and downstream:

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 sewers.pipes p,
  LATERAL ST_Dump(p.geom) dump_line,
  LATERAL (SELECT s.geom 
           FROM sewers.structures s
           ORDER BY ST_EndPoint((dump_line).geom)<->s.geom 
           LIMIT 1) j (geom_closest_downstream),
  LATERAL (SELECT s.geom 
           FROM sewers.structures s
           ORDER BY ST_StartPoint((dump_line).geom)<->s.geom 
           LIMIT 1) i (geom_closest_upstream);

• To access the geometries from ST_Dump you have to wrap its output with parenthesis, e.g. (ST_Dump(multiline)).geom
• The operator <-> at the ORDER BY clause corresponds to distance. So, using it combined with a LIMIT 1 you get only the closest distance.

Demo: db<>fiddle