TransWikia.com

Bounding box in Dijkstra algorithm (many to many)

Geographic Information Systems Asked by Michal De on June 16, 2021

I apologize for the delicate duplication of my TOPIC but I am still standing.

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 this topic but I don’t know, how I can change ID number to nearest_mz and nearest_poi from my table.

One Answer

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.

Correct answer by geozelot on June 16, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP