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
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
|
BEGIN;
BEGIN
SET client_min_messages TO NOTICE;
SET
/* :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;
NOTICE: table "edges1" does not exist, skipping
DROP TABLE
SELECT id, source, target, cost, reverse_cost, x1, x2, y1, y2, geom
INTO edges1
FROM edges ORDER BY id;
SELECT 18
DROP TABLE IF EXISTS edges1_vertices_pgr;
NOTICE: table "edges1_vertices_pgr" does not exist, skipping
DROP TABLE
SELECT *
INTO edges1_vertices_pgr
FROM vertices ORDER BY id;
SELECT 17
UPDATE edges1 SET id = id * 100, source = 1000 * source, target = 1000 * target;
UPDATE 18
UPDATE edges1_vertices_pgr SET id = id * 1000;
UPDATE 17
DROP TABLE IF EXISTS result2;
NOTICE: table "result2" does not exist, skipping
DROP TABLE
SELECT * INTO result2 FROM pgr_lineGraphFull(
$$SELECT id, source, target, cost, reverse_cost
FROM edges1$$
);
SELECT 95
SELECT * FROM result2;
seq | source | target | cost | edge
-----+--------+--------+------+-------
1 | -1 | 6000 | 1 | 100
2 | 5000 | -1 | 0 | 0
3 | -2 | 5000 | 1 | -100
4 | -3 | 7000 | 1 | 400
5 | 6000 | -2 | 0 | 0
6 | 6000 | -3 | 0 | 0
7 | -4 | -2 | 0 | 0
8 | -4 | -3 | 0 | 0
9 | -5 | -2 | 0 | 0
10 | -5 | -3 | 0 | 0
11 | -6 | -4 | 1 | -200
12 | -7 | 11000 | 1 | 500
13 | 10000 | -6 | 0 | 0
14 | 10000 | -7 | 0 | 0
15 | -8 | 10000 | 1 | -300
16 | -9 | -30 | 1 | 1600
17 | 15000 | -8 | 0 | 0
18 | 15000 | -9 | 0 | 0
19 | -10 | -5 | 1 | -400
20 | -11 | -25 | 1 | -700
21 | -12 | -20 | 1 | 800
22 | -13 | 8000 | 1 | 1000
23 | 7000 | -10 | 0 | 0
24 | 7000 | -11 | 0 | 0
25 | 7000 | -12 | 0 | 0
26 | 7000 | -13 | 0 | 0
27 | -14 | -10 | 0 | 0
28 | -14 | -11 | 0 | 0
29 | -14 | -12 | 0 | 0
30 | -14 | -13 | 0 | 0
31 | -15 | -10 | 0 | 0
32 | -15 | -11 | 0 | 0
33 | -15 | -12 | 0 | 0
34 | -15 | -13 | 0 | 0
35 | -16 | -10 | 0 | 0
36 | -16 | -11 | 0 | 0
37 | -16 | -12 | 0 | 0
38 | -16 | -13 | 0 | 0
39 | -17 | -15 | 1 | -800
40 | -18 | 16000 | 1 | 900
41 | -19 | 12000 | 1 | 1100
42 | 11000 | -17 | 0 | 0
43 | 11000 | -18 | 0 | 0
44 | 11000 | -19 | 0 | 0
45 | -20 | -17 | 0 | 0
46 | -20 | -18 | 0 | 0
47 | -20 | -19 | 0 | 0
48 | -21 | -17 | 0 | 0
49 | -21 | -18 | 0 | 0
50 | -21 | -19 | 0 | 0
51 | -22 | 3000 | 1 | 600
52 | 1000 | -22 | 0 | 0
53 | -23 | 1000 | 1 | -600
54 | -24 | -14 | 1 | 700
55 | 3000 | -23 | 0 | 0
56 | 3000 | -24 | 0 | 0
57 | -25 | -23 | 0 | 0
58 | -25 | -24 | 0 | 0
59 | -26 | -21 | 1 | -900
60 | -27 | -38 | 1 | 1500
61 | -28 | 15000 | 1 | -1600
62 | 16000 | -26 | 0 | 0
63 | 16000 | -27 | 0 | 0
64 | 16000 | -28 | 0 | 0
65 | -29 | -26 | 0 | 0
66 | -29 | -27 | 0 | 0
67 | -29 | -28 | 0 | 0
68 | -30 | -26 | 0 | 0
69 | -30 | -27 | 0 | 0
70 | -30 | -28 | 0 | 0
71 | -31 | -16 | 1 | -1000
72 | -32 | -36 | 1 | 1200
73 | -33 | 9000 | 1 | 1400
74 | 8000 | -31 | 0 | 0
75 | 8000 | -32 | 0 | 0
76 | 8000 | -33 | 0 | 0
77 | -34 | -31 | 0 | 0
78 | -34 | -32 | 0 | 0
79 | -34 | -33 | 0 | 0
80 | -35 | 17000 | 1 | 1300
81 | 12000 | -35 | 0 | 0
82 | -36 | -35 | 0 | 0
83 | -37 | -29 | 1 | -1500
84 | 17000 | -37 | 0 | 0
85 | -38 | -37 | 0 | 0
86 | -39 | -34 | 1 | -1400
87 | 9000 | -39 | 0 | 0
88 | -40 | 4000 | 1 | 1700
89 | 2000 | -40 | 0 | 0
90 | -41 | 2000 | 1 | -1700
91 | 4000 | -41 | 0 | 0
92 | -42 | 14000 | 1 | 1800
93 | 13000 | -42 | 0 | 0
94 | -43 | 13000 | 1 | -1800
95 | 14000 | -43 | 0 | 0
(95 rows)
DROP TABLE IF EXISTS result2_vertices_pgr;
NOTICE: table "result2_vertices_pgr" does not exist, skipping
DROP TABLE
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 60
SELECT * FROM result2_vertices_pgr;
id | original_id
-------+-------------
-43 |
-42 |
-41 |
-40 |
-39 |
-38 |
-37 |
-36 |
-35 |
-34 |
-33 |
-32 |
-31 |
-30 |
-29 |
-28 |
-27 |
-26 |
-25 |
-24 |
-23 |
-22 |
-21 |
-20 |
-19 |
-18 |
-17 |
-16 |
-15 |
-14 |
-13 |
-12 |
-11 |
-10 |
-9 |
-8 |
-7 |
-6 |
-5 |
-4 |
-3 |
-2 |
-1 |
1000 |
2000 |
3000 |
4000 |
5000 |
6000 |
7000 |
8000 |
9000 |
10000 |
11000 |
12000 |
13000 |
14000 |
15000 |
16000 |
17000 |
(60 rows)
UPDATE result2_vertices_pgr AS r SET original_id = v.id
FROM edges1_vertices_pgr AS v WHERE v.id = r.id;
UPDATE 17
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;
UPDATE 30
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;
UPDATE 13
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);
UPDATE 0
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);
UPDATE 0
SELECT * FROM result2_vertices_pgr;
id | original_id
-------+-------------
1000 | 1000
2000 | 2000
3000 | 3000
4000 | 4000
5000 | 5000
6000 | 6000
7000 | 7000
8000 | 8000
9000 | 9000
10000 | 10000
11000 | 11000
12000 | 12000
13000 | 13000
14000 | 14000
15000 | 15000
16000 | 16000
17000 | 17000
-43 | 14000
-42 | 13000
-41 | 4000
-40 | 2000
-39 | 9000
-37 | 17000
-35 | 12000
-33 | 8000
-32 | 8000
-31 | 8000
-28 | 16000
-27 | 16000
-26 | 16000
-24 | 3000
-23 | 3000
-22 | 1000
-19 | 11000
-18 | 11000
-17 | 11000
-13 | 7000
-12 | 7000
-11 | 7000
-10 | 7000
-9 | 15000
-8 | 15000
-7 | 10000
-6 | 10000
-3 | 6000
-2 | 6000
-1 | 5000
-38 | 17000
-36 | 12000
-34 | 8000
-30 | 16000
-29 | 16000
-25 | 3000
-21 | 11000
-20 | 11000
-16 | 7000
-15 | 7000
-14 | 7000
-5 | 6000
-4 | 6000
(60 rows)
ALTER TABLE result2 ADD COLUMN original_source_vertex BIGINT;
ALTER TABLE
ALTER TABLE result2 ADD COLUMN original_target_vertex BIGINT;
ALTER TABLE
ALTER TABLE result2 ADD COLUMN original_source_edge BIGINT;
ALTER TABLE
ALTER TABLE result2 ADD COLUMN original_target_edge BIGINT;
ALTER TABLE
UPDATE result2 AS edges SET original_source_vertex = vertices.original_id
FROM result2_vertices_pgr AS vertices WHERE edges.source = vertices.id;
UPDATE 95
UPDATE result2 AS edges SET original_target_vertex = vertices.original_id
FROM result2_vertices_pgr AS vertices WHERE edges.target = vertices.id;
UPDATE 95
UPDATE result2
SET original_source_edge = edge,
original_target_edge = edge
WHERE edge != 0;
UPDATE 30
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 65
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;
UPDATE 65
DROP TABLE IF EXISTS q_result_1;
NOTICE: table "q_result_1" does not exist, skipping
DROP TABLE
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 181
SELECT * FROM q_result_1;
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+-------+------+------+----------
1 | 1 | -5 | -7 | -5 | 10 | 0 | 0
2 | 2 | -5 | -7 | -3 | 4 | 1 | 0
3 | 3 | -5 | -7 | 7000 | 25 | 0 | 1
4 | 4 | -5 | -7 | -12 | 21 | 1 | 1
5 | 5 | -5 | -7 | -20 | 46 | 0 | 2
6 | 6 | -5 | -7 | -18 | 40 | 1 | 2
7 | 7 | -5 | -7 | 16000 | 64 | 0 | 3
8 | 8 | -5 | -7 | -28 | 61 | 1 | 3
9 | 9 | -5 | -7 | 15000 | 17 | 0 | 4
10 | 10 | -5 | -7 | -8 | 15 | 1 | 4
11 | 11 | -5 | -7 | 10000 | 14 | 0 | 5
12 | 12 | -5 | -7 | -7 | -1 | 0 | 5
13 | 1 | -5 | -6 | -5 | 10 | 0 | 0
14 | 2 | -5 | -6 | -3 | 4 | 1 | 0
15 | 3 | -5 | -6 | 7000 | 25 | 0 | 1
16 | 4 | -5 | -6 | -12 | 21 | 1 | 1
17 | 5 | -5 | -6 | -20 | 46 | 0 | 2
18 | 6 | -5 | -6 | -18 | 40 | 1 | 2
19 | 7 | -5 | -6 | 16000 | 64 | 0 | 3
20 | 8 | -5 | -6 | -28 | 61 | 1 | 3
21 | 9 | -5 | -6 | 15000 | 17 | 0 | 4
22 | 10 | -5 | -6 | -8 | 15 | 1 | 4
23 | 11 | -5 | -6 | 10000 | 13 | 0 | 5
24 | 12 | -5 | -6 | -6 | -1 | 0 | 5
25 | 1 | -5 | 10000 | -5 | 10 | 0 | 0
26 | 2 | -5 | 10000 | -3 | 4 | 1 | 0
27 | 3 | -5 | 10000 | 7000 | 25 | 0 | 1
28 | 4 | -5 | 10000 | -12 | 21 | 1 | 1
29 | 5 | -5 | 10000 | -20 | 46 | 0 | 2
30 | 6 | -5 | 10000 | -18 | 40 | 1 | 2
31 | 7 | -5 | 10000 | 16000 | 64 | 0 | 3
32 | 8 | -5 | 10000 | -28 | 61 | 1 | 3
33 | 9 | -5 | 10000 | 15000 | 17 | 0 | 4
34 | 10 | -5 | 10000 | -8 | 15 | 1 | 4
35 | 11 | -5 | 10000 | 10000 | -1 | 0 | 5
36 | 1 | -4 | -7 | -4 | 8 | 0 | 0
37 | 2 | -4 | -7 | -3 | 4 | 1 | 0
38 | 3 | -4 | -7 | 7000 | 25 | 0 | 1
39 | 4 | -4 | -7 | -12 | 21 | 1 | 1
40 | 5 | -4 | -7 | -20 | 46 | 0 | 2
41 | 6 | -4 | -7 | -18 | 40 | 1 | 2
42 | 7 | -4 | -7 | 16000 | 64 | 0 | 3
43 | 8 | -4 | -7 | -28 | 61 | 1 | 3
44 | 9 | -4 | -7 | 15000 | 17 | 0 | 4
45 | 10 | -4 | -7 | -8 | 15 | 1 | 4
46 | 11 | -4 | -7 | 10000 | 14 | 0 | 5
47 | 12 | -4 | -7 | -7 | -1 | 0 | 5
48 | 1 | -4 | -6 | -4 | 8 | 0 | 0
49 | 2 | -4 | -6 | -3 | 4 | 1 | 0
50 | 3 | -4 | -6 | 7000 | 25 | 0 | 1
51 | 4 | -4 | -6 | -12 | 21 | 1 | 1
52 | 5 | -4 | -6 | -20 | 46 | 0 | 2
53 | 6 | -4 | -6 | -18 | 40 | 1 | 2
54 | 7 | -4 | -6 | 16000 | 64 | 0 | 3
55 | 8 | -4 | -6 | -28 | 61 | 1 | 3
56 | 9 | -4 | -6 | 15000 | 17 | 0 | 4
57 | 10 | -4 | -6 | -8 | 15 | 1 | 4
58 | 11 | -4 | -6 | 10000 | 13 | 0 | 5
59 | 12 | -4 | -6 | -6 | -1 | 0 | 5
60 | 1 | -4 | 10000 | -4 | 8 | 0 | 0
61 | 2 | -4 | 10000 | -3 | 4 | 1 | 0
62 | 3 | -4 | 10000 | 7000 | 25 | 0 | 1
63 | 4 | -4 | 10000 | -12 | 21 | 1 | 1
64 | 5 | -4 | 10000 | -20 | 46 | 0 | 2
65 | 6 | -4 | 10000 | -18 | 40 | 1 | 2
66 | 7 | -4 | 10000 | 16000 | 64 | 0 | 3
67 | 8 | -4 | 10000 | -28 | 61 | 1 | 3
68 | 9 | -4 | 10000 | 15000 | 17 | 0 | 4
69 | 10 | -4 | 10000 | -8 | 15 | 1 | 4
70 | 11 | -4 | 10000 | 10000 | -1 | 0 | 5
71 | 1 | -3 | -7 | -3 | 4 | 1 | 0
72 | 2 | -3 | -7 | 7000 | 25 | 0 | 1
73 | 3 | -3 | -7 | -12 | 21 | 1 | 1
74 | 4 | -3 | -7 | -20 | 46 | 0 | 2
75 | 5 | -3 | -7 | -18 | 40 | 1 | 2
76 | 6 | -3 | -7 | 16000 | 64 | 0 | 3
77 | 7 | -3 | -7 | -28 | 61 | 1 | 3
78 | 8 | -3 | -7 | 15000 | 17 | 0 | 4
79 | 9 | -3 | -7 | -8 | 15 | 1 | 4
80 | 10 | -3 | -7 | 10000 | 14 | 0 | 5
81 | 11 | -3 | -7 | -7 | -1 | 0 | 5
82 | 1 | -3 | -6 | -3 | 4 | 1 | 0
83 | 2 | -3 | -6 | 7000 | 25 | 0 | 1
84 | 3 | -3 | -6 | -12 | 21 | 1 | 1
85 | 4 | -3 | -6 | -20 | 46 | 0 | 2
86 | 5 | -3 | -6 | -18 | 40 | 1 | 2
87 | 6 | -3 | -6 | 16000 | 64 | 0 | 3
88 | 7 | -3 | -6 | -28 | 61 | 1 | 3
89 | 8 | -3 | -6 | 15000 | 17 | 0 | 4
90 | 9 | -3 | -6 | -8 | 15 | 1 | 4
91 | 10 | -3 | -6 | 10000 | 13 | 0 | 5
92 | 11 | -3 | -6 | -6 | -1 | 0 | 5
93 | 1 | -3 | 10000 | -3 | 4 | 1 | 0
94 | 2 | -3 | 10000 | 7000 | 25 | 0 | 1
95 | 3 | -3 | 10000 | -12 | 21 | 1 | 1
96 | 4 | -3 | 10000 | -20 | 46 | 0 | 2
97 | 5 | -3 | 10000 | -18 | 40 | 1 | 2
98 | 6 | -3 | 10000 | 16000 | 64 | 0 | 3
99 | 7 | -3 | 10000 | -28 | 61 | 1 | 3
100 | 8 | -3 | 10000 | 15000 | 17 | 0 | 4
101 | 9 | -3 | 10000 | -8 | 15 | 1 | 4
102 | 10 | -3 | 10000 | 10000 | -1 | 0 | 5
103 | 1 | -2 | -7 | -2 | 3 | 1 | 0
104 | 2 | -2 | -7 | 5000 | 2 | 0 | 1
105 | 3 | -2 | -7 | -1 | 1 | 1 | 1
106 | 4 | -2 | -7 | 6000 | 6 | 0 | 2
107 | 5 | -2 | -7 | -3 | 4 | 1 | 2
108 | 6 | -2 | -7 | 7000 | 25 | 0 | 3
109 | 7 | -2 | -7 | -12 | 21 | 1 | 3
110 | 8 | -2 | -7 | -20 | 46 | 0 | 4
111 | 9 | -2 | -7 | -18 | 40 | 1 | 4
112 | 10 | -2 | -7 | 16000 | 64 | 0 | 5
113 | 11 | -2 | -7 | -28 | 61 | 1 | 5
114 | 12 | -2 | -7 | 15000 | 17 | 0 | 6
115 | 13 | -2 | -7 | -8 | 15 | 1 | 6
116 | 14 | -2 | -7 | 10000 | 14 | 0 | 7
117 | 15 | -2 | -7 | -7 | -1 | 0 | 7
118 | 1 | -2 | -6 | -2 | 3 | 1 | 0
119 | 2 | -2 | -6 | 5000 | 2 | 0 | 1
120 | 3 | -2 | -6 | -1 | 1 | 1 | 1
121 | 4 | -2 | -6 | 6000 | 6 | 0 | 2
122 | 5 | -2 | -6 | -3 | 4 | 1 | 2
123 | 6 | -2 | -6 | 7000 | 25 | 0 | 3
124 | 7 | -2 | -6 | -12 | 21 | 1 | 3
125 | 8 | -2 | -6 | -20 | 46 | 0 | 4
126 | 9 | -2 | -6 | -18 | 40 | 1 | 4
127 | 10 | -2 | -6 | 16000 | 64 | 0 | 5
128 | 11 | -2 | -6 | -28 | 61 | 1 | 5
129 | 12 | -2 | -6 | 15000 | 17 | 0 | 6
130 | 13 | -2 | -6 | -8 | 15 | 1 | 6
131 | 14 | -2 | -6 | 10000 | 13 | 0 | 7
132 | 15 | -2 | -6 | -6 | -1 | 0 | 7
133 | 1 | -2 | 10000 | -2 | 3 | 1 | 0
134 | 2 | -2 | 10000 | 5000 | 2 | 0 | 1
135 | 3 | -2 | 10000 | -1 | 1 | 1 | 1
136 | 4 | -2 | 10000 | 6000 | 6 | 0 | 2
137 | 5 | -2 | 10000 | -3 | 4 | 1 | 2
138 | 6 | -2 | 10000 | 7000 | 25 | 0 | 3
139 | 7 | -2 | 10000 | -12 | 21 | 1 | 3
140 | 8 | -2 | 10000 | -20 | 46 | 0 | 4
141 | 9 | -2 | 10000 | -18 | 40 | 1 | 4
142 | 10 | -2 | 10000 | 16000 | 64 | 0 | 5
143 | 11 | -2 | 10000 | -28 | 61 | 1 | 5
144 | 12 | -2 | 10000 | 15000 | 17 | 0 | 6
145 | 13 | -2 | 10000 | -8 | 15 | 1 | 6
146 | 14 | -2 | 10000 | 10000 | -1 | 0 | 7
147 | 1 | 6000 | -7 | 6000 | 6 | 0 | 0
148 | 2 | 6000 | -7 | -3 | 4 | 1 | 0
149 | 3 | 6000 | -7 | 7000 | 25 | 0 | 1
150 | 4 | 6000 | -7 | -12 | 21 | 1 | 1
151 | 5 | 6000 | -7 | -20 | 46 | 0 | 2
152 | 6 | 6000 | -7 | -18 | 40 | 1 | 2
153 | 7 | 6000 | -7 | 16000 | 64 | 0 | 3
154 | 8 | 6000 | -7 | -28 | 61 | 1 | 3
155 | 9 | 6000 | -7 | 15000 | 17 | 0 | 4
156 | 10 | 6000 | -7 | -8 | 15 | 1 | 4
157 | 11 | 6000 | -7 | 10000 | 14 | 0 | 5
158 | 12 | 6000 | -7 | -7 | -1 | 0 | 5
159 | 1 | 6000 | -6 | 6000 | 6 | 0 | 0
160 | 2 | 6000 | -6 | -3 | 4 | 1 | 0
161 | 3 | 6000 | -6 | 7000 | 25 | 0 | 1
162 | 4 | 6000 | -6 | -12 | 21 | 1 | 1
163 | 5 | 6000 | -6 | -20 | 46 | 0 | 2
164 | 6 | 6000 | -6 | -18 | 40 | 1 | 2
165 | 7 | 6000 | -6 | 16000 | 64 | 0 | 3
166 | 8 | 6000 | -6 | -28 | 61 | 1 | 3
167 | 9 | 6000 | -6 | 15000 | 17 | 0 | 4
168 | 10 | 6000 | -6 | -8 | 15 | 1 | 4
169 | 11 | 6000 | -6 | 10000 | 13 | 0 | 5
170 | 12 | 6000 | -6 | -6 | -1 | 0 | 5
171 | 1 | 6000 | 10000 | 6000 | 6 | 0 | 0
172 | 2 | 6000 | 10000 | -3 | 4 | 1 | 0
173 | 3 | 6000 | 10000 | 7000 | 25 | 0 | 1
174 | 4 | 6000 | 10000 | -12 | 21 | 1 | 1
175 | 5 | 6000 | 10000 | -20 | 46 | 0 | 2
176 | 6 | 6000 | 10000 | -18 | 40 | 1 | 2
177 | 7 | 6000 | 10000 | 16000 | 64 | 0 | 3
178 | 8 | 6000 | 10000 | -28 | 61 | 1 | 3
179 | 9 | 6000 | 10000 | 15000 | 17 | 0 | 4
180 | 10 | 6000 | 10000 | -8 | 15 | 1 | 4
181 | 11 | 6000 | 10000 | 10000 | -1 | 0 | 5
(181 rows)
SELECT min(agg_cost) FROM q_result_1 WHERE edge = -1;
min
-----
5
(1 row)
SELECT path_seq, start_vid, end_vid FROM q_result_1 WHERE edge = -1 and agg_cost = 5;
path_seq | start_vid | end_vid
----------+-----------+---------
12 | -5 | -7
12 | -5 | -6
11 | -5 | 10000
12 | -4 | -7
12 | -4 | -6
11 | -4 | 10000
11 | -3 | -7
11 | -3 | -6
10 | -3 | 10000
12 | 6000 | -7
12 | 6000 | -6
11 | 6000 | 10000
(12 rows)
SELECT * FROM q_result_1 WHERE start_vid = -3 AND end_vid = 10000;
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+-------+------+------+----------
93 | 1 | -3 | 10000 | -3 | 4 | 1 | 0
94 | 2 | -3 | 10000 | 7000 | 25 | 0 | 1
95 | 3 | -3 | 10000 | -12 | 21 | 1 | 1
96 | 4 | -3 | 10000 | -20 | 46 | 0 | 2
97 | 5 | -3 | 10000 | -18 | 40 | 1 | 2
98 | 6 | -3 | 10000 | 16000 | 64 | 0 | 3
99 | 7 | -3 | 10000 | -28 | 61 | 1 | 3
100 | 8 | -3 | 10000 | 15000 | 17 | 0 | 4
101 | 9 | -3 | 10000 | -8 | 15 | 1 | 4
102 | 10 | -3 | 10000 | 10000 | -1 | 0 | 5
(10 rows)
SELECT * FROM q_result_1 WHERE start_vid = -3 AND end_vid = 10000 AND (cost != 0 OR edge = -1);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+-------+------+------+----------
93 | 1 | -3 | 10000 | -3 | 4 | 1 | 0
95 | 3 | -3 | 10000 | -12 | 21 | 1 | 1
97 | 5 | -3 | 10000 | -18 | 40 | 1 | 2
99 | 7 | -3 | 10000 | -28 | 61 | 1 | 3
101 | 9 | -3 | 10000 | -8 | 15 | 1 | 4
102 | 10 | -3 | 10000 | 10000 | -1 | 0 | 5
(6 rows)
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;
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+-------+------+------+----------
93 | 1 | 6000 | 10000 | 6000 | 4 | 1 | 0
95 | 3 | 6000 | 10000 | 7000 | 21 | 1 | 1
97 | 5 | 6000 | 10000 | 11000 | 40 | 1 | 2
99 | 7 | 6000 | 10000 | 16000 | 61 | 1 | 3
101 | 9 | 6000 | 10000 | 15000 | 15 | 1 | 4
102 | 10 | 6000 | 10000 | 10000 | -1 | 0 | 5
(6 rows)
SELECT * from pgr_dijkstra($$SELECT id, * FROM edges1$$,
ARRAY[6000], ARRAY[10000]);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+-------+------+------+----------
1 | 1 | 6000 | 10000 | 6000 | 400 | 1 | 0
2 | 2 | 6000 | 10000 | 7000 | 800 | 1 | 1
3 | 3 | 6000 | 10000 | 11000 | 900 | 1 | 2
4 | 4 | 6000 | 10000 | 16000 | 1600 | 1 | 3
5 | 5 | 6000 | 10000 | 15000 | 300 | 1 | 4
6 | 6 | 6000 | 10000 | 10000 | -1 | 0 | 5
(6 rows)
ROLLBACK;
ROLLBACK
|
