how to get the distance of sequence of nodes in pgr_dijkstra pgrouting?

Question

I have an array of integers(nodes or destinations) i.e array[2,3,4,5,6,8] that need to be visited in the given sequence. What I want is, to get the shortest distance using pgr_dijkstra. But the pgr_dijkstra finds the shortest path for two points, therefore I need to find the distance of each pair using pgr_dijkstra and adding all distances to get the total distance. The pairs will be like
2,3
3,4

4,5

5,6

6,8. Is there any way to define a function that takes this array and finds the shortest path using pgr_dijkstra.

Query is: for 1st pair(2,3) SELECT * FROM pgr_dijkstra('SELECT gid as id,source, target, rcost_len AS cost FROM finalroads',2,3, false);

for 2nd pair(3,4) SELECT * FROM pgr_dijkstra('SELECT gid as id,source, target, rcost_len AS cost FROM finalroads'***,3,4,*** false)

for 3rd pair(4,5) SELECT * FROM pgr_dijkstra('SELECT gid as id,source, target, rcost_len AS cost FROM finalroads'***,4,5,*** false);

NOTE: The array size is not fixed, it can be different.

Is there any way to automate this in postgres sql may be using a loop etc? Please let me know how to do it. Thank you.

score 1 · Accepted Answer · answered May 20 '18 at 01:06

1

Using the solution provided in this post that makes use of a source table, it is possible to use your array. Note that the orig is your array omitting the last entry, and the dest omits the 1st entry.

SELECT 
  source, 
  target,
  (SELECT SUM(cost) FROM  -- or whatever you want to do with the routing result
     (SELECT * 
      FROM pgr_dijkstra('SELECT gid as id,source, target, rcost_len AS cost FROM finalroads',
                        orig,
                        dest, 
                        false))
     ) AS foo 
  ) AS cost
FROM (
  select unnest(myarray[:array_upper(myarray,1)-1])  as orig,
         unnest(myarray[2:]) as dest 
  from (select array[1,2,3,4] myarray) b) c;

answered May 20 '18 at 01:06

JGH

15,928
4
31
48

I implemented this in python ` seq=[1,2,3,4,5] sum_=0.00 for j in range(0,len(seq)-1): c=seq[j] d=seq[j+1] cur.execute("""SELECT sum(cost) as Cos FROM pgr_dijkstra('SELECT gid as id,source, target, len_km AS cost FROM finalroads',%s, %s, false, false)""",(c,d)) mcc=cur.fetchall() sum_=sum_+mcc[0][0] Dis.append(sum_) b` but this one and the one implemented in python takes the same time. Is there any way to get the results faster? – Ayaz49 May 20 '18 at 12:30
@Ayaz49 it is expected to take about the same time, the same function being called the same number of times in both cases. You should work on making `pg_dijkstra()` faster, but that should be a different question. – JGH May 22 '18 at 13:15
1

thank you so much for ur help sir, I will look into it, – Ayaz49 May 25 '18 at 18:33

score 0 · Answer 2 · answered Jul 03 '18 at 19:20

0

If you want all pairs distance then use

select * from pgr_apspJohnson ('SELECT gid as id,source, target, rcost_len AS cost FROM finalroads)

answered Jul 03 '18 at 19:20

Grzegorz Grabek

960
7
16

how to get the distance of sequence of nodes in pgr_dijkstra pgrouting?

2 Answers2