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

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 ;-) Jan 16, 2017 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. Jan 16, 2017 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. Jan 16, 2017 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. Jan 16, 2017 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`. Jan 16, 2017 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
...
``````

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! Jan 16, 2017 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. Jan 16, 2017 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. Jan 16, 2017 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? Jan 16, 2017 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 Jan 16, 2017 at 19:27

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` Jan 24, 2017 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. Mar 10, 2017 at 15:38