6

I have table network which has about 2 million records (only 3 countries in Europe). When I run Shorthest-path it is very slow, it takes 3 min to take records only. I tried with dijkstra-sp-delta-directed but it is also slow, about 2-2.5 min. This sp is using bounding box and gist index so it should be faster but it is not. In the future I should add some other country so table will be very large.

What could I do to make this routing faster? As far as I know Postgre doesn't have option to save temp table in RAM, it works only in one session.

I have 4 GB RAM, I work with OSM and Postgre because it is free, I realy don't know is there any solution for this problem? I am not in position to buy some expensive solution, I have demand to work with open sources. Of course everyone expects quick and cheap solution that works like Google :)

Update:

For the fastest route (avoid toll , avoid highway) my query is: 

SELECT * FROM shortest_path(
   'SELECT gid AS id, 
           source::int4 AS source, 
           target::int4 AS target, 
           time + cost::float8 AS cost, 
           time + reverse_cost::float8 AS reverse_cost 
    from network where toll is null and road_class!=1 ', 
    257027, 276521, true, true)

For the shothest route (avoid toll , avoid highway) my query is: 

SELECT * FROM shortest_path(
   'SELECT gid AS id, 
           source::int4 AS source,
           target::int4 AS target, 
           length + cost::float8 AS cost, 
           length + reverse_cost::float8 AS reverse_cost 
    from network where toll is null and road_class!=1 ', 
    257027, 276521, true, true)
Improve this question
1

3 Answers 3

Reset to default
7

What is your bounding box buffer value? It needs to be in the same unit as your network data. If your unit is degree then it should be a small decimal number.

Also did you add indices to source, target and gid and run VACUUM FULL?

Improve this answer
9
  • 1
    My data SRID is 4326. Gid is PK. Source and target did not have index, so I create indices and it is much faster now, thank you for this. When creating route I have 2 types: fastest and shothest, also I have 2 checkboxes: avoid tolls, and avoid highways. So filtering network table depends on user demand. In network table I have 4 fields: time, length, cost and reversse_cost (last 2 depends on oneway)
    – Route Maker
    Commented Feb 10, 2012 at 9:30
  • Should I also create inidices for time, length, cost,reverse_cost ?
    – Route Maker
    Commented Feb 10, 2012 at 9:32
  • 1
    @Route Maker - I am very interested in your results as i work with the same tools. You say your solution became much faster after placing indeces on source and target. How much faster did it become? You also seem to be filtering out highways, for what type of usage are you planning the routes?
    – mrg
    Commented Feb 10, 2012 at 10:44
  • Let say from 180 sec to 4 sec ... much faster!!!
    – Route Maker
    Commented Feb 10, 2012 at 10:56
  • My client has system for vehicle tracking, so he needs routing for cost optimisation. He wants me to make routing as Google has. Google is extemely fast, so my job is to be the same as Google :) Google has avoid tolls, avoid higways, fastest, shorthest route.
    – Route Maker
    Commented Feb 10, 2012 at 11:02
1

3 min. could be realistic for Postgres/pgRouting, depends on size of country road network you have tested. You could try osm2po. I have tested it on OSM dataset and it is really fast, even on large road networks. You can try it and see if it fits you needs.

Improve this answer
1
  • Mario, demand is to make our own routing engine :) But, thanks to dkastl when I put indices on source and target it becomes much faster. Right now I have 3 countries, but in the future I will have whole Europe.
    – Route Maker
    Commented Feb 10, 2012 at 12:34
0

if any reader of this thread wants to create distance matrixes, I modified shortest-path() to use boost::dijkstra with many destinations.

See github.com/pgRouting/pgrouting/wiki/One_to_many-Dijkstra---To-review and github.com/pgRouting/pgrouting/issues/66 for the missing installation instructions. Please feedback...

Improve this answer

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.