Running PostGIS 3.2 on PostgreSQL 14.
I have a layer of linestrings with two columns,
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.