[pgrouting-users] pgr_drivingdistance (loop)

[Stephen Woodbridge]

Hi Carla,

It would be useful if you posted what your error message is.

I suspect the the following lines:

 > SELECT id1 as no, cost as cost' || i ||
 > ' FROM pgr_drivingdistance('

needs to get changed to:

 > SELECT id1 as no, cost as cost,' || i || '::integer
 >  FROM pgr_drivingdistance('

-Steve W


On 9/27/2013 5:59 AM, Carla Rebelo wrote:
> 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 <http://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
>
>
>
>
> _______________________________________________
> Pgrouting-users mailing list
> Pgrouting-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>