Strange results in pgRouting

Question

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?

Answer 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

Answer 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.

Answer 3

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.