2

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.

1 Answer 1

5

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 
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.