<div dir="ltr">Hi Stephen,<br><br>Thanks for your reply.<br><br>Sorry for the incomplete information...<br><br>Running que code:<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> 18 ,<br> 30,<br> false,<br> false)'<br>;<br>return the error:<br>
<br>-----------------------------------------<br>ERROR: syntax error at or near "SELECT"<br>LINE 11: SELECT gid AS id,<br>-----------------------------------------<br><br>I think I understand what's wrong. I have a query string inside a query 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, 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,6a63bc1,develop,1.46.1)"<br><br>
<br><br><br>END LOOP;<br>END; <br>$$;<br>-------------------------------------------------------------------<br><br>return the error:<br><br>-----------------------------------------<br>ERROR: syntax error at or near "SELECT"<br>
LINE 11: SELECT gid AS id,<br>-----------------------------------------<br><br>I think I understand what's wrong. I have a query string inside a query 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, 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,6a63bc1,develop,1.46.1)"<br><br><br><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">
Hi Carla,<br>
<br>
It would be useful if you posted what your error message is.<br>
<br>
I suspect the the following lines:<div class="im"><br>
<br>
> SELECT id1 as no, cost as cost' || i ||<br>
> ' FROM pgr_drivingdistance('<br>
<br></div>
needs to get changed to:<br>
<br>
> SELECT id1 as no, cost as cost,' || i || '::integer<br>
> FROM pgr_drivingdistance('<br>
<br>
-Steve W<div class="im"><br>
<br>
<br>
On 9/27/2013 5:59 AM, Carla Rebelo wrote:<br>
</div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="im">
Dear all,<br>
<br>
I need to calculate the catchment area for a number of nodes (schools).<br>
<br>
I'm using pgr_driving distance and adding geometry from the nodes table.<br>
<br>
-----<br>
My code creates a table for each destination. Example (for node 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></div>
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>> = foo.id1);<div><div class="h5"><br>
<br>
------------------------------<u></u>---<br>
Now, since I have around 100 destinations, I tried to loop through the<br>
list of nodes.<br>
<br>
I wrote the following code (column numero.nn contains the list 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>
------------------------------<u></u>------------------------------<u></u>---------------<br>
<br>
Now, I know where the problem is: the query string after EXECUTE 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></div>
______________________________<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>
</blockquote>
<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>
</blockquote></div><br></div>