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.