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

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.