pgRouting - calculating route from point to point

Question

I have three shapefiles:

• roads(lines),
• logistic center(one point feature) and
• places(a lot of point features).

I have tried to calculate route from logistic center to places by ArcMap network analyst, but it was pain and I do not successfully made it.

So I installed PostGIS/Postgresql with pgRouting extension. Now I have those shapefiles imported to dabatabase with SPIT tool from QGIS. In database they are shown as 3 tables. Also I successfully created topology for roads by tutorial found in pgRouting workshop documentation.

For now, I can't find tutorial for my situation, as I want to calculate shortest routes from logistic center to places using roads layer. I can do it by hand, one by one, on QGIS or ArcMap, using their tools, but I have 1500 places, so it would be pain to do it.

My questions:

Is it possible to calculate routes from one point to many with pgRouting (I need only distances for shortest route) using logistic center as source and places as targets?

Is it possible to create a table with information containing target ID and route distance and create a shapefile with geometry?

How to do that?

Answer 1

The pgr_kDijkstra function allows you to calculate one-to-many distances.

With Dijkstra algorithm you need to start and end your route with a node ID, so it might be a good idea to integrate your logistic centers and points into the road network.

You can do this with some PostGIS functions:

• ST_LineLocatePoint gives you closest point on LineString to the given Point, as a fraction of total 2d line length.
• ST_LineSubstring returns you a partial linestring using the fraction you calculated before.
• ST_LineInterpolatePoint returns you the interpolated point.

Then give your interpolated point a node ID and replace the linestring with the 2 substrings.

You could also write a function to do this with every request, but if your places and logistic centers don't change, you could do this once for all points and store the extended network in the database.

Answer 2

I think you are looking for the pgr_drivingDistance function

This function computes a Dijkstra shortest path solution them extracts the cost to get to each node in the network from the starting node. Using these nodes and costs it is possible to compute constant drive time polygons. Returns a set of pgr_costResult (seq, id1, id2, cost) rows, that make up a list of accessible points.

http://docs.pgrouting.org/2.0/en/src/driving_distance/doc/dd_driving_distance.html#pgr-driving-distance

Example:

SELECT seq, id1 AS node, cost
    FROM pgr_drivingDistance(
            'SELECT id, source, target, cost FROM edge_table',
            7, 1.5, false, false
    );

seq | node | cost -----+------+------ 0 | 2 | 1 1 | 6 | 1 2 | 7 | 0 3 | 8 | 1 4 | 10 | 1 (5 rows)