How to display proper route for OSM data using pgRouting

Question

I am facing a problem while displaying routes using OSM data and pgRouting. I have followed the following procedure:

• downloaded OSM data
• made it routable using osm2pgrouting
• taking point coordinates from OpenLayers JavaScript
• querying to get the nearest point available in the data for source and target
• querying the shortest route using those source and target points
• List item
• Displaying the route

Here are my queries

For source and target selection:

select w.source, w.target, ST_Distance(w.the_geom,ST_SetSRID(ST_MakePoint(%s, %s),4326)) as d 
from ways as w 
WHERE ST_DWithin(ST_SetSRID(ST_MakePoint(%s, %s),4326), w.the_geom, 100) 
order by d

Where coordinate value go in %s

For routing query:

SELECT ST_AsGeoJSON(the_geom) AS geoj 
FROM ways 
JOIN (SELECT * FROM pgr_dijkstra(\'SELECT class_id AS id, source, target, length AS cost FROM ways\', %s, %s, directed:=TRUE)) AS route ON ways.gid = route.edge

The source and target obtained previously are given here in %s

The problem is that I am not getting the correct route I am getting segments fragmented and scattered here and there on the map.

How can I correct it?

the two points close to each other are the source and destination and the green fragments is the route obtained

the two points close to each other are the source and destination and the green fragments is the route obtained

• John Powell: It's not really clear from the question what the route should be and which are the start and end points, though from the first query it looks like the will be two points very close together.
• daas-shukla: the two blue points which are near the JVLR tag in the map are the start and end points. the route should be between the two points
• John Powell: It is best to edit the question with that information. Are you sure you have a complete and correct road network?

Answer 1

It looks like there is a problem with your edge id reference.

In the pgr_dijkstra query, you are using the class_id column from your ways table as your edge id. The edge column that is returned by pgr_dijkstra will match the class_id column.

Then, you join the result back to the ways table (to get the geometry) based on the gid column.

ON ways.gid = route.edge

This will only work if gid is the same as class_id. You probably want to use gid in place of class_id in the pgr_dijkstra query, or class_id in place of gid in the join.