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

[Annu Anurag]

Dear Stephen sir,
I tried your suggestion sir. But I'm still having some errors. Is there
something wrong with the syntax of the statement? I changed the line in my
code to this as you had suggested:

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 edges_line', 50, 210, false, false)) as a edges_line
as b where a.edgeid=b.gid) AS foo using unique gid using srid=-1"

and I'm getting the following error now:

msDrawMap(): Image handling error. Failed to draw layer named 'route'.
msPostGISLayerWhichShapes(): Query error. Error executing query: ERROR:
syntax error at or near "edges_line" LINE 1: ...st FROM edges_line', 50,
210, false, false)) as a edges_line... ^

When I looked up the error log, I got this:

[Tue Jun 12 07:17:54 2012].140295 msPostGISLayerNextShape called.
[Tue Jun 12 07:17:54 2012].140331 msPostGISLayerFreeItemInfo called.
[Tue Jun 12 07:17:54 2012].140363 msPostGISLayerClose called: the_geom from
edges_arc
[Tue Jun 12 07:17:54 2012].140412 msConnPoolRelease(Road4,user=postgres
password=************ dbname=VSDelhidb host=localhost,0x896c410)
[Tue Jun 12 07:17:54 2012].140445 msConnPoolClose(user=postgres
password=************ dbname=VSDelhidb host=localhost,0x896c410)
[Tue Jun 12 07:17:54 2012].141013 msDrawMap(): Layer 3 (Road4), 0.508s
[Tue Jun 12 07:17:54 2012].141134 msPostGISLayerOpen called: 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
edges_line', 50, 210, false, false)) as a edges_line as b where
a.edgeid=b.gid) AS foo using unique gid using srid=-1
[Tue Jun 12 07:17:54 2012].141187 msPostGISLayerOpen: No connection in
pool, creating a fresh one.
[Tue Jun 12 07:17:54 2012].322140 msConnPoolRegister(route,user=postgres
password=************ dbname=VSDelhidb host=localhost,0x896c410)
[Tue Jun 12 07:17:54 2012].363321 msPostGISLayerOpen: Got PostGIS version
10500.
[Tue Jun 12 07:17:54 2012].363393 msPostGISLayerFreeItemInfo called.
[Tue Jun 12 07:17:54 2012].363433 msPostGISLayerWhichShapes called.
[Tue Jun 12 07:17:54 2012].363461 msPostGISParseData called.
[Tue Jun 12 07:17:54 2012].363535 msPostGISParseData: unique_column=gid,
srid=-1, geom_column_name=the_geom, table_name=(select b.the_geom, a.* FROM
(SELECT * FROM shortest_path('SELECT gid AS id, source::int4, target::int4,
length::double precision AS cost FROM edges_line', 50, 210, false, false))
as a edges_line as b where a.edgeid=b.gid) AS foo
[Tue Jun 12 07:17:54 2012].363568 msPostGISBuildSQL called.
[Tue Jun 12 07:17:54 2012].363597 msPostGISBuildSQLItems called.
[Tue Jun 12 07:17:54 2012].363629 msPostGISBuildSQLItems: 0 items requested.
[Tue Jun 12 07:17:54 2012].363660 msPostGISBuildSQLFrom called.
[Tue Jun 12 07:17:54 2012].363692 msPostGISBuildSQLWhere called.
[Tue Jun 12 07:17:54 2012].363721 msPostGISBuildSQLSRID called.
[Tue Jun 12 07:17:54 2012].363750 msPostGISBuildSQLSRID: SRID provided (-1)
[Tue Jun 12 07:17:54 2012].363780 msPostGISBuildSQLBox called.
[Tue Jun 12 07:17:54 2012].363876 msPostGISLayerWhichShapes query: select
encode(ST_AsBinary(ST_Force_2D("the_geom"),'NDR'),'hex') as geom,"gid" 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
edges_line', 50, 210, false, false)) as a edges_line as b where
a.edgeid=b.gid) 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)
[Tue Jun 12 07:17:54 2012].365626 msPostGISLayerWhichShapes query status:
PGRES_FATAL_ERROR (7)
[Tue Jun 12 07:17:54 2012].365707 Error (msPostGISLayerWhichShapes()
) executing query: ERROR:  syntax error at or near "edges_line"
LINE 1: ...st FROM edges_line', 50, 210, false, false)) as a edges_line...
                                                             ^
[Tue Jun 12 07:17:54 2012].365759 msPostGISLayerWhichShapes(): Query error.
Error executing query: ERROR:  syntax error at or near "edges_line"
LINE 1: ...st FROM edges_line', 50, 210, false, false)) as a edges_line...
                                                             ^

[Tue Jun 12 07:17:54 2012].365881 msPostGISLayerFreeItemInfo called.
[Tue Jun 12 07:17:54 2012].365925 msPostGISLayerClose called: 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
edges_line', 50, 210, false, false)) as a edges_line as b where
a.edgeid=b.gid) AS foo using unique gid using srid=-1
[Tue Jun 12 07:17:54 2012].365975 msConnPoolRelease(route,user=postgres
password=************ dbname=VSDelhidb host=localhost,0x896c410)
[Tue Jun 12 07:17:54 2012].366021 msConnPoolClose(user=postgres
password=************ dbname=VSDelhidb host=localhost,0x896c410)
[Tue Jun 12 07:17:54 2012].366792 msDrawMap(): Image handling error. Failed
to draw layer named 'route'.
[Tue Jun 12 07:17:54 2012].368660 msPostGISLayerClose called: the_geom from
edges_line
[Tue Jun 12 07:17:54 2012].368737 msPostGISLayerClose called: the_geom from
allfeatureslabel_point
[Tue Jun 12 07:17:54 2012].368776 msPostGISLayerClose called: the_geom from
namesofroads_text
[Tue Jun 12 07:17:54 2012].368814 msPostGISLayerClose called: the_geom from
edges_arc
[Tue Jun 12 07:17:54 2012].368852 msPostGISLayerClose called: 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
edges_line', 50, 210, false, false)) as a edges_line as b where
a.edgeid=b.gid) AS foo using unique gid using srid=-1
[Tue Jun 12 07:17:54 2012].370944 freeLayer(): freeing layer at 0x88e04e8.
[Tue Jun 12 07:17:54 2012].371022 msPostGISLayerIsOpen called.
[Tue Jun 12 07:17:54 2012].371067 freeLayer(): freeing layer at 0x88e12e8.
[Tue Jun 12 07:17:54 2012].371099 msPostGISLayerIsOpen called.
[Tue Jun 12 07:17:54 2012].371136 freeLayer(): freeing layer at 0x88e2220.
[Tue Jun 12 07:17:54 2012].371167 msPostGISLayerIsOpen called.
[Tue Jun 12 07:17:54 2012].371207 freeLayer(): freeing layer at 0x88e30d8.
[Tue Jun 12 07:17:54 2012].371239 msPostGISLayerIsOpen called.
[Tue Jun 12 07:17:54 2012].371285 freeLayer(): freeing layer at 0x88e3fb8.
[Tue Jun 12 07:17:54 2012].371317 msPostGISLayerIsOpen called.


Another thing I think worth mentioning is that I haven't done anything
special with my database to make pgrouting work on it. I have only enabled
the pgrouting basic functions to the database but haven't created any index
or anything of that sort. Actually the documentation for pgrouting is so
poor that I'm not even able to understand where to look for a problem. I
just need to be able to route from node 'a' to node 'b' in my road network.
The name of the network is 'edges_line' as you can see. Can you mention the
query you would use to route from node a to b in this table?  I can try and
run that code and then modify it according to my need. Thank you!
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20120612/85cb2741/attachment.htm>