pgr_pointAsPolygon doesn't work for queries with quotes in them

[robe2]

I tried running this query:

SELECT 1 As id, pgr_pointsAsPolygon('SELECT v.id::int4, ST_X(v.the_geom) AS x, ST_Y(v.the_geom) As y 
        FROM pgr_drivingDistance(''SELECT gid As id, source, target, 
            cost_s AS cost, reverse_cost_s AS reverse_cost 
            FROM ospr.ways'',
        (SELECT n.id 
            FROM ospr.ways_vertices_pgr AS n
              ORDER BY ST_SetSRID(
                ST_Point(-77.02734, 38.97328),4326) <-> n.the_geom LIMIT 1) 
        , 20, true 
      ) AS dd INNER JOIN ospr.ways_vertices_pgr AS v ON dd.node = v.id');

and got this error:

ERROR:  syntax error at or near "SELECT"
LINE 2:         FROM pgr_drivingDistance('SELECT gid As id, source, ...
                                          ^
QUERY:  SELECT x, y FROM pgr_alphashape('SELECT v.id::int4, ST_X(v.the_geom) AS x, ST_Y(v.the_geom) As y 
        FROM pgr_drivingDistance('SELECT gid As id, source, target, 
            cost_s AS cost, reverse_cost_s AS reverse_cost 
            FROM ospr.ways',
        (SELECT n.id 
            FROM ospr.ways_vertices_pgr AS n
              ORDER BY ST_SetSRID(
                ST_Point(-77.02734, 38.97328),4326) <-> n.the_geom LIMIT 1) 
        , 20, true 
      ) AS dd INNER JOIN ospr.ways_vertices_pgr AS v ON dd.node = v.id', 0)
CONTEXT:  PL/pgSQL function pgr_pointsaspolygon(character varying,double precision) line 17 at FOR over EXECUTE statement

The problem is the line in pgr_pointsAsPolygon that does this:

FOR vertex_result IN EXECUTE 'SELECT x, y FROM pgr_alphashape('''|| query || ''', ' || alpha || ')' 

If you change that to a parameterized query:

FOR vertex_result IN EXECUTE 'SELECT x, y FROM pgr_alphashape($1, $2)' USING query, alpha 

Then it works.

[robe2]

Might be worthwhile back porting this fix to 2.1 and 2.0

[robe2]

As noted by @cvvergara this works:

SELECT *
FROM pgr_alphaShape( $$SELECT v.id::int4, ST_X(v.the_geom) AS x, ST_Y(v.the_geom) As y 
        FROM pgr_drivingDistance('SELECT gid As id, source, target, 
            cost_s AS cost, reverse_cost_s AS reverse_cost 
            FROM ospr.ways',
        (SELECT n.id 
            FROM ospr.ways_vertices_pgr AS n
              ORDER BY ST_SetSRID(
                ST_Point(-77.02734, 38.97328),4326) <-> n.the_geom LIMIT 1) 
        , 20, true 
      ) AS dd INNER JOIN ospr.ways_vertices_pgr AS v ON dd.node = v.id$$);

but annoying thing i is its inconsistent with pgr_pointsAsPolygons, because to use same query in that I need double-quotes

SELECT *
FROM pgr_pointsAsPolygon( $$SELECT v.id::int4, ST_X(v.the_geom) AS x, ST_Y(v.the_geom) As y 
        FROM pgr_drivingDistance(''SELECT gid As id, source, target, 
            cost_s AS cost, reverse_cost_s AS reverse_cost 
            FROM ospr.ways'',
        (SELECT n.id 
            FROM ospr.ways_vertices_pgr AS n
              ORDER BY ST_SetSRID(
                ST_Point(-77.02734, 38.97328),4326) <-> n.the_geom LIMIT 1) 
        , 20, true 
      ) AS dd INNER JOIN ospr.ways_vertices_pgr AS v ON dd.node = v.id$$);

[robe2]

I pushed this to 2.3.0 since even though I consider this a bug, it would be a breaking change for anyone who is working around this broken functionality. If we do put in 2.2.2 probably should be a news note that this is a breaking change, but brings consistency.

[cvvergara]

actually, pushed to 3.0, could not make it for 2.3

[dkastl]

Also mentioned here: http://gis.stackexchange.com/questions/215203/cannot-create-pgr-pointsaspolygon-from-pgr-drivingdistance-function

[cvvergara]

This function is deleted in v3.0 This one should be used https://docs.pgrouting.org/dev/en/pgr_alphaShape.html