1

I am looking to find the nearest neighbor between two point tables in PostGIS and return the ids of both along with the line geometry. I have been able to determine the distance for the nearest between the two datasets but how do I return the line geometry.

select a.id, a._unique, closest_pt.dist
into nearest_neighbor_lines
from pois_1600 a
CROSS JOIN LATERAL
  (SELECT
     id , 
     a.geom <-> b.geom as dist
     FROM intersections b
     ORDER BY a.geom <-> b.geom) AS closest_pt;
1

Just ST_MakeLine the the initial and the closest point:

SELECT a.id,
       a._unique,
       closest_pt.dist,
       closest_pt.line_geom
INTO   nearest_neighbor_lines
FROM   pois_1600 AS a
CROSS JOIN LATERAL (
  SELECT id,
         ST_MakeLine(a.geom, b.geom) AS line_geom,
         a.geom <-> b.geom AS dist
  FROM   intersections AS b
  ORDER BY
         a.geom <-> b.geom
  LIMIT  1
) AS closest_pt;

Note that you have to LIMIT 1 in the join query to get only the one closest point each.

  • you are my PostGIS guru. Always coming thru in the clutch. Thanks again. – wtgeographer Aug 26 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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