Batch driving distance - a pgRouting function
A function to batch process driving distance results for a set of input points, in this case, schools.
The function creates a table to hold the results (dropping it if it exists) and then selects each school from the your_schools table and calculates the driving distance numbers. For each school it writes the results to a temp table (temp_dd, dropping it first if it exists) and writes those results to the output table (school_driving_distance). In the end you have a table with a lot of rows (more or less depending on your cost) with which you can do neat things with QGIS Time Manager.
The schools data needs to be pre-processed to give each school an id field, school_nn, which contains the node id of the nearest network node.
Note: You'll need to VACUUM ANALYZE your output table to update the statistics.
-- Function: public.school_driving_distance() -- DROP FUNCTION public.school_driving_distance(); CREATE OR REPLACE FUNCTION public.school_driving_distance() RETURNS character varying AS $BODY$ DECLARE cur_school refcursor; v_snn integer; --variable for the school nearest node v_node integer; v_seq integer; --variable for the sequence id v_cost double precision; --variable for the cost results v_geom geometry; --variable for the output geometry v_sql varchar(1000); --variable to hold some SQL BEGIN -- Drop the results table first RAISE NOTICE 'Dropping table...'; v_sql:='DROP TABLE IF EXISTS public.school_driving_distance;'; EXECUTE v_sql; -- Create a new results table next RAISE NOTICE 'Creating table...'; v_sql:='CREATE TABLE IF NOT EXISTS public.school_driving_distance ( gid serial NOT NULL, seq integer, node integer, school_nn integer, agg_cost double precision, geometry geometry(Point,27700), CONSTRAINT school_dd_ipk PRIMARY KEY (gid) ); CREATE INDEX school_driving_distance_geometry_ixs ON public.school_driving_distance USING gist(geometry);'; EXECUTE v_sql; -- Process the schools RAISE NOTICE 'Processing school...'; OPEN cur_school FOR EXECUTE format('SELECT school_nn FROM your_schools WHERE school_nn IS NOT NULL'); LOOP -- Drop the temp table v_sql:='DROP TABLE IF EXISTS temp_dd;'; EXECUTE v_sql; -- Create a temp table to hold results for each school v_sql:='CREATE TEMPORARY TABLE temp_dd( seq integer, node integer, school_nn integer, agg_cost double precision, geometry geometry(Point,27700) );'; EXECUTE v_sql; FETCH cur_school INTO v_snn; EXIT WHEN NOT FOUND; -- Do 30 minute driving distance calculation for each school INSERT INTO temp_dd(seq, node, school_nn, agg_cost, geometry) SELECT foo.seq AS v_seq, foo.node AS v_node, v_snn AS school_nn, foo.agg_cost AS v_cost, n.the_geom AS v_geom FROM pgr_drivingDistance( 'SELECT ogc_fid AS id, source, target, cost, reverse_cost FROM your_network', v_snn, -- school name 1800) AS foo -- change this cost to change the output LEFT JOIN your_network_vertices_pgr n ON n.id = foo.node; -- Insert results into driving distance table EXECUTE format('INSERT INTO public.school_driving_distance(seq,node,school_nn,agg_cost,geometry) SELECT * FROM temp_dd;'); END LOOP; RETURN 'Completed the driving distance calculations'; CLOSE cur_school; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; COMMENT ON FUNCTION public.school_driving_distance() IS '30 minute driving distance function for each school';
Once all the results are written to the output table you can then query the table to get the overall minimum distance to each school:
SELECT DISTINCT ON (node) * FROM school_driving_distance ORDER BY node, agg_cost ASC;
Here's a result from the function above processed with QGIS Time Manager and stitched together with Avidemux and VLC.
And another one.
See this stackexchange post for another way of doing it.