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

  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

-- 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');

-- 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;

-- 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 = 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;');
    RETURN 'Completed the driving distance calculations';
    CLOSE cur_school;

  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:

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.