Adding direction to undirected graph

Question

What I'm trying to do is traverse a known path in a given direction for a given distance.

What I have done so far is build a temp table of the path in the order I want to travel and it looks like:

edge_id	source	target	cost	geom
208346	167398	50564	148.55814507696616	""
118186	167397	167398	18.917451445786348	""
118185	167397	97888	5.577629837679091	""
58802	97888	97889	87.04193787193802	""
118187	97889	167399	95.53741276977404	""

now I believe I should add a dir and reverse_cost columns and set value to something.

I'm assuming I can set the values of dir and reverse_cost based on the source and target columns, do I also need to change the value of cost maybe invert?

I can not figure out how to wright an update query to add the correct info into the table to then be able to run pgr_withPointsDD

Answer 1

I'm sure this is not the most efficient way but I have a working example that solves my question.

I update the tmp_network table with:

WITH
direction_tbl AS (
    SELECT 
        path_seq,
        source,
        LEAD(source, 1) OVER (ORDER BY path_seq) AS next_source,
        LAG(source, 1) OVER (ORDER BY path_seq) AS prev_source,
        target,
        LEAD(target, 1) OVER (ORDER BY path_seq) AS next_target,
        LAG(target, 1) OVER (ORDER BY path_seq) AS prev_target
    FROM tmp_network
)
UPDATE tmp_network
    SET cost =
    CASE
        WHEN dir.source = dir.next_source THEN -1
        WHEN dir.source = dir.next_target THEN -1
        WHEN dir.target = dir.prev_source THEN -1
        WHEN dir.target = dir.prev_target THEN -1
        ELSE tmp_network.cost
    END,
    reverse_cost =
    CASE
        WHEN dir.target = dir.next_source THEN -1
        WHEN dir.target = dir.next_target THEN -1
        WHEN dir.source = dir.prev_source THEN -1
        WHEN dir.source = dir.prev_target THEN -1
        ELSE tmp_network.reverse_cost
    END
FROM direction_tbl AS dir
WHERE dir.path_seq = tmp_network.path_seq;

and tmp_network table now looks like: | path_seq | id | source | target | cost | reverse_cost | geom | | :------: | :----: | :----: | :----: | :----: | :----: | :----: | | 1 | 208346 | 167398 | 50564 | -1 | 148.55814507696616 | 0102000020... | | 2 | 118186 | 167397 | 167398 | -1 | 18.917451445786348 | 0102000020... | | 3 | 118185 | 167397 | 97888 | 5.577629837679091 | -1 | 0102000020... | | 4 | 58802 | 97888 | 97889 | 87.04193787193802 | -1 | 0102000020... | | 5 | 118187 | 97889 | 167399 | 95.53741276977404 | -1 | 0102000020... |

and

SELECT pg.*
    FROM pgr_withPointsDD(
        'SELECT id, source, target, cost, reverse_cost FROM tmp_network ORDER BY id',
        'SELECT pid, vid AS edge_id, fraction FROM tmp_poi',
        -1,
        240.0,
        directed := true) AS pg

work as expected.