How to implement pgRouting in a web GIS based on MapServer?

Question

I have 3d lines in PostGIS database (representing hiking trails). I'm using phpPgAdmin administration tool. I want to use Dijkstra method for finding shortest way. I inserted all needed columns to execute this function, for example:

    SELECT * FROM shortest_path('
   SELECT gid AS id,
          start_id::int4 AS source,
          end_id::int4 AS target,
          cost_length::float8 AS cost
   FROM network',
1,
135,
false,
false)

I have .html file and .map file. I alredy have some functions and layers installed in my web application (like layer for shaded relief map). Also, I already have defined layer for 3d hiking trails from PostGIS database and it is shown on the web map by checkbox.

I never worked with pgRouting and MapServer before and I need help how to implement this function in my web application. I need detailed answer on how to implement it in my .map file and .html file. I need to enable users to choose one starting node (I will name them, but I don't know if I need to name it all?) from the combobox I want to define in my .html file, and one end node, also from the combobox, and then enable showing shortest path, or maybe list of nodes where they need to go for shortest path from start point to end point.

Don't give me links like [1]: http://users.qgis.org/planet/user/4/tag/pgrouting/ because it doesn't give me an explanation about working with MapServer and pgRouting.

It is very important and emergency, because it is about my graduation thesis.

Thank you!

---

This is error when I'm trying to open my application:

writeLog(): Unable to access file. mslog.log msDrawMap(): Image handling error. 
Failed to draw layer named 'routing'. msPostGISLayerWhichShapes(): Query error. 
Error (ERROR: syntax error at or near "SELECT" LINE 1: ...) as geom,"gid" from 
(SELECT * FROM shortest_path(SELECT the... ^ ) executing query: 
select encode(ST_AsBinary(ST_Force_2D("the_geom"),'NDR'),'hex') as geom,"gid" from 
(SELECT * FROM shortest_path(SELECT the_geom as the_geom, gid AS id, %start_id%::int4 
AS source, %end_id%::int4 AS target, cost_length::float8 AS cost FROM network, 1, 135, 
false, false)) AS foo where the_geom && GeomFromText('POLYGON((561819.89148581     
5072537.66277129,561819.89148581 5093307.66277129,589524.782971619 
5093307.66277129,589524.782971619 5072537.66277129,561819.89148581 
5072537.66277129))',4326)

Answer 1

To use parameters collected from a web form in a MAP file you can use run-time substitution.

Then it's a case of adding in a standard layer that uses data from PostGIS with the variables that are to be replaced. Example layer for pgRouting here.

The syntax for the DATA parameter will need checking - test it in the database first.

LAYER
        NAME "routing"
        TYPE LINE
        STATUS DEFAULT
        CONNECTIONTYPE postgis
        CONNECTION "user=postgres host=localhost dbname=geo"
        DATA "the_geom from (SELECT * FROM shortest_path(SELECT geom as the_geom, gid AS id,
                  %start_id%::int4 AS source, 
                  %end_id%::int4 AS target, cost_length::float8 
                  AS cost FROM network, 1, 135, false, false)) 
                  AS foo using unique gid using srid=4326"
        TEMPLATE "t"
        CLASS
          NAME "0"
          STYLE
            SYMBOL "circle"
            SIZE 10
            COLOR 50 50 100
          END
        END
END

The DATA clause works as follows. The parts in bold are required by MapServer. The rest of the statement is executed in the database - so when testing in the database exclude the bold parts.

DATA "the_geom from ( SELECT geom as the_geom, gid AS id, %start_id%::int4 AS source, %end_id%::int4 AS target, cost_length::float8 AS cost FROM network, 1, 135, false, false)) AS foo using unique gid using srid=4326"

Answer 2

Based on @geographika's answer, it should be something like:

DATA "the_geom
  FROM network
  JOIN
  (SELECT * FROM shortest_path('
    SELECT gid AS id,
      start_id::int4 AS source,
      end_id::int4 AS target,
      shape_leng::float8 AS cost
    FROM network',
    %start_id%,
    %end_id%,
    false,
    false)) AS route
  ON
  network.gid = route.edge_id 
  using unique gid using srid=4326"