pgrouting-isochrones

pgRouting on Windows 7

pgrouting-networkDrive time isochrones with pgRouting and PostGIS on Windows 7 Pro.  I am using Postgresql 9.0, 9.1 and 9.2, PostGIS 2.0.3 and pgRouting 1.0.7-dev.  Anita Graser has some excellent pgRouting resources on her blog  and I used them in conjunction with Jo Cook’s pgRouting with ITN guide to load and build my network dataset.

One of the problems I had while following this post was that I had more than the three airports she used in her example and to manually update the table 116 times for each play park in the county was not an option.

So, how to write a looping function in Postgresql to loop through all the play parks and calculate all the drive times for each facility in one go?

Postgresql docs are a good place to go and this is what worked for me in the end.  Once the table is complete finish up using Anita’s how to.

-- 1. create calculate playpark costs function
CREATE OR REPLACE FUNCTION insertPlayParkCosts() RETURNS INTEGER AS $$
-- NOTE: function returns an integer representing the next play park nearest node id
DECLARE
 nn INTEGER;
BEGIN
-- NOTE: loops through every nearest_node, calculating drive times and inserting records into the table
FOR nn IN SELECT nearest_node FROM osplayparks_pts LOOP
 INSERT INTO catchment_pp (
 SELECT
 id,
 the_geom,
 (SELECT sum(cost) FROM (
 SELECT * FROM shortest_path('
 SELECT objectid AS id,
 source::int4 AS source,
 target::int4 AS target,
 traveltime::float8 AS cost
 FROM angus_routes',
 nn, -- this updates each time with a new value for nearest_node
 id,
 false,
 false)) AS foo ) AS cost
 FROM vertices_tmp);
END LOOP;
END;
$$ LANGUAGE 'plpgsql';

-- 2. run calculate playpark cost function
SELECT * FROM insertPlayParkCosts();

Leave a Reply

Your email address will not be published. Required fields are marked *