Smathermather's Weblog

Remote Sensing, GIS, Ecology, and Oddball Techniques

Archive for the ‘pgRouting’ Category

Drivetime analyses, pgRouting

Posted by smathermather on August 6, 2014

Map of overlapping 5-minute drive times from park access points
We’ve got some quick and dirty pgRouting-based code up on github. I say quick and dirty because it directly references the table names in both of the functions. I hope to fix this in the future.

The objective with this code is to input a point, use a nearest neighbor search to find the nearest intersection, and from that calculate a drive time alpha shape.

First, the nearest neighbor search:

CREATE OR REPLACE FUNCTION pgr.nn_search (geom geometry) RETURNS
int8 AS $$

SELECT id FROM pgr.roads_500_vertices_pgr AS r
ORDER BY geom <#> r.the_geom


This function takes one argument– pass it a point geometry, and it will do a knn search for the nearest point. Since we are leveraging pgRouting, it simply returns the id of the nearest intersection point. We wrote this as a function in order to run it, e.g. on all the points in a table. As I stated earlier, it directly references a table name, which is a little hackerish, but we’ll patch that faux pax later.

Now that we have the ID of the point in question, we can do a driving distance calculation, wrap that up in an alpha shape, and return our polygon. Again, we write this as a function:

CREATE OR REPLACE FUNCTION pgr.alpha_shape (id integer, minutes integer) RETURNS
geometry AS $$

WITH alphashape AS(SELECT pgr_alphaShape('WITH
SELECT seq, id1 AS node, cost
FROM pgr_drivingDistance(''SELECT id, source, target, cost FROM pgr.roads_500'',' || id || ', ' || minutes || ', false, false)),
dd_points AS (
SELECT id_ AS id, x, y
FROM pgr.roads_500_vertices_pgr v, DD d
WHERE = d.node)
SELECT * FROM dd_points')),

alphapoints AS (
SELECT ST_Makepoint((pgr_alphashape).x, (pgr_alphashape).y) FROM alphashape),

alphaline AS (
SELECT ST_MakeLine(ST_MakePoint) FROM alphapoints)

SELECT ST_MakePolygon(ST_AddPoint(ST_MakeLine, ST_StartPoint(ST_MakeLine))) AS the_geom FROM alphaline


Finally, we’ll use these functions in conjunction with a set of park feature access points to map our our 5-minute drive time. Overlaps in 5-minute zones we’ve rendered as a brighter green in the image above.

CREATE TABLE pgr.alpha_test AS
WITH dest_ids AS (
SELECT pgr.nn_search(the_geom) AS id FROM pgr.dest_pts
SELECT pgr.alpha_shape(id::int, 5)::geometry, id FROM dest_ids;

Oh, and I should point out that for the driving distance calculation, we have pre-calculated the costs of the roads based on the speed limit, so cost is really travel time in minutes.

Posted in Analysis, Database, pgRouting, PostGIS, PostgreSQL, SQL | Tagged: , , | 4 Comments »

Realistic biking distances, pgRouting

Posted by smathermather on December 2, 2013

In an upcoming recipe (another one for the cutting room floor), we’ll play a bit with realistic biking distances, as compared with a 5-mile buffer (light grey), a 5-mile driving distance (dark grey), and a 5-mile biking-on-roads-with-speed-limits-under-40mph (purplish).

A diagram of realistic biking distance as compared with buffers and driving distance.

Realistic biking distance as compared with buffers and driving distance.

This is just a teaser for mapbaker… .

Posted in Analysis, Database, pgRouting, PostGIS, PostgreSQL, SQL | Tagged: , , | 1 Comment »

PostGIS Cookbook Promises

Posted by smathermather on November 30, 2013

So, as we’re are in the final edits of the PostGIS Cookbook, there are some recipes that have ended up on the cutting room floor.  Several of these will end up on this blog:

Amongst those that will end up in the alternate pipeline, and thus ultimately in GitHub, will be a project called pgOrthogonalize, similar to the functionality in Potlatch2 and iD to square up building footprints. I will be adding a set of scripts for PostGIS to do the same at the database level.

Other smaller things are ending up on the cutting room floor, including a soup-to-nuts tutorial on how to use TravelingSalesPerson (pgr_tsp and related tools) in pgRouting.

So, stay tuned for those.  Really cool thing– once I recover from book writing, expect more blog posts here than have been in the past year or so.  I’m looking forward to being back… .

Driving Distance as compared to Buffer Distance

Driving Distance as compared to Buffer Distance

Posted in pgRouting, PostGIS, PostgreSQL, SQL | Tagged: , | 2 Comments »

Nice post on installing pgRouting on Ubuntu

Posted by smathermather on March 9, 2012

Just a quick link to a post I found on installing pgRouting on Ubuntu:

Posted in Database, pgRouting, PostGIS, PostgreSQL, Recreation, SQL, Trails | Tagged: , , , , , | Leave a Comment »