Index

1. Index • pgRouting on PostgreSQL • Introduction • Installation • Template Creation and Use • Topological Structure • Core Features • Public Transit Implementation (Application Specific)

2. Why routing on PostgreSQL? • PostGISships with a Shape file loader an dumper • Various file types (Shape, MapInfo, DGN, GML, ...) can be read, converted and inserted to a PostGIS database using the OGC libraries • A PostGIS Feature Table can be used as data source for a growing variety of map and feature server software like UMN MapServer, GeoServer, uDGI, deegree , JUMP, etc... • The data can be accessed using standard ODBC or JDBC connectors • Several projects have evolved around PostGIS transforming and inserting geometries from highly specialized formats like SICAD C60, EDBS, DXF, WLDGE and many more

3. pgRouting :an PostgreSQL Extension • pgRouting adds routing functionality to PostgreSQL. • pgRouting is an extendible open-source library that provides a variety of tools for shortest path search as extension of PostgreSQL. • Navigation for road networks requires complex routing algorithms that support turn restrictions and even time dependent attributes. • Routing basically requires Network topology, which contains edges and nodes.

4. Core Features of pgRouting pgRouting provides functions for: • Shortest Path Dijkstra: routing algorithm without heuristics • Shortest Path A-Star: routing for large datasets (with heuristics) • Shortest Path Shooting-Star: routing with turn restrictions (with heuristics) (Extras) • Traveling Salesperson Problem (TSP) • Driving Distance calculation (Isolines)

5. pgRouting Installation • On Ubuntu : # Add pgRoutinglaunchpad repository sudo add-apt-repository ppa:georepublic/pgrouting sudo apt-get update # Install pgRouting packages sudo apt-get install gaul-devel\ postgresql-8.4-pgrouting \ postgresql-8.4-pgrouting-dd \ postgresql-8.4-pgrouting-tsp • On Windows: Download pgRouting binary package from : http://www.pgrouting.org/download.html Copy Share folder from binary package to : C:/Program Files/ … /<version>/lib/

6. Required Topological data structure • Nodes : points with a geographic position. • edges : represents connectivity of nodes, representing a polyline or multipolyline. • Relations : between groups of nodes, edges and other relations which can be assigned certain properties. • Tags can be applied to nodes, edges or relations and consist of name=value pairs.

7. pgRouting Template Creation and Use: To enable pgRouting functionality in a database, we need to run : pgRouting functions • routing_core.sql • routing_core_wrappers.sql • routing_topology.sql TSP functions • routing_tsp.sql • routing_tsp_wrappers.sql Driving Distance functions • routing_dd_wrappers.sql

8. pgRouting: Topology Calculation • First we have to add source and target column, then we run the assign_vertex_id function ...: -- Add "source" and "target" column ALTER TABLE ways ADD COLUMN "source" integer; ALTER TABLE ways ADD COLUMN "target" integer; -- Run topology function (eg.) assign_vertex_id(’<table>’, float tolerance, ’<geometry column’, ’<gid>’) SELECT assign_vertex_id(’ways’, 0.00001, ’the_geom’, ’gid’);

9. Public Transit Implementation using pgRouting • Data in-Use: • POI tables • Bus Stops • Malls and Cinema Halls • Hospitals .. etc • Road Network • Bus Routes

10. Without Change-Over from Source to Destination Route Finding

13. References: • Setting up pgRouting http://www.gpsfiledepot.com/tutorials/installing-and-setting-up-postgresql-with-postgis/ 1/2 • pgRouting Workshop http://workshop.pgrouting.org/index.html • pgRouting Documents http://www.pgrouting.org/docs/ • PostGIS in Action