Using PgRouting Topology?

Question

I have a data set of roads which is line and its projection system is WGS UTM (EPSG:32639) I have added it into my database and created a topology using this query

SELECT pgr_createTopology('ways', 0.00001, 'geom_2d', 'gid',rows_where := 'gid>0', clean := TRUE);

The above query works fine. But When I try to route using any alogorithm such as this query

    SELECT id1 as path,b.geom as the_geom
FROM pgr_dijkstra('SELECT gid as id, source, target, st_length(geom_2d) as cost FROM ways',
                           483,507,FALSE ,FALSE) a,
ways b
WHERE a.id1=b.gid

It returns some lines which are not a route at all. Here is an example

In above image the yellow lines is my edges, points are vertexes which are created by pgr_createTopology and blue lines is the result of routing I have tested different values of tolerance such as 1,10,100,0.00001,0.001 and for some of them there is no result and for some f them the result is exactly the same, I think there is something wrong with layers topology but I can not figure out how to fix it.

Can you help me to fix my problem?

edit 1: here is the result of this query

SELECT * FROM "ways" WHERE gid=483 or gid=507

Answer 1

Using pgr_dijkstra():

1. locate the ways.gid of the start road

   assume the ways.gid = 483

2. locate the ways.gid of the end road

   assume the ways.gid = 507

3. locate the vertex of the start road that you want to start at (can be source or target)

   assume the ways.target = 1234

4. locate the vertex of the end road that you want to end at (can be source or target)

   assume the ways.target = 5678

5. your simple statement will look like this:

   SELECT * FROM pgr_dijkstra('SELECT gid, source, target, cost FROM ways', 1234,5678,FALSE ,FALSE);

6. if you have a large dataset like mine (all of north america), then you'll want to filter with a bounding box using the ids of the road

   SELECT * FROM pgr_dijkstra('SELECT id, source, target, cost FROM ways, (SELECT ST_Expand(ST_Extent(geom),0.01) as geombox FROM ways as filteredways WHERE filteredways.id=483 or filteredways.id=488) as box WHERE ways.geom_way && box.geombox' ,1234,5678,FALSE);

7. If you want to get back the total distance, then you can join back on the ways table

   SELECT SUM(km) FROM pgr_dijkstra('SELECT id, source, target, cost FROM ways, (SELECT ST_Expand(ST_Extent(geom),0.01) as geombox FROM ways as filteredways WHERE filteredways.id=483 or filteredways.id=488) as box WHERE ways.geom_way && box.geombox' ,1234,5678,FALSE) as r LEFT JOIN public.na_2po_4pgr as w ON r.edge = w.id;

Answer 2

First hint:

I don´t think that your cost should be the length of the edges. It is a value that means minutes needed to travel that edge. With the length as value it means you need 100minutes for a 100m way. Thats no problem for Dikstra when units don´t matter but should be considered when you want to evaluate it.

Second:

You are joining the outcome of the pgr_dijkstra with your original ways. The function is meant to join with your nodes layer. That way you add the geometry of your ways with the id that is actually meant for the nodes of your route. Thats why you get random lines as these are the lines with the id of your route-nodes. Just alter the join from "ways" to the layer of your nodes. The outcome is a pointlayer which sequence you can use to plot your route.