Drive 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();