2

I have a problem with the Dijkstra algorithm in regards to the query speed. My current php script for the query is looking like this:

$sql = "SELECT * FROM pgr_dijkstra(
            'SELECT id, source, target, ST_Length(geom_way::GEOGRAPHY) AS cost, ST_LENGTH(geom_way::GEOGRAPHY) AS reverse_cost
            FROM ".TABLE."',
            ".$startEdge['source'].",
            ".$endEdge['target'].",
            directed := true);";

$query = pg_query($con, $sql);

This takes like 10 seconds for just one query. The OSM data on the database is this one: http://download.geofabrik.de/europe/germany/baden-wuerttemberg.html The table has ~700000 records.

At this speed, it is not really usable for me, because I have to make x queries at a time and can not wait for x*10 seconds for getting the results.

The forum has some advice on how the speed can be increased, like these ones:

Speeding Up pgr_dijkstra using bounding box in PostGis2.0

Why is any pgr_* routing function taking forever based on OSM data in an pgrouting enabled DB

But I am new to the whole spatial/geographical stuff and have problems adopting the advices given. I do not want to copy blind and also can not do it because most questions are asked on older versions of pgRouter and not on pgRouter 3.0.

I tried to edit my query in regards to the first gis link and it is now looking like this:

$sql = "SELECT * FROM pgr_dijkstra(
            'SELECT id, source, target, ST_Length(geom_way::GEOGRAPHY) AS cost, ST_LENGTH(geom_way::GEOGRAPHY) AS reverse_cost
            FROM ".TABLE."
            WHERE geom_way && ST_Expand((SELECT ST_Collect(geom_way) FROM ".TABLE." WHERE id IN (".$startEdge['source'].", ".$endEdge['target'].")), 0.01)',
            ".$startEdge['source'].",
            ".$endEdge['target'].",
            directed := true);";

In the post, it uses on the FROM in the WHERE statement this: ways_vertices_pgr What is this? Should this be generated somewhere from the ways table? Because with my query it does not give me anything back.

I also have tried this one, but it did not change anything: https://gis.stackexchange.com/a/194328/167149

Hopefully, someone can help me with this. If you need more information do not hesitate to ask!

Kind regards Lars

Lars
  • 31
  • 4
  • How exactly did you create the network topology? If there is no vertice table, check pgr_createVerticesTable to do just that. Make sure its id column is indexed, then reference that table in the inner SELECT (also using its column names). The idea here is to get an expanded bbox around the source and target vertices to limit the edge count in the routing graph. – geozelot Jul 25 '20 at 19:17
  • Note that it may be worth to create the bbox (the result of your inner SELECT) in a separate step and only pass the actual box geometry into the edge_sql! – geozelot Jul 25 '20 at 19:24
  • @geozelot I created the whole table out of osm2po and then imported it in my database. I created the vertice table now with pgr_createVerticesTable command. My query now just takes 0.01 seconds! Thank you so much for your help. You are my life saver:D In which regard would it be good to create the bbox in a seperate step? Does this give also more performance? Can you maybe add an answer that I could mark this post as answered? – Lars Jul 26 '20 at 16:20

1 Answers1

0

I always follow this performance tip and most of the time it solves my slow https://postgis.net/docs/performance_tips.html

  • Create a bounding box around your source and target vertices to limit the number of roads you are routing - way_vertices_pgr is your osm road topology
  • Create index on the geom column and run vacuum analyze
  • Tune your database by increase the work_mem if your system allow
Ngan Le
  • 1