I'm trying to implement pgrouting with MapServer. It works right now but I have to enter the number of the source and target nodes in my mapfile by myself. What I want to do, is being able to use a click on the map and select start and end points. So the tutorial I'm following suggests that I create a VIEW in postgresql which is the following:

CREATE VIEW shortest_path AS
 min(r.seq) AS seq,
 e.old_id AS id,
 sum(e.time) AS time,
 sum(e.distance) AS distance,
ST_Collect(e.geom) AS geom
%cost% AS cost,
FROM roads_noded', %source%, %target%, false) AS r,
 roads_noded AS e
  r.id2 = e.id
  e.old_id, e.street, e.district;

but I get the following error:

ERROR:  syntax error at or near "%"
 LINE 17:   FROM roads_noded', %source%, %target%, false) AS r,
  ********** Error **********

 ERROR: syntax error at or near "%"
 SQL state: 42601
 Character: 302

In the beginning I assumed it because of the percentage symbol so I put %source% in quotes like '%source%'. But then I got another error:

ERROR: function pgr_dijkstra(unknown, unknown, unknown, boolean) is not unique
 SQL state: 42725
 Hint: Could not choose a best candidate function. You might need to add    explicit type casts.
Character: 192

So, I'm quite new to this. Am I going the right way? What is wrong here?


The tutorial you are following is telling you how to create an SQL View in GeoServer which lets you pass in parameters with %string% format.

You have provided code to create a PostgreSQL View which handles parameters a different way with $1.

create or replace function label_params(parm1 text, parm2 text)
  returns table (param_label text, param_graphics_label text)
  select ...
  WHERE region_label = $1 
     AND model_id = (SELECT model_id FROM models WHERE model_label = $2)
language sql;

See https://stackoverflow.com/questions/11401749/pass-in-where-parameters-to-postgresql-view

  • So this tutorial does not work with PostgreSQL? If I want to have a dynamic table this can only be achieved with functions? – Stefanos Dec 16 '16 at 12:37
  • @Stefanos I'm simply stating that the tutorial is telling you how to create an SQL View in Geoserver but your code you posted is creating a view in postgresql, either of which are achievable. Basically, if you want your view to be in Geoserver, then follow that tutorial more closely but if you want a view in postgresql then use the answer I provided. – kttii Dec 16 '16 at 15:24

As the answer of @kttii says, the pgRouting tutorial explains how to configure a Geoserver SQL View, which is a Geoserver specific feature.

I think the answer to your question is not how to create an SQL View correctly in PostgreSQL, but how to configure Mapserver to do variable Substitution. You need to read about "Run-time Substitution" for Mapserver: http://mapserver.org/cgi/runsub.html The terms are slightly different, but the idea is the same.

If you are more familiar with Mapserver you can accomplish the same. You can still create a new VIEW in PostgreSQL, but it's not a requirement, because with Mapserver you can specify the SQL command in your mapfile: http://mapserver.org/input/vector/postgis.html

  • Really??? I was heading towards learning how to make Functions in PostgreSQL in PL/PGSQL. In this way I could for example make a dynamic query and change it according to incoming "source" and "target values" so that routing is different each time. So confused about this.... – Stefanos Dec 19 '16 at 10:00
  • Did you try the pgRouting workshop with Geoserver instead of Mapserver? Then you will find out, that "Geoserver SQL View" is not a pl/pgsql function. It's just about passing variables in the URL and variable substitution, but Geoserver and Mapserver have a different usage. – dkastl Dec 20 '16 at 12:16
  • Stefano, if you need to completely change the definition of the query based on inputs, then using a plpgsql function is the way to go. You would still use macro substitution though - something like SELECT * FROM label_params('%my_label%', '%graphic%') would be the query you have in map server. In most cases, even when query needs to be slightly different, I've been able to just use CASE statements and embed the whole SQL in mapserver map file. If your SQL is long, I would highly suggest creating a function and just calling the function from mapserver. – LR1234567 Dec 27 '16 at 4:51

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.