1

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

enter image description here

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

enter image description here

8
  • Please provide the result of: SELECT * FROM ways WHERE gid=483 or gid=507; and provide the result of: SELECT * FROM ways_vertices_pgr WHERE gid=483 or gid=507; – kttii Sep 7 '16 at 16:43
  • @kttii thanks my friend. I have ran "SELECT * FROM "ways" WHERE gid=483 or gid=507" and it returns two rows.I am attaching my results to my question – Majid Hojati Sep 7 '16 at 16:46
  • @kttii You know my queries for routing return some results but It seems that resulting lines are not complate. In the first image you can see some results that are shown .. – Majid Hojati Sep 7 '16 at 16:50
  • looks like some columns got cut off. – kttii Sep 7 '16 at 16:50
  • yes, I ran into the exact same problem and thought that my topology didn't get built correctly but it really had to do with picking the correct IDs. Are there columns called SourceID and DestID in the ways table? – kttii Sep 7 '16 at 16:51
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;

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.

7
  • Dear Matte, I will consider your note about costs, But about my problem you said I have missed start and end point. But this is start and end point (483,507) in second query,Can you please explain a little more?Also I think that the step of calculation of nodes from each segment is done by pgr_createtopology function,isnt it? – Majid Hojati Sep 7 '16 at 10:39
  • You are correct, i just miss interpreted your select statement and mixed up some comema. Edited the answer as i just run your statement with my network and found the mistake. – Matte Sep 7 '16 at 11:10
  • thanks for your help,I think there is something wrong with ,my topology,by the way I can easily route with ARCGIS,But I have this problem with pgrouting, Do you know how can I check for topology of network?Or find the best value for tolerance ? – Majid Hojati Sep 7 '16 at 11:34
  • Well ArcGIS has lots of "convenience functions" where it assumes a lot and calculates stuff in the background(don´t know what it does at the network manager though). Or puts the needed entries in a well sorted dialog. You need in general more time for many open source tools to get around with and they don´t want to calculate stuff without the user explicitly calling it(and understanding it in the first place). – Matte Sep 7 '16 at 11:46
  • Yes I agree, anyway I don't know where is problem with my network. Finding the problem is half of solving it :(( – Majid Hojati Sep 7 '16 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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