2

I am updating thousands of rows in a survey table finding shortest path from each place a survey respondent visits. My code below is the query I use to update the blank route column. The function "pgr_fromAtoPO" is the same as the "pgr_fromAtoB" listed on the pgr tutorial, except the name of the tables have been changed. I initially tried to run the querry to update my entire table, with 90,000 rows of start and end points. This ran for 2 days and crashed my 8GB RAM 2.0GHZ quad core macbook pro, so I have since changed my querry to limit the rows to 5,000. This querry took below took 112 minutes on 5000 routes.

update analysis.po_routing
set route = (select st_multi(st_union(geom)) from pgr_fromAtoPO('osm2po_routing',x1,y1,x2,y2)) 
where uniqueid <    605300515 
and uniqueid >=     604479116 
and route is null and x1 is not null; 

My question is: Are these functions optimized for single calls (i.e. what a website might get), so would I be better off looping through each individual row separately rather than a call to update 5,000 rows, and is there some sort of memory limit, such that I would be better off updating a smaller chunk of rows at a time, say 500 or 1000 to improve the speed? Thanks for your help.

3

Yes, there are memory limits and processing in chunks is faster. It's hard to say how big chunks you should use.

Apart from processing in smaller parts you should also consider following:

  • Make sure you have proper indices on your tables
  • Increase shared_buffers and work_mem
  • Change the function. If you're using this function there's much room for improvment, for example:
    • Implement bboxing
    • Don't calculate heading, you're not using it
    • Precalculate the nearest nodes instead of searching for them every time a function is called. This way you will find the respondent's/place's nearest node only once instead of every time.
  • That makes a lot of sense. I initially calculated source and target nodes but was having problems with pgr_dijkstra finding a route for about half of my routes.Also makes a lot of sense not to calculate heading and to implement bboxing. I was hoping to implement bboxing with the Astar algorithm as it implements that I think, but that function doesn't return the geometry. I'll have to look into that, seems like you have to be careful to make the bbox bigger than the 2 points somehow to not exclude routes that might be in the opposite direction from the node to get on a faster edge. – EconGeo Jun 24 '14 at 23:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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