[pgrouting-users] pgr_drivingdistance (loop)

pgRouting pgrouting-users
1

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

2

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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

3

Hi Stephen,

Thanks for your reply.

Sorry for the incomplete information…

Running que code:

···

2013/9/27 Stephen Woodbridge <woodbri@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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

4

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@swoodbridge.com
<mailto:woodbri@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@lists.osgeo.__org
        <mailto:Pgrouting-users@lists.osgeo.org>
        http://lists.osgeo.org/__mailman/listinfo/pgrouting-__users
        <http://lists.osgeo.org/mailman/listinfo/pgrouting-users&gt;

    _________________________________________________
    Pgrouting-users mailing list
    Pgrouting-users@lists.osgeo.__org
    <mailto:Pgrouting-users@lists.osgeo.org>
    http://lists.osgeo.org/__mailman/listinfo/pgrouting-__users
    <http://lists.osgeo.org/mailman/listinfo/pgrouting-users&gt;

_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

5

i again,

Thanks for the feedback.

I believe I’m nearly there…

now if I run:

···

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

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@swoodbridge.com

mailto:[woodbri@swoodbridge.com](mailto:woodbri@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@lists.osgeo.__org
mailto:[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@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@lists.osgeo.__org
mailto:[Pgrouting-users@lists.osgeo.org](mailto:Pgrouting-users@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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users