[pgrouting-users] Speed when using big maps

[Stephen Woodbridge]

On 10/25/2013 4:49 AM, Philipp Hamm wrote:
>
>
> Thanks Steve! But I still have some questions:
>
>>> To understand this I have some questions about how pgRouting
>>> works: Does pgRouting reloads the road data on every request and
>>> does the slow calculation results only from fetching the graph?
>>> If so, is it possible to load the graph into the memory and reuse
>>> it for further requests?
>>
>> No, not currently because there is no place to load the graph into
>> memory in the database that is persistent.
>
> What do you mean? Is there not enough space on the RAM for the whole
> road network?

pGRouting runs as a postgresql database extension, which means we have 
to play within the rules and constraints of extensions to the database.

The database lives effectively forever in memory and it would not be 
nice to other users if we just grabbed a huge chunk of memory. What 
would happen if 10 or 100 users all grabbed a huge chunk. Then there is 
another issue of how long do we hold the memory?, what if the user 
forgets to free the memory? what happens if someone restarts the 
database how do we know if we have to reload the graph, etc, etc, etc

All of the current commands have a lifespan of the query. everything 
gets done in a single query and everything is released at the end of the 
query.

> And does "not currently" mean that someone is working
> on that? ;) Or are there any ideas and hints to achieve that? Because
> the most recent posting I've found about that is this one:
> http://lists.osgeo.org/pipermail/pgrouting-users/2012-December/001379.html

No, there are some ideas bouncing around, but mostly these focus on 
things like the kdijkstra one to many. It would be possible to do a many 
to many, using kdijstra like code the builds the graph once, and then 
solve multiple kdijkstra solutions on it. This would be a good project 
for a programmer that want to contribute to pgRouting with a pull 
request. Or if there is some funding we would be happy to tackle 
something like this.

>>> As I want to execute a lot of kDijkstras consecutively I guess
>>> only bounding boxes won't make it fast enough.
>>
>> If you need many routes from a single start point, then you should
>> look at pgr_kdijkstra().
>
> Yes, this is what I am going to do. But in fact I want to calculate a
> many-to-many matrix. So I have to run the one-to-many Dijkstra n
> times, which would cause a huge waste of time when rebuilding the
> graph n times

Yes, I understand as I mentioned above and opened:
https://github.com/pgRouting/pgrouting/issues/205

Regards,
   -Steve

> Regards, Philipp
>
>
>
> -----Ursprüngliche Nachricht----- Von:
> pgrouting-users-bounces at lists.osgeo.org
> [mailto:pgrouting-users-bounces at lists.osgeo.org] Im Auftrag von
> Stephen Woodbridge Gesendet: Donnerstag, 24. Oktober 2013 15:04 An:
> pgrouting-users at lists.osgeo.org Betreff: Re: [pgrouting-users] Speed
> when using big maps
>
> On 10/24/2013 6:16 AM, Philipp Hamm wrote:
>> Hi everyone,
>>
>> I've just started testing pgRouting on a big network (Germany) and
>> want to use the whole Europe map later, but it's pretty slow. I
>> takes around 2,5 min to calculate one route and it makes no
>> difference if the route distance is 7km or 700km.
>>
>> Is there anything I can do to speed it up? I have already indices
>> to 'source' and 'target' and id is primary key, but I don't know
>> how to use bounding boxes.
>
> You need to create a spatial index:
>
> create index myroads_gidx on myroads using gist (the_geom);
>
>> Furthermore I've noticed: when selecting only a part of the graph
>> (keeping in mind that the area of the dijkstra expansion is loaded)
>> it gets much faster. Is this similar to what the bounding box would
>> do?
>
> Then only load the part of the roads needed to solve the graph. The
> more roads you load the long it takes to load the data and to solve
> the problem. Also astar and trsp are both faster then Dijkstra.
>
>> To understand this I have some questions about how pgRouting
>> works: Does pgRouting reloads the road data on every request and
>> does the slow calculation results only from fetching the graph? If
>> so, is it possible to load the graph into the memory and reuse it
>> for further requests?
>
> No, not currently because there is no place to load the graph into
> memory in the database that is persistent.
>
>> As I want to execute a lot of kDijkstras consecutively I guess
>> only bounding boxes won't make it fast enough.
>
> If you need many routes from a single start point, then you should
> look at pgr_kdijkstra().
>
> If your routes are long and you want to use highways, then you can
> load data like a barbell using three bbox queries and union the
> results together. So, query 1, gets all roads for some radius around
> the start point, query2, gets all roads for some radius around the
> end point, and query 3, gets all the highways only in the bounding
> box between the start and end points.
>
> -Steve
>
>> Thanks in advance!
>>
>>
>>
>> _______________________________________________ Pgrouting-users
>> mailing list Pgrouting-users at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>>
>
> _______________________________________________ Pgrouting-users
> mailing list Pgrouting-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
> _______________________________________________ Pgrouting-users
> mailing list Pgrouting-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>