[FOSS4G 2017] pgRouting

Transcript

1. Routing in the Database with pgRouting Daniel Kastl - FOSS4G Europe 2017
2. 2 What is pgRouting?
3. 3 Extension
4. Free Software 4
5. Library “low-level” interface to algorithms 5
6. Shortest Path 6
7. Network Analysis 7
8. Optimization 8
9. Feature Highlights • Turn Restriction Shortest Path (TRSP) • One-to-many, Many-to-one, Many-to-many Shortest Path • Routing Topology functions • Graph Analytics functions • osm2pgrouting for OSM data • Prebuilt packages for many operating systems • Active community • Commercial support • Much more ... 9
10. v2.4
11. 11 Why pgRouting?
14. Routing for Road Networks 14
18. Sometimes costs have different meaning 18
19. A B
20. 20 Road Types … and conditions
22. 22 Database CREATE DATABASE routing_db; \c routing_db CREATE EXTENSION postgis; CREATE EXTENSION pgrouting;
23. 23 pgRouting is SQL SELECT * FROM pgr_dijkstra(' SELECT gid AS id, source, target, length AS cost FROM ways', 13224, 6549, directed := false);
24. 24 Query Result seq | path_seq | node | edge | cost | agg_cost -----+----------+-------+-------+----------------------+--------------------- 1 | 1 | 13224 | 815 | 0.00177810584893137 | 0 2 | 2 | 3495 | 820 | 0.00179225967147818 | 0.00177810584893137 3 | 3 | 905 | 19177 | 0.000102092605022171 | 0.00357036552040955 4 | 4 | 6893 | 19178 | 8.66225143958747e-05 | 0.00367245812543172 5 | 5 | 7753 | 19191 | 0.00047705096163826 | 0.0037590806398276 6 | 6 | 8159 | 7015 | 0.000513853140499986 | 0.00423613160146586 7 | 7 | 2477 | 7016 | 9.12382595170489e-05 | 0.00474998474196585 8 | 8 | 11902 | 7017 | 9.71560600269149e-05 | 0.00484122300148289 9 | 9 | 2042 | 7018 | 0.000120258430056735 | 0.00493837906150981 10 | 10 | 6549 | -1 | 0 | 0.00608474802167719 (11 rows)
25. source target
27. 1 min B C D A
29. A
30. 30 Dynamic Costs
33. 33 Restrictions
36. Cost can be virtually anything
37. 37 pgRouting is SQL SELECT * FROM pgr_dijkstra(' SELECT gid AS id, source, target, length + incline AS cost FROM ways WHERE barrier-free', 13224, 6549, directed := false);
38. 38 pgRouting is SQL SELECT * FROM pgr_dijkstra(' SELECT gid AS id, source, target, air-quality AS cost FROM ways WHERE no-cars', 13224, 6549, directed := false);
39. No need to rebuild the network! 39
40. Flexibility vs Speed 40
41. Any Networks 41
45. 45 Custom Functions
46. 46 DriveTime Analysis INSERT INTO data.distances SELECT 123 AS dest_id, a.*, b.the_geom FROM pgr_drivingDistance( 'SELECT * FROM osm.ways', 123, 9999999 ) AS a LEFT JOIN osm.ways_vertices_pgr AS b ON a.node = b.id;
48. 48 Points as Polygons SELECT pgr_pointsAsPolygon( 'SELECT id, ST_X(geom) AS x, ST_Y(geom) AS y FROM distances WHERE distance <= ' || dist || ';', 0.00001 ) AS geom;
50. 50 Combined Destinations SELECT DISTINCT ON (node) * FROM data.distances ORDER BY node, agg_cost ASC;
52. SELECT * FROM ctg_drivingdistance( ST_SetSRID(ST_MakePoint(-117.107,32.711),4326), 'ways', '2500,5000,7500', 'm');
53. http://workshop.pgrouting.org 53
54. New Development 54
55. Maximum Flow SELECT * FROM pgr_maxFlowEdmondsKarp( 'SELECT id, source, target, capacity FROM flow_network', source_vertex := 1, sink_vertex := 6 ); By Andrea Nardelli (GSoC 2016) 55
56. Network Contraction • Flexible Framework “Easy” to add a new operation. • Speeds up large graphs • Current operations: ◦ Dead end contraction [1] ◦ Linear contraction [2] By Rohith Reddy (GSoC 2016) 56
57. Dead end Contraction 57 Linear Contraction
58. Network Contraction SELECT * FROM pgr_contractGraph( 'SELECT id, source, target, cost, reverse_cost FROM network_table', ARRAY[1,2] ); By Rohith Reddy (GSoC 2016) 58
59. Google Summer of Code 2017 1. Vidhan Jain: Rewrite of TRSP Implementation of “pgr_lineGraph” Implementation of “pgr_dijkstraTRSP” 2. Maoguang Wang: Connected Components “Set of vertices, that are reachable from each other” Useful for graph analysis and graph contraction 59
60. 60 Tour Planning Vehicle Route Planning / Scheduling / Optimization
62. 62 Practical Examples 1. On-demand bus shuttle -> VRP with time windows and capacity constraints 2. Cleaning services -> VRP with Time Windows (with “break times”) 3. Garbage Collection -> Highly customized workflow
63. pgRouting Functions Traveling Sales Person • pgr_TSP - When input is given as matrix cell information • pgr_eucledianTSP - When input are coordinates. Vehicle Routing Problems • pgr_gsoc_vrppdtw - VRP Pickup & Delivery (Euclidean) • pgr_vrpOneDepot - VRP One Depot • More coming soon... 63
64. https://github.com/pgRouting
65. Interested in pgRouting? Project: http://pgrouting.org Email: daniel@georepublic.de Twitter: @pgrouting Thank you! 65