[pgrouting-users] pgr_drivingdistance (loop)

[Carla Rebelo]

Dear all,

I need to calculate the catchment area for a number of nodes (schools).

I'm using pgr_driving distance and adding geometry from the nodes table.

-----
My code creates a table for each destination. Example (for node 71197):

drop table if exists contagio71197;
create table contagio71197 as
SELECT foo.id1 as no, foo.cost as cost71197, vertices_tmp.the_geom
 FROM vertices_tmp inner join pgr_drivingdistance('
      SELECT gid AS id,
          source,
          target,
          tempo::float8 AS cost
      FROM lisboa',
      71197,
      20,
      false,
      false) as foo
on (vertices_tmp.id = foo.id1);

---------------------------------
Now, since I have around 100 destinations, I tried to loop through the list
of nodes.

I wrote the following code (column numero.nn contains the list of nodes
which constitute the destinations:

'

DO $$
DECLARE
    i integer;
BEGIN
FOR i IN (select nn from numero)
LOOP
EXECUTE
'create table contagio' || i || ' as
SELECT id1 as no, cost as cost' || i ||
' FROM pgr_drivingdistance('
      SELECT gid AS id,
          source,
          target,
          tempo::float8 AS cost
      FROM lisboa', '
      || i || ' ,
      30,
      false,
      false)'
;
END LOOP;
END;
$$;

---------------------------------------------------------------------------

Now, I know where the problem is: the query string after EXECUTE has a
function that uses another query string as an input
(pgr_drivingDistance(text sql, ......)

Probably there's a simple solution, but I can't get it.

Any help.
Thank you very much.

Carla
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20130927/a8036b1a/attachment.html>