<div dir="ltr">i again,<br><br>Thanks for the feedback.<br><br>I believe I'm nearly there...<br><br>now if I run:<br><br>-----------------------------------------------------<br>DO $$<br>DECLARE<br> i integer;<br>BEGIN<br>
FOR i IN (select nn from numeros)<br>LOOP<br>EXECUTE <br>'create table contagio' || i || ' as<br>SELECT *<br> FROM pgr_drivingdistance("<br> SELECT gid AS id,<br> source,<br> target,<br>
tempo::float8 AS cost<br> FROM lisboa",<br> ' || i || ' ,<br> 30,<br> false,<br> false)<br>';<br>END LOOP;<br>END; <br>$$;<br>------------------------------------------------------<br>
<br>I get:<br>-----------------------------------------------------<br>NOTICE: identifier "<br> SELECT gid AS id,<br> source,<br> target,<br> tempo::float8 AS cost<br> FROM lisboa" will be truncated to "<br>
SELECT gid AS id,<br> source,<br> target,<br> "<br>CONTEXT: SQL statement "create table contagio18 as<br>SELECT *<br> FROM pgr_drivingdistance("<br> SELECT gid AS id,<br> source,<br>
target,<br> tempo::float8 AS cost<br> FROM lisboa",<br> 18 ,<br> 30,<br> false,<br> false)<br>"<br>PL/pgSQL function "inline_code_block" line 7 at EXECUTE statement<br>
<br><br>ERROR: column "<br> SELECT gid AS id,<br> source,<br> target,<br> " does not exist<br>LINE 3: FROM pgr_drivingdistance("<br> ^<br>QUERY: create table contagio18 as<br>
SELECT *<br> FROM pgr_drivingdistance("<br> SELECT gid AS id,<br> source,<br> target,<br> tempo::float8 AS cost<br> FROM lisboa",<br> 18 ,<br> 30,<br> false,<br>
false)<br><br>CONTEXT: PL/pgSQL function "inline_code_block" line 7 at EXECUTE statement<br><br><br>********** Error **********<br><br>ERROR: column "<br> SELECT gid AS id,<br> source,<br>
target,<br> " does not exist<br>SQL state: 42703<br>Context: PL/pgSQL function "inline_code_block" line 7 at EXECUTE statement<br>-------------------------------------------------------------------------------------------<br>
<br>Do you have any clue what he's complaining about.<br><br>Thanks again.<br>Carla<br></div><div class="gmail_extra"><br><br><div class="gmail_quote">2013/9/27 Stephen Woodbridge <span dir="ltr"><<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><br>
In a quoted string like 'string with '' in it' you have to double the quote marks for the embedded quote.<div class="im"><br>
<br>
DO $$<br>
DECLARE<br>
i integer;<br>
BEGIN<br>
FOR i IN (select nn from numero)<br>
LOOP<br>
EXECUTE<br>
'create table contagio' || i || ' as<br>
SELECT id1 as no, cost as cost' || i ||<br>
' FROM pgr_drivingdistance(''<br>
SELECT gid AS id,<br>
source,<br>
target,<br>
tempo::float8 AS cost<br>
FROM lisboa'',<br>
18 ,<br>
30,<br>
false,<br>
false)'<br>
;<br>
<br>
<br></div><div><div class="h5">
On 9/27/2013 11:08 AM, Carla Rebelo wrote:<br>
</div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div class="h5">
Hi Stephen,<br>
<br>
Thanks for your reply.<br>
<br>
Sorry for the incomplete information...<br>
<br>
Running que code:<br>
<br>
------------------------------<u></u>------------------------------<u></u>-----<br>
DO $$<br>
DECLARE<br>
i integer;<br>
BEGIN<br>
FOR i IN (select nn from numero)<br>
LOOP<br>
EXECUTE<br>
'create table contagio' || i || ' as<br>
SELECT id1 as no, cost as cost' || i ||<br>
' FROM pgr_drivingdistance('<br>
SELECT gid AS id,<br>
source,<br>
target,<br>
tempo::float8 AS cost<br>
FROM lisboa',<br>
18 ,<br>
30,<br>
false,<br>
false)'<br>
;<br>
return the error:<br>
<br>
------------------------------<u></u>-----------<br>
ERROR: syntax error at or near "SELECT"<br>
LINE 11: SELECT gid AS id,<br>
------------------------------<u></u>-----------<br>
<br>
I think I understand what's wrong. I have a query string inside a query<br>
string.<br>
<br>
I tried to solve this using $$, but it doesn't work.<br>
<br>
If I run:<br>
<br>
-----<br>
SELECT *<br>
FROM pgr_drivingdistance($$<br>
SELECT gid AS id,<br>
source,<br>
target,<br>
tempo::float8 AS cost<br>
FROM lisboa$$,<br>
18 ,<br>
30,<br>
false,<br>
false);<br>
-----<br>
<br>
It works fine. But if this code comes after EXECUTE as a query string,<br>
it doesn't work (a query string inside a query string)...<br>
<br>
Thanks fro any help,<br>
Carla<br>
<br>
ubuntu 12.04<br>
postgis version:<br>
"2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"<br>
pgrouting version:<br>
"(2.0.0-rc1,v2.0.0-beta,50,<u></u>6a63bc1,develop,1.46.1)"<br>
<br>
<br>
<br>
<br>
END LOOP;<br>
END;<br>
$$;<br>
------------------------------<u></u>------------------------------<u></u>-------<br>
<br>
return the error:<br>
<br>
------------------------------<u></u>-----------<br>
ERROR: syntax error at or near "SELECT"<br>
LINE 11: SELECT gid AS id,<br>
------------------------------<u></u>-----------<br>
<br>
I think I understand what's wrong. I have a query string inside a query<br>
string.<br>
<br>
I tried to solve this using $$, but it doesn't work.<br>
<br>
If I run:<br>
<br>
-----<br>
SELECT *<br>
FROM pgr_drivingdistance($$<br>
SELECT gid AS id,<br>
source,<br>
target,<br>
tempo::float8 AS cost<br>
FROM lisboa$$,<br>
18 ,<br>
30,<br>
false,<br>
false);<br>
-----<br>
<br>
It works fine. But if this code comes after EXECUTE as a query string,<br>
it doesn't work (a query string inside a query string)...<br>
<br>
Thanks fro any help,<br>
Carla<br>
<br>
ubuntu 12.04<br>
postgis version:<br>
"2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"<br>
pgrouting version:<br>
"(2.0.0-rc1,v2.0.0-beta,50,<u></u>6a63bc1,develop,1.46.1)"<br>
<br>
<br>
<br>
<br>
<br>
2013/9/27 Stephen Woodbridge <<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a><br></div></div>
<mailto:<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.<u></u>com</a>>><div><div class="h5"><br>
<br>
Hi Carla,<br>
<br>
It would be useful if you posted what your error message is.<br>
<br>
I suspect the the following lines:<br>
<br>
<br>
> SELECT id1 as no, cost as cost' || i ||<br>
> ' FROM pgr_drivingdistance('<br>
<br>
needs to get changed to:<br>
<br>
> SELECT id1 as no, cost as cost,' || i || '::integer<br>
> FROM pgr_drivingdistance('<br>
<br>
-Steve W<br>
<br>
<br>
<br>
On 9/27/2013 5:59 AM, Carla Rebelo wrote:<br>
<br>
Dear all,<br>
<br>
I need to calculate the catchment area for a number of nodes<br>
(schools).<br>
<br>
I'm using pgr_driving distance and adding geometry from the<br>
nodes table.<br>
<br>
-----<br>
My code creates a table for each destination. Example (for node<br>
71197):<br>
<br>
drop table if exists contagio71197;<br>
create table contagio71197 as<br>
SELECT foo.id1 as no, foo.cost as cost71197, vertices_tmp.the_geom<br>
FROM vertices_tmp inner join pgr_drivingdistance('<br>
SELECT gid AS id,<br>
source,<br>
target,<br>
tempo::float8 AS cost<br>
FROM lisboa',<br>
71197,<br>
20,<br>
false,<br>
false) as foo<br>
on (<a href="http://vertices_tmp.id" target="_blank">vertices_tmp.id</a> <<a href="http://vertices_tmp.id" target="_blank">http://vertices_tmp.id</a>><br></div></div>
<<a href="http://vertices_tmp.id" target="_blank">http://vertices_tmp.id</a>> = foo.id1);<br>
<br>
<br>
------------------------------<u></u>__---<div><div class="h5"><br>
Now, since I have around 100 destinations, I tried to loop<br>
through the<br>
list of nodes.<br>
<br>
I wrote the following code (column numero.nn contains the list<br>
of nodes<br>
which constitute the destinations:<br>
<br>
'<br>
<br>
DO $$<br>
DECLARE<br>
i integer;<br>
BEGIN<br>
FOR i IN (select nn from numero)<br>
LOOP<br>
EXECUTE<br>
'create table contagio' || i || ' as<br>
SELECT id1 as no, cost as cost' || i ||<br>
' FROM pgr_drivingdistance('<br>
SELECT gid AS id,<br>
source,<br>
target,<br>
tempo::float8 AS cost<br>
FROM lisboa', '<br>
|| i || ' ,<br>
30,<br>
false,<br>
false)'<br>
;<br>
END LOOP;<br>
END;<br>
$$;<br>
<br></div></div>
------------------------------<u></u>__----------------------------<u></u>--__---------------<div class="im"><br>
<br>
Now, I know where the problem is: the query string after EXECUTE<br>
has a<br>
function that uses another query string as an input<br>
(pgr_drivingDistance(text sql, ......)<br>
<br>
Probably there's a simple solution, but I can't get it.<br>
<br>
Any help.<br>
Thank you very much.<br>
<br>
Carla<br>
<br>
<br>
<br>
<br></div>
______________________________<u></u>___________________<br>
Pgrouting-users mailing list<br>
Pgrouting-users@lists.osgeo.__<u></u>org<br>
<mailto:<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.<u></u>osgeo.org</a>><br>
<a href="http://lists.osgeo.org/__mailman/listinfo/pgrouting-__users" target="_blank">http://lists.osgeo.org/__<u></u>mailman/listinfo/pgrouting-__<u></u>users</a><br>
<<a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/<u></u>mailman/listinfo/pgrouting-<u></u>users</a>><br>
<br>
<br>
______________________________<u></u>___________________<br>
Pgrouting-users mailing list<br>
Pgrouting-users@lists.osgeo.__<u></u>org<br>
<mailto:<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.<u></u>osgeo.org</a>><br>
<a href="http://lists.osgeo.org/__mailman/listinfo/pgrouting-__users" target="_blank">http://lists.osgeo.org/__<u></u>mailman/listinfo/pgrouting-__<u></u>users</a><div class="im"><br>
<<a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/<u></u>mailman/listinfo/pgrouting-<u></u>users</a>><br>
<br>
<br>
<br>
<br>
______________________________<u></u>_________________<br>
Pgrouting-users mailing list<br>
<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.<u></u>org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/<u></u>mailman/listinfo/pgrouting-<u></u>users</a><br>
<br>
</div></blockquote><div class="HOEnZb"><div class="h5">
<br>
______________________________<u></u>_________________<br>
Pgrouting-users mailing list<br>
<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.<u></u>org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/<u></u>mailman/listinfo/pgrouting-<u></u>users</a><br>
</div></div></blockquote></div><br></div>