[pgrouting-users] pgr_drivingdistance (loop)

[Stephen Woodbridge]

In a quoted string like 'string with '' in it' you have to double the 
quote marks for the embedded quote.

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


On 9/27/2013 11:08 AM, Carla Rebelo wrote:
> 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
> <mailto: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>
>         <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
>         <mailto: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
>     <mailto: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
> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>