TransWikia.com

Nearest Neighbor Two Point Tables Return Line Geometry

Geographic Information Systems Asked by wtgeographer on January 16, 2021

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;

One Answer

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.

Correct answer by geozelot on January 16, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP