Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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)
share|improve this question
    
Usability question: How many entries will the comboboxes have? If you don't name them, how are users supposed to know which start/end node to chose? –  underdark Jul 1 '11 at 15:33
    
I thought to have about only 10 entries. But I want to know if I need to name all nodes, or just those 10 entries. –  againstflow Jul 1 '11 at 15:50
    
Every node needs an ID. Names are not necessary. –  underdark Jul 1 '11 at 16:07
    
Ok, every node has an ID. My question still stands, and it refers to connection between MapServer and pgRouting. –  againstflow Jul 1 '11 at 16:26

2 Answers 2

up vote 3 down vote accepted

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"

share|improve this answer
    
+1 for run-time substitution, looks handy. Shortest_path() doesn't return the_geom, it would have to be joined there. –  underdark Jul 2 '11 at 10:15
1  
Yes I'm unclear what the pg functions return. They'll need an Id and geometry field as a minimum, and also the SRID should be set. –  geographika Jul 2 '11 at 10:20
    
Something is wrong with "SELECT geom AS the_geom". My geom column in table network has name "the_geom". But something is wrong with selecting the_geom or the_geom from because pgRouting function doesn't return geometry. –  againstflow Jul 2 '11 at 10:58
    
Try simply SELECT the_geom. Get the SQL correct by testing directly in the database and then put it in the MapFile. –  geographika Jul 2 '11 at 11:06
    
I tested it in phpPgAdmin and it says it is problem with this first statement "the_geom from" –  againstflow Jul 2 '11 at 11:11

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"
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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