I am using pgRouting version 2.6.
I am creating a routing topology from a subset of lion data https://www1.nyc.gov/site/planning/data-maps/open-data/dwn-lion.page
I have gone through the process of setting up the network and I am able to do some basic shortest path routing using the pgr_dijkstra function.
However I am running into the routing issue of overpasses/bridges/tunnels where the network is not aware of the grade separations or heights of the roads that are intersecting. As a few resources on the web have mentioned this issue
- How to create a routable road network from Ordnance Survey data?
- Create a network topology for pgrouting with grade separation
These questions and answers have helped me contextualize my problem but the solutions they came up with do no really apply to the dataset I am using.
I have already figured out the logic on what I need to do but I do not know how to implement it into either the network topology creation or the routing functions
dijkstra AS (
FROM pgr_dijkstra('SELECT id, source, target, cost_drive AS cost, rcost_drive as reverse_cost
FROM edges where driveable = true',
directed := TRUE)
FROM dijkstra left JOIN edges ON (edge = id)
ORDER BY seq
The query above routes from Staten Island to Williamsburg BK and for the most part it does a good job accurately finding the route however in the picture below the route is on the BQE and then makes an impossible left turn onto Borinquen Place.
I have been able to identify when a turn or route should not be allowed.
In the data there are two columns that deal with gradient. nodelevelf (Level code indicator vertical topology at the start of the street segment. goes from A-Z where z is the highest level) and nodelevelt (Level code indicator vertical topology at the end of the street segment. goes from A-Z where z is the highest level)
Two segments should ONLY be routed together if the nodelevelt of the 1st segment equals the nodelevelf of the 2nd segment.
In the above example the BQE segment (highlighted in red) has a gradient of I for both its nodelevelf and nodelevelt and the next segment which is a left turn onto Borinquen Place has a nodelevelf and nodelevelt of M which is a level higher than the previous BQE segment. This should not happen.
How do I implement such a rule in the routing?
Things I have tried:
- pre-processing the data to merge streets with similar attributes but this creates multilinestrings which does not work in the pgrouting functions
- run the above SQL query to generate the route then identify the wrong turn link and delete that node from the vertices table (abandoned that idea once other routes were not working because of it)