PG Routing

Download current PG Routing Version

https://github.com/pgRouting/pgrouting/releases

Follow Installation Instructions

http://docs.pgrouting.org/2.5/en/pgRouting-installation.html

Mini Tutorial Example - Subway Routing

Erstellen des Netzwerks - Queries

create extension postgis; create extension pgrouting; create extension postgis_topology;

SET search_path = topology,public;

SELECT topology.CreateTopology('subway_topo', 32633); --(UTM Zone 33N für Berlin)

SELECT topology.AddTopoGeometryColumn('subway_topo', 'public', 'subway', 'topo_geom', 'LINESTRING');

CREATE INDEX subway_index ON subway USING GIST (wkb_geometry);

Erstellen des Netzwerkes bzw. Reparatur der Topology

DO $$DECLARE r record; BEGIN FOR r IN SELECT * FROM subway LOOP BEGIN UPDATE subway SET topo_geom = topology.toTopoGeom(wkb_geometry, 'subway_topo', 1, 0.01) WHERE ogc_fid = r.ogc_fid; EXCEPTION WHEN OTHERS THEN RAISE WARNING 'Loading of record % failed: %', r.ogc_fid, SQLERRM; END; END LOOP; END$$;

Einfügen von zusätzlichen Punkten in das Netzwerk

select TopoGeo_AddPoint('subway_topo', public.station_point.geom, 1) from public.station_point;

Ausschließen von nicht genutzten node_id's

create table public.subway_node_berlin as select node.geom, node.node_id, node.containing_face from subway_topo.node, subway_topo.edge_data where node_id = edge_data.start_node or node.node_id = edge_data.end_node;

Routing - Queries

Suchen des nächsten Netzwerk Knotenpunktes innerhalb eines 500m Puffers zum Startpunkt der Ubahn Fahrt

drop table if exists start_point_temp_subway_berlin; create table start_point_temp_subway_berlin AS SELECT test_start_punkt.id, subway_node_berlin.node_id FROM test_start_punkt, subway_node_berlin WHERE test_start_punkt.geom && ST_Expand(subway_node_berlin.geom, 500) ORDER BY ST_Distance(test_start_punkt.geom, subway_node_berlin.geom) ASC Limit 1;

Selbes Spiel für den Endpunkt der Ubahn Fahrt

drop table if exists end_point_temp_subway_berlin; create table end_point_temp_subway_berlin AS SELECT test_end_punkt.id, subway_node_berlin.node_id FROM test_end_punkt, subway_node_berlin WHERE test_end_punkt.geom && ST_Expand(subway_node_berlin.geom, 500) ORDER BY ST_Distance(test_end_punkt.geom, subway_node_berlin.geom) ASC Limit 1;

Einsetzen der beiden identifizierten Nodes in den Dijkstra Algorithmus und Erstellen einer Linien Geometrie, die den kürzesten Weg im Ubahn Netzwerk repräsentiert

DROP TABLE IF EXISTS public.test_subway; CREATE TABLE public.test_subway as SELECT * FROM subway_network_berlin JOIN (SELECT * FROM pgr_dijkstra (' SELECT id AS id, start_node::int4 AS source, end_node::int4 AS target, shape_leng::float8 AS cost FROM subway_network_berlin', (select start_point_temp_subway_berlin.node_id from start_point_temp_subway_berlin), (select end_point_temp_subway_berlin.node_id from end_point_temp_subway_berlin), false, false)) AS route ON subway_network_berlin.id = route.id2 order by seq;