I am trying to use dynamic IDs so I can create a bounding box over the edges.
I do have a starting road, which I set up before the query. The closest road_id from a city's coordinates. (i.e. 923342 in this case).
From this road I need to calculate the distance to other cities.
The next query works, but it is way too slow (about 15s), since it selects all the 1.2M roads.
SELECT city.id, city.road_id, city.name, ( SELECT SUM(roads.km) FROM pgr_dijkstra( 'SELECT id, source, target, cost, reverse_cost FROM roads', (SELECT source FROM roads WHERE id = 923342), (SELECT target FROM roads WHERE id = city.road_id) ) AS dijkstra INNER JOIN roads ON roads.id = dijkstra.edge ) AS distance FROM cities AS city WHERE city.id IN (2136, 1984, 1850) ORDER BY distance ASC Now, in order to speed things up I created a bounding box over the edges:
SELECT city.id, city.road_id, city.name, ( SELECT SUM(roads.km) FROM pgr_dijkstra( 'SELECT id, source, target, cost, reverse_cost FROM roads ( SELECT ST_Expand(ST_Extent(geom_way), 0.1) as box FROM roads WHERE source = (SELECT source FROM roads WHERE id = 923342) OR target = (SELECT target FROM roads WHERE id = 12345678) ) AS bbox WHERE roads.geom_way && bbox.box ', (SELECT source FROM roads WHERE id = 923342), (SELECT target FROM roads WHERE id = city.road_id) ) AS dijkstra INNER JOIN roads ON roads.id = dijkstra.edge ) AS distance FROM cities AS city WHERE city.id IN (2136, 1984, 1850) ORDER BY distance ASC (12345678 is a test value retrieved manually).
Now the execution time is around ms. That's great!
The final request is to dynamically change that 12345678 value and replace it with city.road_id. I have no idea how to do that. I've tried using WITH clause, so I can select the roads within the bounding box previously, but I was unable use that virtual table inside the SQL text from pgr_dijkstra.
Looking for something along the lines of:
... SELECT SUM(roads.km) FROM pgr_dijkstra( 'SELECT id, source, target, cost, reverse_cost FROM roads ( SELECT ST_Expand(ST_Extent(geom_way), 0.1) as box FROM roads WHERE source = (SELECT source FROM roads WHERE id = 923342) OR target = (SELECT target FROM roads WHERE id = city.road_id) -- [using dynamic ID] ) AS bbox WHERE roads.geom_way && bbox.box ', (SELECT source FROM roads WHERE id = 923342), (SELECT target FROM roads WHERE id = city.road_id) ) AS dijkstra INNER JOIN roads ON roads.id = dijkstra.edge ... 
