5

I have set up Postgresql, PostGIS and pgrouting on Ubuntu. I have imported an osm for wales using the following command:

./osm2pgrouting -file wales.osm \
                            -conf mapconfig.xml \
                            -dbname routing \
                            -user postgres \
                            -clean

All I now want to do is execute something to give me the fastest route between 2 Latitude/Longitude points.

Start: 51.499954,-3.571651
End: 53.386809,-4.445

The route has to be in the format of a series of latitude/longitude positions that make up the route.

Is this even possible? If not, what would it take to make it possible.

If it is possible, what sql do I need to execute?

I have been looking everywhere for an answer to no avail.

Further to Steve Horn's recommendation. I have now managed to get 5000 points coming back using this sql:

SELECT gid, AsText(the_geom) AS the_geom FROM dijkstra_sp('ways', 15522, 26857);

However, the points all seem to be ignoring the road type. (When I run it using Google Directions, the route mainly follows the main roads). What have I done wrong?

3 Answers 3

4

There is a great walkthrough of using the shortest_path() and shortest_path_astar() methods here: http://workshop.pgrouting.org/chapters/shortest_path.html

These methods rely on parameters representing vertex Ids from your table that was created using osm2pgrouting. The vertex Ids are the source and target columns in the ways table.

To find the closest vertex to your lat/lng pair, you can use find_nearest_node_within_distance from the script found here: https://github.com/pgRouting/pgrouting/blob/master/core/sql/matching.sql

1
2

I am using the following function to do that:

CREATE or REPLACE FUNCTION get_nearest_vertex_to_lon_lat(float,float) RETURNS  integer AS $$
select id from ways_vertices_pgr as vertices 
order by  vertices.the_geom <-> ST_SetSrid(ST_MakePoint($1,$2),4326) LIMIT 1;
$$ LANGUAGE SQL;

The db generated by osm2pgrouting has bigint as the type of the id in ways_vertices_pgr so you can also turn this into an integer so that the algorithms needing integer source/target vertices do not complain. (not sure why this happens (ie, why the routing algorithms need int), and what one is supposed to do if the number of vertices exceed maxint). Anyway, you can convert the id to integer by running this:

ALTER TABLE ways_vertices_pgr ALTER COLUMN id TYPE integer; 
0

However, the points all seem to be ignoring the road type. (When I run it using Google Directions, the route mainly follows the main roads). What have I done wrong?

You need to define some "cost" for every vertex into "ways" table ( i think, its a while when i used pgrouting). Good cost could be something like travel time (length / speedlimit)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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