Geographic Information Systems Asked on September 2, 2021
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
I always follow this performance tip and most of the time it solves my slow https://postgis.net/docs/performance_tips.html
Answered by Ngan Le on September 2, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP