[pgrouting-users] pgr_drivingdistance (loop)
Carla Rebelo
crrebelo at gmail.com
Fri Sep 27 09:08:29 PDT 2013
i again,
Thanks for the feedback.
I believe I'm nearly there...
now if I run:
-----------------------------------------------------
DO $$
DECLARE
i integer;
BEGIN
FOR i IN (select nn from numeros)
LOOP
EXECUTE
'create table contagio' || i || ' as
SELECT *
FROM pgr_drivingdistance("
SELECT gid AS id,
source,
target,
tempo::float8 AS cost
FROM lisboa",
' || i || ' ,
30,
false,
false)
';
END LOOP;
END;
$$;
------------------------------------------------------
I get:
-----------------------------------------------------
NOTICE: identifier "
SELECT gid AS id,
source,
target,
tempo::float8 AS cost
FROM lisboa" will be truncated to "
SELECT gid AS id,
source,
target,
"
CONTEXT: SQL statement "create table contagio18 as
SELECT *
FROM pgr_drivingdistance("
SELECT gid AS id,
source,
target,
tempo::float8 AS cost
FROM lisboa",
18 ,
30,
false,
false)
"
PL/pgSQL function "inline_code_block" line 7 at EXECUTE statement
ERROR: column "
SELECT gid AS id,
source,
target,
" does not exist
LINE 3: FROM pgr_drivingdistance("
^
QUERY: create table contagio18 as
SELECT *
FROM pgr_drivingdistance("
SELECT gid AS id,
source,
target,
tempo::float8 AS cost
FROM lisboa",
18 ,
30,
false,
false)
CONTEXT: PL/pgSQL function "inline_code_block" line 7 at EXECUTE statement
********** Error **********
ERROR: column "
SELECT gid AS id,
source,
target,
" does not exist
SQL state: 42703
Context: PL/pgSQL function "inline_code_block" line 7 at EXECUTE statement
-------------------------------------------------------------------------------------------
Do you have any clue what he's complaining about.
Thanks again.
Carla
2013/9/27 Stephen Woodbridge <woodbri at 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 at swoodbridge.com
>> <mailto:woodbri at swoodbridge.**com <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<Pgrouting-users at lists.osgeo.org>
>> >
>> http://lists.osgeo.org/__**mailman/listinfo/pgrouting-__**users<http://lists.osgeo.org/__mailman/listinfo/pgrouting-__users>
>> <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<Pgrouting-users at lists.osgeo.org>
>> >
>> http://lists.osgeo.org/__**mailman/listinfo/pgrouting-__**users<http://lists.osgeo.org/__mailman/listinfo/pgrouting-__users>
>>
>> <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>
>>
>>
> ______________________________**_________________
> 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/529e2792/attachment.html>
More information about the Pgrouting-users
mailing list