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
130
131
132
|
-- CopyRight(c) pgRouting developers
-- Creative Commons Attribution-Share Alike 3.0 License : https://creativecommons.org/licenses/by-sa/3.0/
SET extra_float_digits=-3;
/* -- q0 */
SELECT * FROM pgr_trspVia_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
$$SELECT path, cost FROM restrictions$$,
$$SELECT pid, edge_id, side, fraction FROM pointsOfInterest$$,
ARRAY[-6, 15, -5]);
/* -- q1 */
SELECT * FROM pgr_trspVia_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
$$SELECT path, cost FROM restrictions$$,
$$SELECT pid, edge_id, side, fraction FROM pointsOfInterest$$,
ARRAY[-6, 7, -4, 8, -2]
);
/* -- q2 */
SELECT agg_cost FROM pgr_trspVia_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
$$SELECT path, cost FROM restrictions$$,
$$SELECT pid, edge_id, side, fraction FROM pointsOfInterest$$,
ARRAY[-6, 7, -4, 8, -2]
)
WHERE path_id = 3 AND edge <0;
/* -- q3 */
SELECT route_agg_cost FROM pgr_trspVia_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
$$SELECT path, cost FROM restrictions$$,
$$SELECT pid, edge_id, side, fraction FROM pointsOfInterest$$,
ARRAY[-6, 7, -4, 8, -2]
)
WHERE path_id = 3 AND edge < 0;
/* -- q4 */
SELECT row_number() over () as node_seq, node
FROM pgr_trspVia_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
$$SELECT path, cost FROM restrictions$$,
$$SELECT pid, edge_id, side, fraction FROM pointsOfInterest$$,
ARRAY[-6, 7, -4, 8, -2]
)
WHERE edge <> -1 ORDER BY seq;
/* -- q5 */
SELECT path_id, route_agg_cost FROM pgr_trspVia_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
$$SELECT path, cost FROM restrictions$$,
$$SELECT pid, edge_id, side, fraction FROM pointsOfInterest$$,
ARRAY[-6, 7, -4, 8, -2]
)
WHERE edge < 0;
/* -- q6 */
SELECT seq, route_agg_cost, node, agg_cost ,
CASE WHEN edge = -1 THEN $$visits$$
ELSE $$passes in front$$
END as status
FROM pgr_trspVia_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
$$SELECT path, cost FROM restrictions$$,
$$SELECT pid, edge_id, side, fraction FROM pointsOfInterest$$,
ARRAY[-6, 7, -4, 8, -2])
WHERE agg_cost <> 0 or seq = 1;
/* -- q7 */
SELECT * FROM pgr_withPointsVia(
$$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
$$SELECT pid, edge_id, side, fraction FROM pointsOfInterest$$,
ARRAY[-6, 15, -5]);
/* -- q8 */
SELECT 1 AS path_id, path_seq, start_vid, end_vid, node, edge, cost, agg_cost
FROM pgr_trsp_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT path, cost FROM restrictions$$,
$$SELECT pid, edge_id, side, fraction FROM pointsOfInterest$$,
-6, 15);
/* -- q9 */
WITH
solutions AS (
SELECT path_id, path_seq, start_vid, end_vid, node, edge, cost, agg_cost
FROM pgr_withPointsVia(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT pid, edge_id, side, fraction FROM pointsOfInterest$$,
ARRAY[-6, 15, -5]) WHERE path_id != 1
UNION
SELECT 1 AS path_id, path_seq, start_vid, end_vid, node, edge, cost, agg_cost
FROM pgr_trsp_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT path, cost FROM restrictions$$,
$$SELECT pid, edge_id, side, fraction FROM pointsOfInterest$$,
-6, 15)),
with_seq AS (
SELECT row_number() over(ORDER BY path_id, path_seq) AS seq, *
FROM solutions),
aggregation AS (SELECT seq, SUM(cost) OVER(ORDER BY seq) AS route_agg_cost FROM with_seq)
SELECT with_seq.*, COALESCE(route_agg_cost, 0) AS route_agg_cost
FROM with_seq LEFT JOIN aggregation ON (with_seq.seq = aggregation.seq + 1);
/* -- q10 */
SELECT * FROM pgr_trspVia_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
$$SELECT path, cost FROM restrictions$$,
$$SELECT pid, edge_id, side, fraction FROM pointsOfInterest$$,
ARRAY[-6, 15, -5]);
/* -- q11 */
SELECT * FROM pgr_withPointsVia(
$$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
$$SELECT pid, edge_id, side, fraction FROM pointsOfInterest$$,
ARRAY[6, 7, 6], U_turn_on_edge => false);
/* -- q12 */
SELECT 5 AS path_id, path_seq, start_vid, end_vid, node, edge, cost, agg_cost
FROM pgr_trsp_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT path, cost FROM restrictions$$,
$$SELECT pid, edge_id, side, fraction FROM pointsOfInterest$$,
7, 6);
/* -- q13 */
SELECT * FROM pgr_trspVia_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
$$SELECT path, cost FROM restrictions$$,
$$SELECT pid, edge_id, side, fraction FROM pointsOfInterest$$,
ARRAY[6, 7, 6], U_turn_on_edge => false);
/* -- q14 */
SELECT * FROM pgr_trspVia_withPoints(
$e$ SELECT * FROM edges $e$,
$r$ SELECT path, cost FROM restrictions $r$,
$p$ SELECT edge_id, round(fraction::numeric, 2) AS fraction, side
FROM pgr_findCloseEdges(
$$SELECT id, geom FROM edges$$,
(SELECT ST_POINT(2.9, 1.8)),
0.5, cap => 2)
$p$,
ARRAY[1, -1, -2], details => true);
/* -- q15 */
|
