[mapserver-users] making pgrouting shortest distance function run with mapserver

Stephen Woodbridge woodbri at swoodbridge.com
Mon Jun 11 05:56:45 PDT 2012 

Annu,

This page is in error:

http://www.pgrouting.org/docs/howto/mapserver.html

The result of the dijkstra_sp() function returns a record set like:
    vertex_id | edge_id | cost
which does not contain a geometry column.

To get the geometry column which is in your dourol table you need to 
join the results back to that table, change you DATA statement to 
something like:

DATA "the_geom from (
select b.the_geom, a.* from
   (SELECT * FROM shortest_path('SELECT gid AS id, source::int4,
              target::int4, length::double precision AS cost,
         FROM dourol',3, 7, false, false)) as a
   dourol b where a.edge_id=b.gid) AS
               foo using unique gid using srid=-1"

-Steve W

On 6/11/2012 8:29 AM, Annu Anurag wrote:
> Dear friends. I'm new to pgrouting. It is part of my M.Tech thesis. I
> need to route from one point to another using dijkstra's algorithm. I
> intend to use pgrouting for it. I tried to follow the documentation of
> pgrouting along with some forum posts on websites. But despite having
> tried many combinations of code, I've been getting errors and I haven't
> been able to get the code to run. It is supposed to be simple but I'm
> obviously making a silly mistake somewhere. Please look into the code
> that I'm attaching and send me some suggestions as to how to make it
> work. Thank you very much. Here's the relevant layer code:
>
>          LAYER
>              NAME "route"
>              CONNECTIONTYPE postgis
>              CONNECTION "user=postgres password=**********
> dbname=VSDelhidb host=localhost"
>              DEBUG 5
>              STATUS DEFAULT
>              TYPE LINE
>              DATA "the_geom from (SELECT * FROM shortest_path('SELECT
> gid AS id, source::int4, target::int4, length::double precision AS cost,
> length::double AS reverse_cost FROM edges_line', 50, 210, false, false))
> AS foo using unique gid using srid=-1"
>              TEMPLATE "t"
>
>               #
>              # //////////////////////////////////////////////////////THE
> CLASS THAT CLASSIFIES EVERYTHING IN IT. ////////////////////////////////
>               #
>
>              CLASS
>
>                  NAME "0"
>                  STYLE
>                      SYMBOL "circle"
>                      SIZE 10
>                      OUTLINECOLOR 255 0 0
>                      COLOR 165 42 42
>
>              END #_________END OF THE CLASS THAT CLASSIFIES
> EVERYTHING.___________##############################################################
>          END #________END OF THE LAYER THAT DRAWS
> EVERYTHING._________##############################################################################
>
>
> I am getting this error:
>
> msDrawMap(): Image handling error. Failed to draw layer named 'route'.
> msPostGISLayerWhichShapes(): Query error. Error executing query: ERROR:
> column "the_geom" does not exist LINE 1: select
> encode(ST_AsBinary(ST_Force_2D("the_geom"),'NDR'),'he... ^
>
>
> When I brought up the error log, this is what I got:
>
>
> [Mon Jun 11 17:33:41 2012].605652 msPostGISLayerNextShape called.
> [Mon Jun 11 17:33:41 2012].605688 msPostGISLayerFreeItemInfo called.
> [Mon Jun 11 17:33:41 2012].605716 msPostGISLayerClose called: the_geom
> from edges_arc
> [Mon Jun 11 17:33:41 2012].605764 msConnPoolRelease(Road4,user=postgres
> password=********** dbname=VSDelhidb host=localhost,0x88fe3f0)
> [Mon Jun 11 17:33:41 2012].605801 msConnPoolClose(user=postgres
> password=********** dbname=VSDelhidb host=localhost,0x88fe3f0)
> [Mon Jun 11 17:33:41 2012].606352 msDrawMap(): Layer 3 (Road4), 0.487s
> [Mon Jun 11 17:33:41 2012].606465 msPostGISLayerOpen called: the_geom
> from (SELECT * FROM shortest_path('SELECT gid AS id, source::int4,
> target::int4, length::double precision AS cost, length::double AS
> reverse_cost FROM edges_line', 50, 210, false, false)) AS foo using
> unique gid using srid=-1
> [Mon Jun 11 17:33:41 2012].606515 msPostGISLayerOpen: No connection in
> pool, creating a fresh one.
> [Mon Jun 11 17:33:41 2012].796074 msConnPoolRegister(route,user=postgres
> password=********** dbname=VSDelhidb host=localhost,0x88fe3f0)
> [Mon Jun 11 17:33:41 2012].834424 msPostGISLayerOpen: Got PostGIS
> version 10500.
> [Mon Jun 11 17:33:41 2012].834495 msPostGISLayerFreeItemInfo called.
> [Mon Jun 11 17:33:41 2012].834635 msPostGISLayerWhichShapes called.
> [Mon Jun 11 17:33:41 2012].834677 msPostGISParseData called.
> [Mon Jun 11 17:33:41 2012].834770 msPostGISParseData: unique_column=gid,
> srid=-1, geom_column_name=the_geom, table_name=(SELECT * FROM
> shortest_path('SELECT gid AS id, source::int4, target::int4,
> length::double precision AS cost, length::double AS reverse_cost FROM
> edges_line', 50, 210, false, false)) AS foo
> [Mon Jun 11 17:33:41 2012].834817 msPostGISBuildSQL called.
> [Mon Jun 11 17:33:41 2012].834855 msPostGISBuildSQLItems called.
> [Mon Jun 11 17:33:41 2012].834887 msPostGISBuildSQLItems: 0 items requested.
> [Mon Jun 11 17:33:41 2012].834919 msPostGISBuildSQLFrom called.
> [Mon Jun 11 17:33:41 2012].834949 msPostGISBuildSQLWhere called.
> [Mon Jun 11 17:33:41 2012].834978 msPostGISBuildSQLSRID called.
> [Mon Jun 11 17:33:41 2012].835008 msPostGISBuildSQLSRID: SRID provided (-1)
> [Mon Jun 11 17:33:41 2012].835037 msPostGISBuildSQLBox called.
> [Mon Jun 11 17:33:41 2012].835136 msPostGISLayerWhichShapes query:
> select encode(ST_AsBinary(ST_Force_2D("the_geom"),'NDR'),'hex') as
> geom,"gid" from (SELECT * FROM shortest_path('SELECT gid AS id,
> source::int4, target::int4, length::double precision AS cost,
> length::double AS reverse_cost FROM edges_line', 50, 210, false, false))
> AS foo where the_geom && GeomFromText('POLYGON((-6015.80966101695
> -4605.49,-6015.80966101695 4811.71,6544.54966101695
> 4811.71,6544.54966101695 -4605.49,-6015.80966101695 -4605.49))',-1)
> [Mon Jun 11 17:33:41 2012].837949 msPostGISLayerWhichShapes query
> status: PGRES_FATAL_ERROR (7)
> [Mon Jun 11 17:33:41 2012].838047 Error (msPostGISLayerWhichShapes()
> ) executing query: ERROR:  column "the_geom" does not exist
> LINE 1: select encode(ST_AsBinary(ST_Force_2D("the_geom"),'NDR'),'he...
>                                                ^
> [Mon Jun 11 17:33:41 2012].838102 msPostGISLayerWhichShapes(): Query
> error. Error executing query: ERROR:  column "the_geom" does not exist
> LINE 1: select encode(ST_AsBinary(ST_Force_2D("the_geom"),'NDR'),'he...
>                                                ^
>
> [Mon Jun 11 17:33:41 2012].838139 msPostGISLayerFreeItemInfo called.
> [Mon Jun 11 17:33:41 2012].838171 msPostGISLayerClose called: the_geom
> from (SELECT * FROM shortest_path('SELECT gid AS id, source::int4,
> target::int4, length::double precision AS cost, length::double AS
> reverse_cost FROM edges_line', 50, 210, false, false)) AS foo using
> unique gid using srid=-1
> [Mon Jun 11 17:33:41 2012].838205 msConnPoolRelease(route,user=postgres
> password=********** dbname=VSDelhidb host=localhost,0x88fe3f0)
> [Mon Jun 11 17:33:41 2012].838239 msConnPoolClose(user=postgres
> password=********** dbname=VSDelhidb host=localhost,0x88fe3f0)
> [Mon Jun 11 17:33:41 2012].838779 msDrawMap(): Image handling error.
> Failed to draw layer named 'route'.
> [Mon Jun 11 17:33:41 2012].840194 msPostGISLayerClose called: the_geom
> from edges_line
> [Mon Jun 11 17:33:41 2012].840272 msPostGISLayerClose called: the_geom
> from allfeatureslabel_point
> [Mon Jun 11 17:33:41 2012].840309 msPostGISLayerClose called: the_geom
> from namesofroads_text
> [Mon Jun 11 17:33:41 2012].840347 msPostGISLayerClose called: the_geom
> from edges_arc
> [Mon Jun 11 17:33:41 2012].840386 msPostGISLayerClose called: the_geom
> from (SELECT * FROM shortest_path('SELECT gid AS id, source::int4,
> target::int4, length::double precision AS cost, length::double AS
> reverse_cost FROM edges_line', 50, 210, false, false)) AS foo using
> unique gid using srid=-1
> [Mon Jun 11 17:33:41 2012].842833 freeLayer(): freeing layer at 0x88724e8.
> [Mon Jun 11 17:33:41 2012].842908 msPostGISLayerIsOpen called.
> [Mon Jun 11 17:33:41 2012].842953 freeLayer(): freeing layer at 0x88732e8.
> [Mon Jun 11 17:33:41 2012].842984 msPostGISLayerIsOpen called.
> [Mon Jun 11 17:33:41 2012].843025 freeLayer(): freeing layer at 0x8874220.
> [Mon Jun 11 17:33:41 2012].843057 msPostGISLayerIsOpen called.
> [Mon Jun 11 17:33:41 2012].843099 freeLayer(): freeing layer at 0x88750d8.
> [Mon Jun 11 17:33:41 2012].843152 msPostGISLayerIsOpen called.
> [Mon Jun 11 17:33:41 2012].843203 freeLayer(): freeing layer at 0x8875fb8.
> [Mon Jun 11 17:33:41 2012].843351 msPostGISLayerIsOpen called.
>
>
> Please help! Thank you!
>
>
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users

More information about the mapserver-users mailing list