How can I use the ORDER BY with LIMIT 1 statements multiple times in the same query?

Question

I've downloaded OpenStreetMap data from Geofabrik where we can find information about road geometries. I know that if I have a random point and I need to see the closest road that I have from it I can use the following query on PostgreSQL:

SELECT way, osm_id 
FROM osm AS osm 
ORDER BY osm.way <-> 'SRID=3857;POINT(-3890646.5744145643 -899377.0801662721)'::GEOMETRY LIMIT 1 ;

Here I have hardcoded the point POINT(-3890646.5744145643 -899377.0801662721). This query is functional and it returns the result:

It finds out which is the closest road from my point and gives me the osm_id of this road. The problem here is that I'd like to get points from a different table where I'll have multiple geometries of points, not just a single one. Since this query requires the use of LIMIT 1 in order to work. What approach could I use to run this same query for each point geometry that I have on a different_table?

I can see how I'd solve this problem by accessing PostgreSQL with a programming language since I'd be able to make a query to get all text points from different_table and then I'd loop through all of them while using my first query and getting its result one by one. But I'm not sure that it would be the ideal solution. I'd like a solution where I use only PostgreSQL... How can I do it? How can I use the ORDER BY with LIMIT 1 statements multiple times and execute it for each geometry that exists in a different table (each row would be a different point like 'SRID=3857;POINT(-3890646.5744145643 -899377.0801662721)'::GEOMETRY)?

Answer 1

You need a lateral join, which executes one subquery once for each row of the other table:

SELECT diff_id, way, osm_id, osm.way <-> diff_geog as distance
FROM different_table cross join lateral (
    select way, osm_id from osm  
    ORDER BY osm.way <-> diff_geog LIMIT 1
) osm ;