Strange results in pgRouting

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
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
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?

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
\$\$,
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

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

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