2

I created a topology from OSM data with osm2pgrouting.

I use a very simple Query

SELECT * FROM pgr_dijkstra('
    SELECT gid AS id,
         source,
         target,
         cost_s AS cost,
         reverse_cost_s AS reverse_cost
        FROM ways',
    13224, 9224, directed := true);

Which works fine. However, I am also interested in the length of the segments.

The ways table has a column length_m so I though I add this columns to the nested SELECT

SELECT * FROM pgr_dijkstra('
    SELECT gid AS id,
         source,
         target,
         length_m,
         cost_s AS cost,
         reverse_cost_s AS reverse_cost
        FROM ways',
    13224, 9224, directed := true);

However, the resulting table does not contain a column with the length of the segment.

Assuming I imported OSM data with osm2pgrouting. Would a join with the ways table gives me the desired output?

SELECT * FROM pgr_dijkstra('
    SELECT gid AS id,
         source,
         target,
         length_m,
         cost_s AS cost,
         reverse_cost_s AS reverse_cost
        FROM ways',
    13224, 9224, directed := true) AS route
INNER JOIN ways w
ON route.node = w.gid;

Two question: Is the join the right way to get the desired information (length of the routing segments).

And: I wonder why I cannot select the stuff within the pgr_dijkstra()?

*edit: The proper way to join the tables should be

SELECT * FROM pgr_dijkstra('
    SELECT gid AS id,
         source,
         target,
         length_m,
         cost_s AS cost,
         reverse_cost_s AS reverse_cost
        FROM ways',
    13224, 9224, directed := true) AS route
LEFT JOIN ways w
ON route.edge = w.gid;
4

pgr_dijkstra(edges_sql, start_vid, end_vid, directed:=true ) is a SQL function. It returns the following, regardless of what you input into its TEXT edges_sql argument.

RETURNS SET OF (seq, path_seq, node, edge, cost, agg_cost)

To answer your first question: yes, joining will be the only way you can get information from an external table to be added to the results.

  • okay. Assuming I imported my network with osm2pgrouting. The tables this framework creates are all the same. Would my join be ok? – Stophface Dec 15 '16 at 21:53
  • yes, your join is ok. – kttii Dec 15 '16 at 22:16
  • @kttii I think the join was wrong. I edited my Q. Further: Is the unit of the cost_s column, created through osm2pgrouting, seconds? What unit is the cost column (also created through osm2pgrouting) then? I cannot find anything in the docs on that. – Stophface Dec 16 '16 at 8:40
  • Would you be so kind as to post a new question please? – kttii Dec 16 '16 at 14:46
  • @Stophface basically though, cost is derived from length divided by speed (or km / kph) – kttii Dec 16 '16 at 15:29

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.