0

I have successfully build a pgr_DrivingDistance query,

select 
    id::int4,
    lon::float8 as x,
    lat::float8 as y
from 
pgr_DrivingDistance(
    'select gid as id, source, target, length as cost from ways',
    23199, 
    0.01, 
    false
) as isokron inner join ways_vertices_pgr on (isokron.node = ways_vertices_pgr.id)

to have successfully have an id, x, and y column for the output.

I want to use that output into pgr_PointsAsPolygon function, which will create a polygon from the (x, y) pair above.

But I'm having difficulty in building the combined. I try these, but without result.

select pgr_pointsAsPolygon(
'select 
    id,
    lon as x,
    lat as y
from 
    pgr_drivingDistance(
        ''select gid as id, source, target, length as cost from ways'',
        23199, 
        0.01, 
        false
    ) as isokron inner join ways_vertices_pgr on (isokron.node = ways_vertices_pgr.id)');

It keeps telling me this error:

ERROR:  syntax error at or near "select"
LINE 7:    'select gid as id, source, target, length as cost from wa...
            ^
QUERY:  SELECT x, y FROM pgr_alphashape('select 
    id,
    lon as x,
    lat as y
from 
    pgr_drivingDistance(
        'select gid as id, source, target, length as cost from ways',
        23199, 
        0.01, 
        false
    ) as isokron inner join ways_vertices_pgr on (isokron.node = ways_vertices_pgr.id)', 0)
CONTEXT:  PL/pgSQL function pgr_pointsaspolygon(character varying,double precision) line 17 at FOR over EXECUTE statement
********** Error **********

ERROR: syntax error at or near "select"
SQL state: 42601
Context: PL/pgSQL function pgr_pointsaspolygon(character varying,double precision) line 17 at FOR over EXECUTE statement

I've tried create the polygons (pgr_pointsAsPolygon) using all the vertices (without using pgr_drivingDistance) and it worked perfectly!

Whats wrong with the code? Is the double single-quote escape wrong?

  • As i search for answers, I know that this kind of problem can be solved by using VIEW.. but why does the query above doesnt work? – Keenan Gebze Oct 23 '16 at 13:37
3

We mentioned this issue in the pgRouting Practical Guide - http://locatepress.com/pgrouting (still in draft). Hopefully will be fixed in later pgRouting versions.

The work around is to do this (as DavidP pointed out using $$ is a first step), but the bug is that you still need '' in the inner query where as you really shouldn't need that:

SELECT pgr_pointsAsPolygon(
 $$ SELECT 
   id,
    lon as x,
    lat as y
FROM 
   pgr_drivingDistance(
    ''SELECT gid as id, source, target, length as cost from ways'',
    23199, 
    0.01, 
    false
) as isokron 
inner join ways_vertices_pgr 
   on (isokron.node = ways_vertices_pgr.id) $$);
0

It looks like your query is translated inside pgr_pointsAsPolygon into another query with bad syntax - multiple single quotes inside one string (look at the QUERY part of error message). I would try to replace double quetes ('') with $$ or you can use CTE syntax:

WITH isokron AS (
select 
    id::int4,
    lon::float8 as x,
    lat::float8 as y
from 
pgr_DrivingDistance(
    'select gid as id, source, target, length as cost from ways',
    23199, 
    0.01, 
    false) a
inner join ways_vertices_pgr b on (a.node = b.id)
)
SELECT
pgr_pointsAsPolygon(
'select 
    id,
    lon as x,
    lat as y
from isokron')
  • This won't work because the isokron is an alias name only valid within the CTE so not visible tot eh pgr_pointsAsPolygon. – LR1234567 Oct 23 '16 at 16:26
  • @LR1234567 I'm bit smarter now... – DavidP Oct 23 '16 at 19:36
  • @LR1234567 what is CTE? – Keenan Gebze Oct 24 '16 at 18:30
  • 1
    @KeenanGebze you can find more about CTE (Common Table Expression) in Postgres docs here: postgresql.org/docs/9.6/static/queries-with.html – DavidP Oct 25 '16 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.