Routing obviously is not limited to pedestrians. This chapter will cover routing for vehicles and how to manipulate the query costs:
A query for vehicle routing generally differs from routing for pedestrians: the road segments are considered directed, and the reverse_cost attribute must be taken into account. This is due to the fact that there are roads that are “one way”. Depending on the geometry the valid way is
cost >= 0 and reverse_cost < 0)cost < 0 and reverse_cost >= 0)So a “wrong way” is indicated with a negative value and is not inserted in the graph for processing.
For two way roads cost >= 0 and reverse_cost >= 0 and their values can
be different. For example, it is faster going down hill on a sloped road.
In general cost and reverse_cost do not need to be length; they can be
almost anything, for example time, slope, surface, road type, etc., or they can
be a combination of multiple parameters.
The following queries indicate the number of road segments, where a “one way” rule applies:
Number of (source, target) segments with cost < 0
Run: SELECT count(*) FROM ways WHERE cost < 0;
count
-------
10
(1 row)
Number of (target, source) segments with reverse_cost < 0
Run: SELECT count(*) FROM ways WHERE reverse_cost < 0;
count
-------
2238
(1 row)
Exercise 7 - Vehicle routing
13224 to vertex 9224.cost_s and reverse_cost_s columns, which are in unit seconds.SELECT * FROM pgr_dijkstra('
SELECT gid AS id,
source,
target,
cost_s AS cost,
reverse_cost_s AS reverse_cost
FROM ways',
13224, 9224, directed := true);
Exercise 8 - Vehicle routing where “time is money”
13224 to vertex 9224.€100 per 3600 seconds.cost_s and reverse_cost_s columns, which are in unit seconds.cost / 3600cost / 3600 * 100SELECT * FROM pgr_dijkstra('
SELECT gid AS id,
source,
target,
cost_s / 3600 * 100 AS cost,
reverse_cost_s / 3600 * 100 AS reverse_cost
FROM ways',
13224, 9224);
Note
Comparing with Exercise 7:
In “real” networks there are different limitations or preferences for different road types for example. In other words, we don’t want to get the shortest but the cheapest path - a path with a minimal cost. There is no limitation in what we take as costs.
When we convert data from OSM format using the osm2pgrouting tool, we get two
additional tables: osm_way_types and osm_way_classes:
Run SELECT * FROM osm_way_types ORDER BY type_id;
type_id | name
---------+-----------
1 | highway
2 | cycleway
3 | tracktype
4 | junction
(4 rows)
Run SELECT * FROM osm_way_classes ORDER BY class_id;
class_id | type_id | name | priority | default_maxspeed
----------+---------+-------------------+----------+------------------
100 | 1 | road | 1 | 50
101 | 1 | motorway | 1 | 50
102 | 1 | motorway_link | 1 | 50
103 | 1 | motorway_junction | 1 | 50
104 | 1 | trunk | 1 | 50
105 | 1 | trunk_link | 1 | 50
106 | 1 | primary | 1 | 50
107 | 1 | primary_link | 1 | 50
108 | 1 | secondary | 1 | 50
109 | 1 | tertiary | 1 | 50
110 | 1 | residential | 1 | 50
111 | 1 | living_street | 1 | 50
112 | 1 | service | 1 | 50
113 | 1 | track | 1 | 50
114 | 1 | pedestrian | 1 | 50
115 | 1 | services | 1 | 50
116 | 1 | bus_guideway | 1 | 50
117 | 1 | path | 1 | 50
118 | 1 | cycleway | 1 | 50
119 | 1 | footway | 1 | 50
120 | 1 | bridleway | 1 | 50
121 | 1 | byway | 1 | 50
122 | 1 | steps | 1 | 50
123 | 1 | unclassified | 1 | 50
124 | 1 | secondary_link | 1 | 50
125 | 1 | tertiary_link | 1 | 50
201 | 2 | lane | 1 | 50
202 | 2 | track | 1 | 50
203 | 2 | opposite_lane | 1 | 50
204 | 2 | opposite | 1 | 50
301 | 3 | grade1 | 1 | 50
302 | 3 | grade2 | 1 | 50
303 | 3 | grade3 | 1 | 50
304 | 3 | grade4 | 1 | 50
305 | 3 | grade5 | 1 | 50
401 | 4 | roundabout | 1 | 50
(36 rows)
Manipulating cost values
osm_way_classes table is linked with the ways table by the
class_id field.UPDATE query.Let’s change the cost values for the osm_way_classes table, that the use
of “faster” roads is encouraged when the cost of each road segment is multiplied
with a certain factor:
ALTER TABLE osm_way_classes ADD COLUMN penalty FLOAT;
UPDATE osm_way_classes SET penalty=1;
UPDATE osm_way_classes SET penalty=2.0 WHERE name IN ('pedestrian','steps','footway');
UPDATE osm_way_classes SET penalty=1.5 WHERE name IN ('cicleway','living_street','path');
UPDATE osm_way_classes SET penalty=0.8 WHERE name IN ('secondary','tertiary');
UPDATE osm_way_classes SET penalty=0.6 WHERE name IN ('primary','primary_link');
UPDATE osm_way_classes SET penalty=0.4 WHERE name IN ('trunk','trunk_link');
UPDATE osm_way_classes SET penalty=0.3 WHERE name IN ('motorway','motorway_junction','motorway_link');
Exercise 9 - Vehicle routing preferring “fast” roads
13224 to vertex 9224.cost_s and reverse_cost_s columns, which are in unit seconds.penaltySELECT * FROM pgr_dijkstra('
SELECT gid AS id,
source,
target,
cost_s * penalty AS cost,
reverse_cost_s * penalty AS reverse_cost
FROM ways JOIN osm_way_classes
USING (class_id)',
13224, 9224);
Note
Comparing with Exercise 7:
Exercise 10 - Vehicle routing with access restrictions
13224 to vertex 9224.residential roads is the original cost in seconds multiplied with a €0.50 penalty.primary road cost is the original cost in seconds multiplied with a €100 fine.Through CASE statements and sub queries costs can be mixed as you like, and
this will change the results of your routing request instantly. Cost changes
will affect the next shortest path search, and there is no need to rebuild your
network.
SELECT * FROM pgr_dijkstra($$
SELECT gid AS id,
source,
target,
CASE
WHEN c.name = 'residential' THEN cost_s * 0.5
WHEN c.name LIKE 'primary%' THEN cost_s * 100
ELSE cost_s * 0.1
END AS cost,
CASE
WHEN c.name = 'residential' THEN reverse_cost_s * 0.5
WHEN c.name LIKE 'primary%' THEN reverse_cost_s * 100
ELSE reverse_cost_s * 0.1
END AS reverse_cost
FROM ways JOIN osm_way_classes AS c
USING (class_id)$$,
13224, 9224);
Note
Comparing with Exercise 7 and with Exercise 9: