TransWikia.com

Creating a Bounding box on pgr_Dijkstra to make it faster

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

One Answer

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

Answered by Ngan Le on September 2, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP