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

[Stephen Woodbridge]

On 6/11/2012 9:58 PM, Annu Anurag wrote:
> 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

THis should be:
     ... as a, edges_line ...

Note you are missing a comma.

-Steve

> 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!
>
>
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users