Cannot figure out how to reference outer table in nearest neighbor subquery

Question

Running PostGIS 3.2 on PostgreSQL 14.

I have a layer of linestrings with two columns, id and geom.

I am trying to select each feature together with its nearest non-touching neighbor, but I'm getting a reference error and can't figure out how to restructure my query to get it to work.

SELECT l1.id AS my_id
     , l1.geom AS my_geom
     , subq.nn_id
     , subq.nn_geom
     , subq.nn_dist
  FROM linestrings AS l1
  LEFT JOIN (SELECT l2.id AS nn_id
                  , l2.geom AS nn_geom
                  , l2.geom <-> l1.geom AS nn_dist
               FROM linestrings AS l2
           ORDER BY nn_dist
              LIMIT 1
       ) subq ON NOT ST_Touches(l1.geom, l2.geom)

SQL Error [42P01]: ERROR: invalid reference to FROM-clause entry for table "l1"
  Hint: There is an entry for table "l1", but it cannot be referenced from this part of the query.

Answer 1

In order to access records of the running table inside a correlated sub-query, a LATERAL join statement is required; I also assume here that 'non-touching' actually means 'non-intersecting' and apply ST_DWithin as filter (depending on geometric complexity it may be faster than ST_Intersects):

SELECT
  l1.id AS my_id,
  l1.geom AS my_geom,
  subq.nn_id,
  subq.nn_geom,
  subq.nn_dist
FROM
  linestrings AS l1
  LEFT JOIN LATERAL (
    SELECT
      l2.id AS nn_id,
      l2.geom AS nn_geom,
      l2.geom <-> l1.geom AS nn_dist
    FROM
      linestring AS l2
    WHERE
      NOT ST_DWithin(l1.geom, l2.geom, 0)
    ORDER BY
      nn_dist
    LIMIT
      1
  ) AS subq ON TRUE 
;