pgr_dijkstra with dynamic IDs

Question

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
...

Answer 1

You can do some string concatenation

...
    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
...