pgRouting in OpenLayers and Mapserver

Question

I've set a WMS server using MapServer that pulls data from PostGIS table which is configured with source and target columns for pgRouting. I've stored a function in the database that returns the geometry of the Dijkstra's shortest path and in MapServer I've set a layer that uses Runtime Substitution to query the stored function. I'm also using OpenLayers to render the map on the browser using the

  ol.layer.Image({
        source: new ol.source.ImageWMS({
                url: 'http://localhost/cgi-bin/mapserv?map=/home/user/mapdata/mapfile.map',
                 params: {
                      'LAYERS': 'Roads,Parks,River,Railroads',
                      'FORMAT' : 'image/png'
                 } 
        })
    });

Now I want to be able to click on the map and have a routing layer according to the start click and end click. So in OpenLayers I use ol.Feature and on the click on the map I set the features a geometry using setGeometry() function. What I'm confused about is that if I want to pass these values to MapServer I'm pretty sure MapServer would not understand this since the Runtime Substitution mechanism is configured to accept source and target vertexes and OpenLayers' functions would return geometry. Do I need to write a php script that calculates the shortest vertex in of my click and pass these values to MapServer then? I'm confused about it.

Answer 1

I finally made it. I created a function in PostGIS that accepts four parameters, x1, y1, and x2, y2. Source and target in Dijkstra's algorithm are sub-queries that return the closet point. The function looks like this:

CREATE OR REPLACE FUNCTION route_function(
IN x1 numeric,
IN y1 numeric,
IN x2 numeric,
IN y2 numeric)
RETURNS TABLE(seq integer, path_seq integer, node bigint, edge bigint, cost double precision, agg_cost double precision, street text, geom text) AS
$BODY$
 WITH dijkstra AS(
   SELECT * FROM pgr_dijkstra('SELECT id, source, target, time AS cost FROM road_network',
   --source
   (SELECT source FROM road_network ORDER BY geom <-> ST_SetSrid(ST_MakePoint($1, $2), 2100) ASC LIMIT 1),
   --target
   (SELECT target from road_network ORDER BY geom <-> ST_SetSrid(ST_MakePoint($3, $4), 2100) ASC LIMIT 1),
  -- undirected
   false)   
  ),
   the_geom AS(
     SELECT dijkstra.*, b.street, ST_AsText(b.geom) FROM dijkstra INNER JOIN road_network AS b on dijkstra.edge = b.id
     )    
 SELECT * FROM the_geom WHERE CASE WHEN 'source' != 'target' THEN true ELSE false END;
$BODY$
LANGUAGE sql VOLATILE

After that you only need MapServer to render the geometry of this function using Run-time Subsitution. The mapfile for this layers is:

 LAYER  
   NAME 'route'
   VALIDATION             
        "x1" "^[0-9]+(\.[0-9]+)?$"
        "y1" "^[0-9]+(\.[0-9]+)?$"
        "x2" "^[0-9]+(\.[0-9]+)?$"
        "y2" "^[0-9]+(\.[0-9]+)?$"
        "default_x1" "300006.3" 
        "default_y1" "4373897.9"
        "default_x2" "300006.3"
        "default_y2" "4373897.9"  ##The default values are a small 'hack' so that nothing is drawn if you don't pass a parameter.If no defaults are included MapServer always tries to substitute and if it doesn't find anything it throws an error. This way I'm always drawing a not existent line let's say
   END
   METADATA
           #various WMS metadata
    END
    TYPE line
    STATUS on
    CLASSITEM "street"
    CONNECTIONTYPE POSTGIS
    CONNECTION 'host=localhost port=5432 user=user password=somePassword dbname=dbname'        
    DATA "geom FROM (SELECT * FROM route_function(%x1%, %y1%, %x2%, %y2%)) AS foo USING UNIQUE seq USING srid=2100"
    CLASS
        NAME "route"
        STYLE
            COLOR 255 0 0
            WIDTH 4
       END
   END
 END