1

I get results using pgRouting I cannot explain. I created a topology using osm2pgrouting. I then want to find the shortest way through this topology, for someone walking on foot. To calculate the costs I wrote a function. This function works, sort of.

CREATE OR REPLACE FUNCTION costs_foot(valueHighway text, costs double 
RETURNS double precision AS
$$
SELECT CASE 
    WHEN $1 IN ('motorway', 'motorway_link', 'motorway_junction', 'trunk', 'trunk_link', 'primary', 'primary_link', 'secondary', 'tertiary', 'bus_guideway', 'cycleway', 'byway', 'secondary_link', 'tertiary_link', 'lane', 'opposite_lane', 'opposite', 'road', 'unclassified', 'grade1', 'grade2', 'grade3', 'grade4', 'grade5')
    THEN -1 -- lines in question
    ELSE costs -- lines in question
    END
$$
language 'sql';

And then I calculate the route

CREATE TABLE route_foot AS
SELECT route.*, w.the_geom, w.length_m FROM pgr_dijkstra('
    SELECT gid AS id,
         source,
         target,
     costs_foot(str_name, cost_s) AS cost,
         reverse_cost_s AS reverse_cost
    FROM ways',
    pgr_pointToEdgeNode('ways', ST_SetSRID(
                    ST_Point(-73.930397, 40.783351), 
                    4326), 0.01
            ), 
    pgr_pointToEdgeNode('ways', ST_SetSRID(
                    ST_Point(-73.882022, 40.852214), 
                    4326), 0.01),
    directed := false) AS route
LEFT JOIN ways w
ON route.edge = w.gid
ORDER BY seq;

That gives me route which goes over highways, motorways, ... basically everything I am trying to avoid with -1.

However, when I change the function to this:

CREATE OR REPLACE FUNCTION costs_foot(valueHighway text, costs double 
RETURNS double precision AS
$$
SELECT CASE 
    WHEN $1 IN ('motorway', 'motorway_link', 'motorway_junction', 'trunk', 'trunk_link', 'primary', 'primary_link', 'secondary', 'tertiary', 'bus_guideway', 'cycleway', 'byway', 'secondary_link', 'tertiary_link', 'lane', 'opposite_lane', 'opposite', 'road', 'unclassified', 'grade1', 'grade2', 'grade3', 'grade4', 'grade5')
    THEN costs -- lines in question
    ELSE -1 -- lines in question
    END
$$
language 'sql';

I get a route that only choses footways, pedestrians, ... Looking at the logic in the SQL Function it should be the other way around.

Why is that?

1

I will start my answer from osm data.

For roads that are not one way (tag: one_way NOT IN (-1, 1), osm2pgrouting considers them as 2 way:

  • Sometimes are real 2 way roads,
  • sometimes are unknown.
  • sometimes are roads that on certain times go on one direction and in other times are in the other direction.

In all of the non one way cases:

  • cost gets a positive value
  • reverse_cost gets a positive value

For roads that are one way:

  • one_way = 1 means that the geometry has the direction of the road.
    • cost gets a positive value
    • reverse_cost gets a negative value
  • one_way = -1 means that the geometry is reversed so it has the opposite direction of the road.
    • cost gets a negative value
    • reverse_cost gets a positive value But all this is routing cars.

From the problem I figure that pedestrians are to be routed and not cars. Most of the time pedestrians can go both ways, (exceptions might be escalators.) And some kind of roads might be forbidden for the pedestrian. So, if the application is only for pedestrians a configuration file that discards all roads where a pedestrian is forbidden might be:

https://github.com/cvvergara/osm2pgrouting/blob/v3/develop/mapconfig_for_pedestrian.xml

Note: under development as I am still reading all osm tags to see what else applies.

That configuration will not add the motorways, or the motorway links, and it will be easier to test if some edges are disconnected (maybe not all the area of interest has being mapped for a pedestrian).

From the problem I see all the data is already imported.

CREATE TABLE route_foot AS 
-- (1 starts)
SELECT route.*, w.the_geom, w.length_m FROM pgr_dijkstra($$
-- (1 ends)
    SELECT gid AS id,
         source,
         target,
         -- no need of reverse cost because it is going to be undirected
         -- also no need of the function "costs_foot"
         length_m as cost
    FROM ways
    WHERE str_name NOT IN ('motorway', 'motorway_link', 'etc') -- discard the forbidden roads
    $$,  
    pgr_pointToEdgeNode('ways', ST_SetSRID(
                    ST_Point(-73.930397, 40.783351), 
                    4326), 0.01
            ), 
    pgr_pointToEdgeNode('ways', ST_SetSRID(
                    ST_Point(-73.882022, 40.852214),
                    4326), 0.01),
    directed := false) AS route 
LEFT JOIN ways w    
ON route.edge = w.gid
ORDER BY seq;

Note: pgr_pointToEdgeNode has a warning in the documentation where it states: "If no edge is within tol distance then return -1" There is no check done on this query about this warning.

FYI: pgr_pointToEdgeNode's code is based on a geometry column named "the_geom", if the function is used using other importer that for example, uses "geom" as the column fo the geometry, the function will not work.

Note: pgr_pointToEdgeNode returns an integer, so make sure the id values are not larger than +2147483647

other note: if planning to use arrows on the resulting geometry the line marked on the previous query changes (I did not test the change, just to give an idea):

-- (1 starts)
SELECT route.*,
    CASE
    WHEN route.node = w.source
        THEN w.the_geom
        ELSE ST_Reverse(w.the_geom)
    END AS the_geom
w.length_m FROM pgr_dijkstra($$
-- (1 ends)

Note: to clarify negative cost (like -1) will not add the edge for the calculation of the route, the edge does not exists, think of it as a law abiding citizen never using a forbidden road. A positive big value will add the edge and it will be used to calculate the route, think of it as not forbidden (the edge exists) but highly penalized if used, the citizen gets a big economic fine :)

Note: the big value on the costs might be useful for the functions that I haven't rewritten as they still have issues building the graphs correctly.

Note: based on your query you might want to use pgr_withPoints, but that will need to get the edge and fraction

  • One more Questions: Lets say I use this config for pedestrians: github.com/cvvergara/osm2pgrouting/blob/v3/develop/…. Would I still need to do this WHERE str_name NOT IN ('motorway', 'motorway_link', 'etc') or are these streets already excluded because they do not appear in the configfile – Stophface Jan 24 '17 at 20:34
  • They would be excluded because they do not appear on the configuration file, except if you are including other tag and belongs to some of those roads. – Vicky Mar 10 '17 at 15:38
2

The newer version of pgRouting from 2.1+ utilizes large numbers rather than negative numbers. Try changing your -1 to something big like 10000.

  • @LR1234567 is this statement correct? I hear this for the first time and it would be a bad idea to get back to high numbers instead of -1. The documentation still says about "costs": When negative: edge (source, target) does not exist, therefore it’s not part of the graph. docs.pgrouting.org/2.3/en/src/dijkstra/doc/… So the documentation must be wrong and I must have missed something ;-) – dkastl Jan 16 '17 at 7:29
  • Maybe the "large numbers" you are referring to are the use of data type "bigint", but it's not related to costs. – dkastl Jan 16 '17 at 7:31
  • @Stophface, please consider to remove the "accepted" check mark from this answer, because I think it's not a correct answer and will be misleading for others. – dkastl Jan 16 '17 at 13:58
  • @dkastl I I'm not absolutely sure. I was going to confirm with Vicky. From what I recall way back she said she was discouraging -1 because it resulted in edges being left out of the graph if you had -1 in cost and a postive number in reverse_cost. That was much earlier though before she had patched things up. So big numbers should be used instead to prevent having to remember that negatives should never be put in the cost column (okay for reverse_cost). That's from memory and my memory is not what it used to be. – LR1234567 Jan 16 '17 at 17:46
  • I also asked Vicky (who is busy preparing her travel) and didn't know about this issue. However, if there is a not-yet-patched version, it's better to fix that bug than discouraging the use of -1. – dkastl Jan 16 '17 at 19:54
2

An alternative approach is to NOT select the road segments at all!

Working with costs only makes sense in case you have different costs by direction (ie. one way streets), or if you want to make the route prefer certain road classes.

The parts of your network, that you want to exclude completely, you better don't load at all. It will also make your query faster.

So part of your function could look like this:

SELECT route.*, w.the_geom, w.length_m FROM pgr_dijkstra('
    SELECT gid AS id,
         source,
         target,
         cost,
         reverse_cost
    FROM ways
    WHERE highway IN (<list your positive road classes here>)',
    ...

That way you only route through the network, where the "highway" attribute is footways, pedestrians or what you allow in your WHERE clause.

Small note: in your function you apply the -1 costs only for cost and not reverse_cost, so your route might use the wrong road when reverse_cost applies.

  • Thanks for the hint. I created the topology with osm2pgrouting which should result in the right costs for reverse_cost. So one-way-streets are considered. Thanks for pointing out that another solution would be not to consider roads I dont want to route over. That might result (in my case) in routs, which are not "solvable". I rather assign costs to certain road types. Doing that, the algorithm tries to avoid the roads with high costs. However, when there is no other road, it still can "solve" the routing request rather than not giving an results. Again, for my case, that is ok! – Stophface Jan 16 '17 at 11:47
  • You're right, that with high costs you still leave a last option if there is no other way. But with -1 you don't. It will be excluded, but after it is loaded once. Eventually you get strange results, because you exclude so much, that you may get disconnected "islands" in your network. You're wrong, that in your case reverse_cost is handled correctly by osm2pgrouting, because you apply a filter function to cost, which you don't apply to reverse_cost. So reverse_cost will not change while cost will exclude the use of many edges in direction source->target. – dkastl Jan 16 '17 at 13:56
  • I am having trouble to follow. Why would I need to apply the filter I am applying to cost to reverse_cost_s? The way I understood is that in reverse_cost(_s) are information stored wether its a one way street or not. Depending on the direction I am moving through the network, the negativ values signify "one-way-street in that direction". Further, what do you mean with So reverse_cost will not change while cost will exclude the use of many edges in direction source->target. – Stophface Jan 16 '17 at 15:23
  • Having a look at this: workshop.pgrouting.org/2.1.0-dev/en/chapters/… the reverse_cost is "taken as it is", while there is manipulation in cost. In the function I wrote, cost is either cost (which equals to its original value which either is <0 or >0) or -1. That should indicate one-way streets correctly? – Stophface Jan 16 '17 at 15:31
  • cost is the cost traveling from from source to target, and reverse_cost is the cost from target to source. In the workshop examples I could only find both of them modified the same way. The AS in cost AS cost is used as ALIAS, see tutorialspoint.com/postgresql/postgresql_alias_syntax.htm – dkastl Jan 16 '17 at 19:27

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.