I'm a beginner when it comes to postgis and pgrouting development.

For a project I'm working on and a bit of the algorithm:

• I have a polygon layer that represents zones
• I have a line layer that represents roads based on classifications: Freeway, Arterial, and Collector, and Local
• I created a point layer of all the zone centroids
• I would like to create a table of n x "dummy link(s)". These links connect the centroid to the road layer based on the road classfication (above), the link is within the polygon, and based on nearest neighbour (nearest proper road classification segment to centroid).
• A dummy link has infinite capacity and has a cost of zero.
• I would like to start the routing from the centroid layer.
• At each of the centroids, I would like to do a minimum spanning tree or Dijkstra to all other centroids of the network.
• The end goal would be a matrix of some sort.
• I'd like this operation to be somewhat fast, as I would have about 200 zones (400 once you consider two way traffic), with a road network of roughly 1,000,000 links. Road weights/costs will change as they are time of day dependent; therefore, there will be at least 24 iterations per zone.
• If possible, I'd like to limit most of my development to python as I'm comfortable with python and libgdal.

Question:

1. Is this feasible using postgis + pgrouting, especially considering that I am not proficient at SQL?
2. Can you critique my model, suggest improvements, "gotchas", anything that would make my life a little bit easier?

ad 1. It's always good to learn SQL. There might be some learning curve, but why not.

ad 2. Have you considered using the network node closest to the polygon centroid instead of your more complex approach of creating dummy links?

• I agree about 1, I just bought myself "SQL in easy steps" from the book store, should be a good 1 weekend start to get my toes wet. Commented Jul 15, 2011 at 12:53
• About Number 2: Ideally, all links should be conencted to the vertices from the network node; however, in some occasions, you might have a link that spans an entire polygon, so the dummy link will end up being outside of the polygon Commented Jul 15, 2011 at 12:54
• I'm going to edit this question and put a bounty on it. How do you suggest I reword it? Commented Jul 21, 2011 at 12:33

For Question 2: You may wish to consider using a very high fixed cost for the dummy links instead.

By putting zero cost on the dummy links -- depending on your routing/minimization algorithm -- you may have flows from zone A to zone B that pass through other zones on the way.

For actual minimal cost from zone A to zone B, you just have to subtract 2x the high fixed cost due to the dummy links at each end.