chicago_driving.sh

#!/bin/bash

createdb network -O jsaxon -D brobspace

sudo -u postgres psql network -c "CREATE EXTENSION postgis; CREATE EXTENSION pgrouting; CREATE EXTENSION postgis_topology;"

# Covering the LiveRamp area, all the way to Wisconsin.
wget 'http://overpass-api.de/api/interpreter?data=(way["highway"]["highway"!~"pedestrian|footway|steps|path"]["service"!~"parking_aisle|driveway"](41.57,-88.29,42.49,-87.30);>;);out;' -O chicago.osm

osm2pgrouting -U jsaxon -d network --f chicago.osm --password MYPASSWD

# OR --- then you also have to do pgr_createTopology();
# osm2pgsql -s -U jsaxon -d network -c hp.osm

complete_cost_matrix.sql

DROP TABLE IF EXISTS times;
CREATE TABLE times AS
SELECT origins.geoid origin, destinations.geoid destination, agg_cost FROM pgr_dijkstraCost('
    WITH w AS (
        SELECT ST_Buffer(ST_Envelope(ST_Union(centroid)), 0.025) u 
        FROM   tracts15
        WHERE  osm_nn IS NOT NULL
      )
    SELECT
      gid id, source, target, 
      CASE WHEN cost         < 0 THEN 1e8 ELSE length_m * 6.2e-4 * 60 / default_maxspeed END AS cost,
      CASE WHEN reverse_cost < 0 THEN 1e8 ELSE length_m * 6.2e-4 * 60 / default_maxspeed END AS reverse_cost
    FROM ways
    JOIN osm_way_classes ON 
      ways.class_id = osm_way_classes.class_id
    WHERE ST_Intersects(the_geom, (SELECT u FROM w))
  ', 
  (SELECT array_agg(osm_nn) A 
   FROM tracts15
   WHERE osm_nn IS NOT NULL 
   AND state = 17 AND county = 31),
  (SELECT array_agg(osm_nn) 
   FROM tracts15
   WHERE osm_nn IS NOT NULL 
   AND ST_DWithin(ST_Transform(centroid, 3528),
                  ST_Transform(ST_SetSRID(ST_MakePoint(-87.60, 41.79),4326), 3528),
                  50000)
  ),
  FALSE
) 
JOIN tracts15 origins      ON start_vid = origins.osm_nn
JOIN tracts15 destinations ON end_vid   = destinations.osm_nn
ORDER BY origin, destination
;

dijkstra.png

test_queries.sql

SELECT * FROM pgr_dijkstra('SELECT gid id, source, target, cost,
                            CASE WHEN reverse_cost < 0 THEN 1e8 ELSE ST_Length(the_geom) END AS reverse_cost
                            FROM ways', 1, 2, FALSE);

SELECT * FROM pgr_dijkstra('SELECT gid id, source, target, cost FROM ways', 1, ARRAY[2, 3], FALSE);

SELECT * FROM pgr_dijkstraCost('SELECT gid id, source, target, cost FROM ways', 1, 2);

SELECT * FROM pgr_dijkstraCostMatrix(
  'SELECT gid id, source, target, cost FROM ways', 
  (SELECT array_agg(gid) FROM ways WHERE gid < 50)
);

-- For testing.
SELECT                              
  a.seq, the_geom path, 
  a.cost, a.agg_cost, a.node, a.edge, b.name
FROM pgr_dijkstra(
  'SELECT gid id, source, target, ST_Length(the_geom) AS cost,
 CASE WHEN reverse_cost < 0 THEN 1e8 ELSE ST_Length(the_geom) END AS reverse_cost FROM ways',
  42608, 32016, True
) a JOIN ways b ON 
  a.edge = b.gid;