1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
|
/* :file: This file is part of the pgRouting project.
:copyright: Copyright (c) 2017-2026 pgRouting developers
:license: Creative Commons Attribution-Share Alike 3.0 https://creativecommons.org/licenses/by-sa/3.0 */
-- TODO move to pgtap
DROP TABLE IF EXISTS edges1;
SELECT id, source, target, cost, reverse_cost, x1, x2, y1, y2, geom
INTO edges1
FROM edges ORDER BY id;
DROP TABLE IF EXISTS edges1_vertices_pgr;
SELECT *
INTO edges1_vertices_pgr
FROM vertices ORDER BY id;
-- this is a hand made test
UPDATE edges1 SET id = id * 100, source = 1000 * source, target = 1000 * target;
UPDATE edges1_vertices_pgr SET id = id * 1000;
DROP TABLE IF EXISTS result2;
SELECT * INTO result2 FROM pgr_lineGraphFull(
$$SELECT id, source, target, cost, reverse_cost
FROM edges1$$
);
SELECT * FROM result2;
DROP TABLE IF EXISTS result2_vertices_pgr;
WITH foo AS (SELECT source AS id FROM result2
UNION
SELECT target FROM result2)
SELECT *, NULL::BIGINT AS original_id
INTO result2_vertices_pgr
FROM foo
ORDER BY id;
SELECT * FROM result2_vertices_pgr;
UPDATE result2_vertices_pgr AS r SET original_id = v.id
FROM edges1_vertices_pgr AS v WHERE v.id = r.id;
WITH a AS (SELECT e.id, e.original_id FROM result2_vertices_pgr AS e WHERE original_id IS NOT NULL),
b AS (SELECT * FROM result2 WHERE cost = 0 and source IN (SELECT id FROM a)),
c AS (SELECT * FROM b JOIN result2_vertices_pgr ON(source = id)),
d AS (SELECT c.source, v.original_id FROM c JOIN result2_vertices_pgr as v ON (target=v.id)),
e AS (SELECT DISTINCT c.target, c.original_id FROM c JOIN result2_vertices_pgr AS r ON(target = r.id AND r.original_id IS NULL))
UPDATE result2_vertices_pgr SET original_id = e.original_id FROM e WHERE e.target = id;
WITH a AS (SELECT e.id, e.original_id FROM result2_vertices_pgr AS e WHERE original_id IS NOT NULL),
b AS (SELECT * FROM result2 WHERE cost = 0 and target IN (SELECT id FROM a)),
c AS (SELECT * FROM b JOIN result2_vertices_pgr ON(target = id)),
d AS (SELECT c.target, v.original_id FROM c JOIN result2_vertices_pgr as v ON (source=v.id)),
e AS (SELECT DISTINCT c.source, c.original_id FROM c JOIN result2_vertices_pgr AS r ON(source = r.id AND r.original_id IS NULL))
UPDATE result2_vertices_pgr SET original_id = e.original_id FROM e WHERE e.source = id;
WITH a AS (SELECT id FROM result2_vertices_pgr WHERE original_id IS NULL),
b AS (SELECT source,edge FROM result2 WHERE source IN (SELECT id FROM a)),
c AS (SELECT id,source FROM edges1 WHERE id IN (SELECT edge FROM b))
UPDATE result2_vertices_pgr AS d SET original_id = (SELECT source FROM c WHERE c.id = (SELECT edge FROM b WHERE b.source = d.id)) WHERE id IN (SELECT id FROM a);
WITH a AS (SELECT id FROM result2_vertices_pgr WHERE original_id IS NULL),
b AS (SELECT target,edge FROM result2 WHERE target IN (SELECT id FROM a)),
c AS (SELECT id,target FROM edges1 WHERE id IN (SELECT edge FROM b))
UPDATE result2_vertices_pgr AS d SET original_id = (SELECT target FROM c WHERE c.id = (SELECT edge FROM b WHERE b.target = d.id)) WHERE id IN (SELECT id FROM a);
SELECT * FROM result2_vertices_pgr;
ALTER TABLE result2 ADD COLUMN original_source_vertex BIGINT;
ALTER TABLE result2 ADD COLUMN original_target_vertex BIGINT;
ALTER TABLE result2 ADD COLUMN original_source_edge BIGINT;
ALTER TABLE result2 ADD COLUMN original_target_edge BIGINT;
-- restoring the original_foo_vertex
UPDATE result2 AS edges SET original_source_vertex = vertices.original_id
FROM result2_vertices_pgr AS vertices WHERE edges.source = vertices.id;
UPDATE result2 AS edges SET original_target_vertex = vertices.original_id
FROM result2_vertices_pgr AS vertices WHERE edges.target = vertices.id;
-- restoring the original_foo_edges
UPDATE result2
SET original_source_edge = edge,
original_target_edge = edge
WHERE edge != 0;
UPDATE result2 AS a
SET original_source_edge = b.edge
FROM result2 AS b
WHERE
a.original_source_edge IS NULL AND
b.original_source_edge IS NOT NULL AND
a.source = b.target;
UPDATE result2 AS a
SET original_target_edge = b.edge
FROM result2 AS b
WHERE
a.original_target_edge IS NULL AND
b.original_target_edge IS NOT NULL AND
a.target = b.source;
-- all the results
DROP TABLE IF EXISTS q_result_1;
SELECT * INTO q_result_1
FROM pgr_dijkstra($$SELECT seq AS id, * FROM result2$$,
(SELECT array_agg(id) FROM result2_vertices_pgr where original_id = 6000),
(SELECT array_agg(id) FROM result2_vertices_pgr where original_id = 10000)
);
SELECT * FROM q_result_1;
-- choosing the best many to many
SELECT min(agg_cost) FROM q_result_1 WHERE edge = -1;
SELECT path_seq, start_vid, end_vid FROM q_result_1 WHERE edge = -1 and agg_cost = 5;
SELECT * FROM q_result_1 WHERE start_vid = -3 AND end_vid = 10000;
-- not showing the 0 cost edges
SELECT * FROM q_result_1 WHERE start_vid = -3 AND end_vid = 10000 AND (cost != 0 OR edge = -1);
-- not showing the 0 cost edges and showing the original edges
WITH a AS (
SELECT * FROM q_result_1
WHERE start_vid = -3 AND end_vid = 10000 AND (cost != 0 OR edge = -1))
SELECT
a.seq, a.path_seq,
(SELECT original_id FROM result2_vertices_pgr AS b WHERE a.start_vid = b.id) AS start_vid,
(SELECT original_id FROM result2_vertices_pgr AS b WHERE a.end_vid = b.id) AS end_vid,
(SELECT original_id FROM result2_vertices_pgr AS b WHERE a.node = b.id) AS node,
edge, cost, agg_cost
FROM a;
-- A Dijkstra
SELECT * from pgr_dijkstra($$SELECT id, * FROM edges1$$,
ARRAY[6000], ARRAY[10000]);
|
