Speeding Up pgr_dijkstra using bounding box in PostGis2.0

Question

I'm using PostGIS for the first time to calculate distances between two points, for a driving school booking system I am working on. I have loaded in all of the maps for Australia, but the query I wrote takes a good 5-6 seconds to execute. Most of the journeys are very short so I was thinking of using a bounding box but the relevant function (dijkstra_sp_delta) appears to be deprecated. Does anyone have any insight on how to implement this or speed up these kinds of functions more generally? I would like to get the query time down to 1 second or less.

SELECT SUM(
        (SELECT ST_length(ST_Transform(the_geom,28355))
          FROM ways w
          WHERE w.gid = id2
        )) as distance_in_metres
      FROM pgr_dijkstra(
        'SELECT gid AS id, source, target, ST_length(ST_Transform(the_geom,28355))/1000*60/maxspeed_forward AS cost 
        FROM ways', 247201, 298103, false, false);

score 6 · Accepted Answer · answered Apr 27 '15 at 10:22

Schemy just build the build the bounding box check in your query or wrap the pgr_dikstra in a function that does

e.g.

SELECT SUM(
    (SELECT ST_length(ST_Transform(the_geom,28355))
      FROM ways w
      WHERE w.gid = id2
    )) as distance_in_metres
  FROM pgr_dijkstra(
    'SELECT gid AS id, source, target, 
         ST_length(ST_Transform(the_geom,28355))/1000*60/maxspeed_forward AS cost 
    FROM ways 
     WHERE the_geom && ST_Expand(
    (SELECT ST_Collect(the_geom) FROM ways_vertices_pgr WHERE id IN( 247201,298103 ) ) , 0.01)  ', 
              247201, 298103, false, false);

You'll need to tweak the 0.01 a little bit.

Thanks so much. Your answer was spot on. Query time is down from around 5-6 seconds to 300ms or less depending on route. — Jake Schoermer, Apr 29 '15 at 06:33

Speeding Up pgr_dijkstra using bounding box in PostGis2.0

1 Answers1

Linked