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

Question

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.

Answer 1

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.