2

I would like to compute shortest path between 2,5 millions pairs.

I created table with id, origin, the nearest node of origin, destination, the nearest node of destination. I also created index on source, target, id. I have table with roads network of one city - also with indexes. All geoms have gist index.

When I tried to use dijkstra, the execute of query is too slowly. It would take over a week. I have idea, I will add where condition (f.e. id <10000) and I will make insert to new table. Therefore I can do a few queries in the same time. I noticed that using a larger index (few columns) speeds up the query.

Below my code:

SELECT a."id" as id
, SUM(a.cost) AS "Aggregated Cost" 
FROM ( SELECT "id"
, (pgr_dijkstra( 'select id, source, target, shape_leng as cost 
from roads', "mz_node", "tz_node", false )).* 
FROM pair_with_near where id < 1000) AS a
JOIN roads AS b ON a.edge = b.id GROUP BY a."id"

I don't have more ideas, how can I make it faster. I read about buffer creating but I can't do it.

The same with Contraction - Family of functions.

Do you have any idea?

Improve this question
3
  • 2
    Your query will become faster if you select fewer data. For example you can limit by bounding box and cut away all areas, that you will not need. Any road network edges, that you know you won't need, exclude them in your query.
    – dkastl
    Commented Oct 8, 2019 at 1:40
  • 1
    Yes, I know but I don't know, where can I add bounding box in my query. Could you help me?
    – Michal De
    Commented Oct 8, 2019 at 16:42
  • When calculating shortest path you always have two points X, Y. Bounding box would be surrounded by square [latX, lonX, latX, lonY]. See e.g. gis.stackexchange.com/questions/83387/… and gis.stackexchange.com/questions/93936/….
    – lubosdz
    Commented Jul 10, 2022 at 21:14

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.