Find nearest neighbour for set of points [duplicate]

Question

Using PostgreSQL and PostGIS. I have two sets of point data (geoms), one is a node graph indicating roads and the other represents street crossings. For my set of crossing data I want to find the single closest node to all the crossings. And use these geoms for further analysis. However, I am having trouble with nearest neighbour techniques as they mainly seem to only function for one point instead of for a set of points e.g.

SELECT
  streets.gid,
  streets.name
FROM
  nyc_streets streets
ORDER BY
  streets.geom <->
  (SELECT geom FROM nyc_subway_stations WHERE name = 'Broad St')
LIMIT 10;

Answer 1

This is not possible with a simple query.

You can use a correlated subquery: for each crossing, look up the nearest road:

SELECT crossings.id,
       (SELECT id
        FROM roads
        ORDER BY roads.geom <-> crossings.geom
        LIMIT 1
       ) AS road_id
FROM crossings;

A lateral join works in the same way (the subquery is run for each row in the other table), but makes it easier to return more than one column:

SELECT crossings.id,
       nearest_road.id
FROM crossings
CROSS JOIN LATERAL (
  SELECT id
  FROM roads
  ORDER BY roads.geom <-> crossings.geom
  LIMIT 1
) AS nearest_road;