# Bounding box in Dijkstra algorithm (many to many) [duplicate]

I have the table, which has information about pairs between source places and points (without geometry). I have used this code so far:

``````drop table if exists dijkstra;
create table dijkstra as (
SELECT a."id_pk"
, a.mz_id
, a.poi_id
, case when a.nearest_mz!=a.nearest_poi then SUM(a.cost)
else 0
end AS "tmp_cost"
FROM
( SELECT "id_pk", mz_id, poi_id, nearest_mz, nearest_poi
, (pgr_dijkstra( 'select id::integer, source, target
, ST_Length(geom) as cost from ways',
"nearest_mz", "nearest_poi", false )).*
FROM pairs_mz_poi_node where id_pk <100 -- limit for test )
AS a
GROUP BY a."id_pk"
, a.mz_id
, a.poi_id
, a.nearest_mz
, a.nearest_poi)
;
``````

It works fine, but now I have too many pairs and I have to calculate it faster than before.

I'm trying to add bounding box in my code.

I used this codes:

``````drop table if exists test;
create table test as (
SELECT a."id_pk"
, a.mz_id
, a.poi_id
, case when a.nearest_mz!=a.nearest_poi then SUM(a.cost)
else 0
end AS "tmp_cost"
FROM
( SELECT "id_pk", mz_id, poi_id, nearest_mz, nearest_poi
, pgr_dijkstra ( 'SELECT gid AS id, source, target,
ST_length(geom) AS cost FROM ways
WHERE geom && ST_Expand(
(SELECT ST_Collect(the_geom) FROM ways_vertices_pgr d
join pairs_mz_poi_node b
on b.nearest_mz=d.id
join pairs_mz_poi_node c
on c.nearest_poi=d.id
WHERE id IN( b.nearest_mz,c.nearest_poi ) )
nearest_mz i nearest_poi
, 1)  ',
"nearest_mz", "nearest_poi", false )).*
FROM pairs_mz_poi_node where id_pk <100 )
AS a
GROUP BY a."id_pk", a.mz_id
, a.poi_id
, a.nearest_mz, a.nearest_poi);
``````

And this:

``````drop table if exists test2;
Create table test2 as (
SELECT a."id_pk"
, a.mz_id
, a.poi_id
, case when a.nearest_mz!=a.nearest_poi then SUM(a.cost)
else 0
end AS "tmp_cost"
FROM
( SELECT "id_pk", mz_id, poi_id, nearest_mz, nearest_poi
, (pgr_dijkstra( 'WITH
buffer AS (SELECT id, ST_Buffer(the_geom, 1500) AS geom FROM ways_vertices_pgr)
select d.id::integer, d.source, d.target
, ST_Length(d.geom) as cost from ways d join buffer
on buffer.id=d.source or buffer.id=d.target
where d.geom=buffer.geom'
, "nearest_mz", "nearest_poi", false )).*
FROM pairs_mz_poi_node where id_pk <100 )
AS a
GROUP BY a."id_pk", a.mz_id
, a.poi_id
, a.nearest_mz, a.nearest_poi
);
``````

I found Speeding Up pgr_dijkstra using bounding box in PostGis2.0 but I don't know, how I can change ID number to nearest_mz and nearest_poi from my table.

Prepare the bbox and pass it as text into the `edges_sql`:

Updated

``````SELECT nodes.id,
SUM(pgr.cost) AS "Aggregated Cost"
FROM    (
SELECT pwn.id,
pwn."nearest_mz",
pwn."nearest_poi",
ST_Collect(vt1.geom, vt2.geom)::TEXT AS bbox
FROM   pairs_mz_poi_node AS pwn
JOIN   <vertice_table> AS vt1
ON   pwn."nearest_mz" = vt1.id
JOIN   <vertice_table> AS vt2
ON   pwn."nearest_poi" = vt2.id
) AS nodes,
LATERAL PGR_Dijkstra(
'
SELECT id::INT,
source,
target,
ST_Length(geom) AS cost
FROM   ways
WHERE  geom && ST_Expand(''' || nodes.bbox || '''::GEOMETRY, 0.001)
',
nodes."nearest_mz",
nodes."nearest_poi",
FALSE
) AS pgr
GROUP BY
nodes.id
;
``````

This works on the assumption that all relevant edges will tend to be within the bbox of the two vertices that you use as source and target; I added `ST_Expand` to slightly increase that bbox, allowing for slight detours in all directions from and to the source and target. You may want to adjust the size parameter.