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;
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
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP