I've downloaded

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)?

CodePudding user response：

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 ;