How to develop an application with pgRouting in Windows (4): catchment areas and isochrones

pgRouting includes a
function
called
pgr_drivingDistance
, that, together with three others functions , pgr_withPointsDD
, pgr_alphaShape and pgr_pointsAsPolygon , can be used to establish which
paths
have
a certain cost versus a starting or arrival
point .

The area surrounding these paths
is
designated
as
a
catchment area . pgr_drivingDistance , in
conjunction
with pgr_pointsAsPolygon is often
used
to determine coverage requirements for a service or to determine optimal
locations for new facilities.

For example , if you have
a
fire
station, you must determine
all
parts of the city that can be reached
within five minutes. A reverse
problem would be to find all parts of the city
where
the residents could drive
to
a hospital within five minutes.

The
function that calculates the paths for both problems
is
called
pgr_drivingDistance
because it is often used
when
the cost is measured as driving
time . However, the costs are not necessarily
  limited to a time or a
distance.
For
this type of application , knowing all routes towards
or
from a given point is not very useful.  It
is here where the functions   pgr _alphaShape and
pgr_pointsAsPolygon come into play. The
pgr_alphaShape use as input a set of x, y
coordinates and returns a set of x, y which correspond to the vertices of a
polygon that includes all the input points.
pgr_pointsAsPolygon
is
similar
to pgr_alphaShape , it uses as input a set of coordinates x, y, but instead of
returning another
series
of points, returns a polygon   that  surrounds all input
points . Internally, it uses the pgr_alphaShape function .

Using pgr_drivingDistance

Let’s take the case of a
fire
station and search the area covered in 5 minutes. The
order sql corresponding to these
criteria
can be translated by the following:

SELECT * FROM pgr_drivingDistance
(‘SELECT gid as id, source, target, cost_s
as cost, reverse_cost_s as reverse_cost
FROM public.ways ‘, 25736, 300, true)

In the SELECT clause we use
the cost of a section in
seconds
(cost_s), and indicate the reverse_cost_s because even fire trucks cannot drive
against traffic. 25736 is
the node corresponding to the fire station, 300
is
the forecasted cost (5 minutes x 60 seconds ), and the true
value indicates that the graph is
directional
(takes into account the forbidden driving
sense ).

The result of the query returns
6948 nodes that are in within
5 minutes from the fire station. By cons the result of the query
having
no geometry , we can not visualize them in QGis.

You have to modify the query as
follows:

SELECT * FROM pgr_drivingDistance
(‘SELECT gid as id, source, target, cost_s
as cost, reverse_cost_s as reverse_cost
FROM public.ways ‘, 25736, 300, true) as JOIN
ways_vertices_pgr on di.node = ways_vertices_pgr.id

We perform a join with the node table (
ways_vertices_pgr ) and the result of the query includes
the geometries :   

We can see the
result in QGis:

How to create
the
catchment
area
with
pgr_pointsAsPolygon

To create the catchment area, we use
the former query as sql
of the function pgr_pointsAsPolygon , by
creating
a polygon in output that we can load in QGis:
SELECT 1 As id,ST_SetSRID(pgr_pointsAsPolygon(
$$ SELECTdi.seqAS id, ST_X
(
v.the_geom) AS x, ST_Y (v.the_geom) As y
FROMpgr_drivingDistance(SELECTgidAs id, source, target,
cost_sAS cost,reverse_cost_sASreverse_cost
FROMpublic.ways», 25736, 300, true
) AS di INNER JOINpublic.ways_vertices_pgrAS v ONdi.node= v.id $$
), 4326) Asgeom;

pgr_pointAsPolygon uses the id, x, and y points
generated by pgr_drivingDistance . For x
and y we use the functions St_x and St_y on the points geometry.

Once loaded in QGis, the
result is as follows :   

How to create the
isochrones

The isochrones correspond to the reachable areas
in
a
series
of time steps. We could , in this
example
, want to know which are
the areas we can reach
in
1, 2, 3, 4 and 5 minutes.

Therefore we will proceed in
two
steps
.

The first one, is to create a
table with our nodes resulting
from pgr_drivingDistance for the fastest time step (5 minutes). Logically
, in this table it will, also, be found the nodes
reachable
in
the shortest periods of time.

We create this table ( dd_caserne )
with the following sql command  :

CREATE TABLE dd_caserne AS

SELECT di.seq As id,
di.node , di.edge ,

di.agg_cost As time_access
, v.the_geom As geom

FROM pgr_drivingDistance (SELECT
gid As id, source, target,
cost_s AS cost, reverse_cost_s
AS reverse_cost
FROM public.ways’, 25736, 300, true

) AS di INNER JOIN public.ways_vertices_pgr
AS v ON di.node = v.id;

The resulting table is the following :

Based on this table we will build
a
series
of polygons with pgr_pointsAsPolygon for points at 60, 120 180 240 and 300
seconds of access time by using generate_series
SELECTiAstime_access,
ST_SetSRID
(pgr_pointsAsPolygon(
‘SELECT id, ST_X (geom) AS
x, ST_Y (
geom) As y
FROMdd_caserne
WHEREaccess_time<= ‘||i :: text), 4326) Asgeom
FROMgenerate_series(60,300,60)
As
i
ORDER BYiDESC

The
result includes
the 5 isochrones polygons , which we can, now,
display
in
QGis:

Leave a Reply

Your email address will not be published. Required fields are marked *