3

When running pg_routing to generate catchment areas I got this error when running the 2 queries below:

 SQL Error [XX000]: ERROR: parse error - invalid geometry
  Hint: "se" <-- parse error at position 2 within geometry
  Position: 26
select pgr_alphaShape(
 'select
 v.id::int4, st_x(v.the_geom) as x, st_y(v.the_geom) as y
 from(
 select * from pgr_drivingDistance(''select gid as id, source,
target, cost_time as cost from roadslink_fixed'', 120829, 300)
 ) as dd
 left outer join public.roadslink_fixed_vertices_pgr v on dd.node = v.id'
);

select pgr_pointsAsPolygon(
'select
 v.id::int4, st_x(v.the_geom) as x, st_y(v.the_geom) as y
 from(
 select * from pgr_drivingDistance(''select gid as id, source,
target, cost_time as cost from roadslink_fixed'', 120829, 300)
 ) as dd
 left outer join public.roadslink_fixed_vertices_pgr v on dd.node = v.id'
);

I'm not sure why I'm getting this error, I have follow different steps to build the roads in different workshops and in all I'm getting this error. Someone has have the same issue for building catchment areas?

2

1 Answer 1

6

Note that pgr_pointsAsPolygon has been moved to legacy as of pgRouting 3.0.0 - I won't address this function.


Read the docs carefully; the full function signature

pgr_alphaShape(geometry, [spoon_radius]) RETURNS geometry

requires a GEOMETRY type as it's main parameter, while you are passing in a string literal of a SQL query.


Assuming the query you are trying to pass in, you'd want to run something along the lines of this:

SELECT
  pgr_alphaShape(
    -- the bracketed sub-query must return a single GEOMETRY value to get accepted as parameter to pgr_alphaShape
    (
      SELECT
        ST_Collect(v.the_geom)
      FROM
        pgr_drivingDistance(
          'SELECT gid as id, source, target, cost_time AS cost FROM roadslink_fixed',
          120829,
          300
        ) AS dd
        LEFT OUTER JOIN
        public.roadslink_fixed_vertices_pgr v
          ON dd.node = v.id
    ),
    <spoon_radius>
  )
;

If you need to use special/reserved characters (e.g. " or ') inside a string in locations where you need to pass in a string literal itself (expressions, function parameters, variable assignments in function bodies etc.), you can wrap string literals in $$, e.g.

  • SELECT $$ ' " / $$ AS string_literal;

  • or the named variant

    SELECT $SPECIAL_CHARS$ ' " / $SPECIAL_CHARS$ AS string_literals;

without escaping.

3
  • Thanks for the answer! it doesn't return the error but instead empty geometry collection what confused me, because up to st_collect is correct, how should I pass the collection to build a catchment area then? because anything after the alphashape doesn't work i.e. st_concavehull Mar 28, 2023 at 15:56
  • does the geometry get returned if you just run the pgr_drivingDistance?
    – ziggy
    Mar 28, 2023 at 19:50
  • Yes it return collection until we pass it to alpha shape which results in empty Mar 29, 2023 at 6:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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