[pgrouting-users] pgr_drivingdistance (loop)

[Carla Rebelo]

Hi Stephen,

Thanks for your reply.

Sorry for the incomplete information...

Running que code:

-----------------------------------------------------------------
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',
      18 ,
      30,
      false,
      false)'
;
return the error:

-----------------------------------------
ERROR:  syntax error at or near "SELECT"
LINE 11:       SELECT gid AS id,
-----------------------------------------

I think I understand what's wrong. I have a query string inside a query
string.

I tried to solve this using $$, but it doesn't work.

If I run:

-----
SELECT *
 FROM pgr_drivingdistance($$
      SELECT gid AS id,
          source,
          target,
          tempo::float8 AS cost
      FROM lisboa$$,
      18 ,
      30,
      false,
      false);
-----

It works fine. But if this code comes after EXECUTE as a query string, it
doesn't work (a query string inside a query string)...

Thanks fro any help,
Carla

ubuntu 12.04
postgis version:
"2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"
pgrouting version:
"(2.0.0-rc1,v2.0.0-beta,50,6a63bc1,develop,1.46.1)"




END LOOP;
END;
$$;
-------------------------------------------------------------------

return the error:

-----------------------------------------
ERROR:  syntax error at or near "SELECT"
LINE 11:       SELECT gid AS id,
-----------------------------------------

I think I understand what's wrong. I have a query string inside a query
string.

I tried to solve this using $$, but it doesn't work.

If I run:

-----
SELECT *
 FROM pgr_drivingdistance($$
      SELECT gid AS id,
          source,
          target,
          tempo::float8 AS cost
      FROM lisboa$$,
      18 ,
      30,
      false,
      false);
-----

It works fine. But if this code comes after EXECUTE as a query string, it
doesn't work (a query string inside a query string)...

Thanks fro any help,
Carla

ubuntu 12.04
postgis version:
"2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"
pgrouting version:
"(2.0.0-rc1,v2.0.0-beta,50,6a63bc1,develop,1.46.1)"





2013/9/27 Stephen Woodbridge <woodbri at swoodbridge.com>

> 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 <Pgrouting-users at lists.osgeo.org>
>> http://lists.osgeo.org/**mailman/listinfo/pgrouting-**users<http://lists.osgeo.org/mailman/listinfo/pgrouting-users>
>>
>>
> ______________________________**_________________
> Pgrouting-users mailing list
> Pgrouting-users at lists.osgeo.**org <Pgrouting-users at lists.osgeo.org>
> http://lists.osgeo.org/**mailman/listinfo/pgrouting-**users<http://lists.osgeo.org/mailman/listinfo/pgrouting-users>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20130927/9a699bd0/attachment.html>