PSQL error when using Mapnik Postgis Plugin for pgRouting #1518

Closed
Exception84 opened this Issue Oct 4, 2012 · 5 comments

Comments

Projects
None yet
3 participants

Hi guys!

I'm sorry if this is not the right forum for my issue, however I didn't know where else to put my concern.

I have huge troubles using the Mapnik Postgis plugin when a pgRouting query is executed. The query is definitely OK, because when I test it directly from the pgAdmin Query tool, everything works fine. The query calculates the shortest path between two POIs in Seattle (Space Needle and Museum of Flight) and looks as follows:

SELECT way, highway, name,
       case when tunnel in ('yes','true','1') then 'yes'::text else tunnel end as tunnel
FROM planet_osm_roads
WHERE osm_id IN
(
SELECT osm_id from roads_po where gid IN (
SELECT edge_id FROM shortest_path('SELECT gid as id, source::integer, target::integer,x1,y1,x2,y2,cost,reverse_cost from roads_po',

    (SELECT id(foo.x)
    FROM (
    SELECT find_node_by_nearest_link_within_distance(
    'POINT(-122.349348 47.620548)',
    0.5,
    'roads_po')::link_point as x
    ) AS foo),

    (SELECT id(foo.x)
    FROM (
    SELECT find_node_by_nearest_link_within_distance(
    'POINT(-122.297388 47.518696)',
    0.5,
    'roads_po')::link_point as x
    ) AS foo),

    true,

    true)
)
)
order by z_order

When I put this SQL query into my Mapnik stylesheet between the Parameter tags with name="table"

I get the following PSQL error:

Configuration error: Postgis Plugin: PSQL error:
FEHLER:  Syntaxfehler bei »,«
LINE 1: ..., srid FROM geometry_columns WHERE f_table_name='roads_po','
                                                                     ^
Full sql was: 'SELECT f_geometry_column, srid FROM geometry_columns WHERE f_tabl
e_name='roads_po',''
  encountered during parsing of layer 'poi-to-poi-labeling' in Layer at line 9 o
f '../data/google_style/google_style.xml'
Press any key to continue . . .

Because of the mistake, I inserted some lines of Code into the Postgis plugin code to output the query text into a file on the disc. The query that fails looks like

SELECT f_geometry_column, srid FROM geometry_columns WHERE f_table_name='roads_po','

I really don't know why this obviously wrong query is executed. Does somebody know, where the mistake is?

I thank you all in advance for your answers!

Best regards,
Exception84

Contributor

lightmare commented Oct 4, 2012

The problem is that the postgis plugin uses your query as a literal ('<your query>') to learn something about your query but doesn't escape it properly, which triggers a parse error if you have a literal in your query ('POINT (...)'). Doubling all apostrophes doesn't work, because then there'd be a parse error later, when it fetches data. The only workaround I've found so far is to use dollar-escaped literals in queries:

select st_geomfromtext($TAG$ POINT(-122.349348 47.620548) $TAG$);
Contributor

lightmare commented Oct 4, 2012

Another option is to specify datasource parameters "geometry_field" and "srid", then the plugin won't do the bad query.

Owner

springmeyer commented Oct 4, 2012

Yes, manually supplying those options, and/or geometry_table and extent, will help reduce the need for mapnik to try to look up metadata by parsing the SQL statement. We don't ever intend for Mapnik's internal sql parser to be able to handle queries this complex, so passing more options is a good workaround and solution. Closing.

springmeyer closed this Oct 4, 2012

Contributor

lightmare commented Oct 4, 2012

Well "complex"... I hit this with something as simple as select st_buffer('<copy-paste-wkt>', 5). I think the plugin could use PQescapeLiteral to escape it, or at least use some hard-to-encounter-in-user-input dollar-escape instead of just apostrophes.

Owner

springmeyer commented Oct 4, 2012

good point. by "complex" in this case I was referring to the 5 occurrences of FROM - depending on the order Mapnik will guess horribly wrong at which one might be beside the table holding the geodata. Improving the handling of escaping sounds very worthy.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment