6. Appendix: Basic workshop solutions¶
6.1. Solutions to Pedestrian Routing¶
6.1.1. Exercise: 1 (Chapter: Pedestrian)¶
Exercise 1: Single pedestrian routing
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------------------------+-----------------------
1 | 1 | 3770 | 2820 | 3770 | 4500 | 8.388535900537716e-05 | 0
2 | 2 | 3770 | 2820 | 3776 | 7282 | 0.00042957455346101254 | 8.388535900537716e-05
3 | 3 | 3770 | 2820 | 6331 | 7281 | 0.0010443881660275542 | 0.0005134599124663897
4 | 4 | 3770 | 2820 | 3779 | 4503 | 0.000328368536714787 | 0.001557848078493944
5 | 5 | 3770 | 2820 | 3792 | 4522 | 0.0006891345977438102 | 0.0018862166152087308
6 | 6 | 3770 | 2820 | 5932 | 6871 | 0.0002524941385430749 | 0.002575351212952541
7 | 7 | 3770 | 2820 | 5153 | 6033 | 9.826372677722964e-05 | 0.002827845351495616
8 | 8 | 3770 | 2820 | 5154 | 6032 | 0.00010299796114649973 | 0.0029261090782728453
9 | 9 | 3770 | 2820 | 29 | 38 | 0.00023002225978721737 | 0.003029107039419345
10 | 10 | 3770 | 2820 | 1363 | 1723 | 2.1328384842893176e-05 | 0.0032591292992065622
11 | 11 | 3770 | 2820 | 4817 | 5685 | 6.710470435823465e-05 | 0.0032804576840494556
12 | 12 | 3770 | 2820 | 17 | 23 | 0.0022424154908164424 | 0.0033475623884076902
13 | 13 | 3770 | 2820 | 4790 | 5652 | 8.258117218987748e-05 | 0.005589977879224132
14 | 14 | 3770 | 2820 | 1458 | 1850 | 0.0017118576535768337 | 0.00567255905141401
15 | 15 | 3770 | 2820 | 3838 | 1851 | 5.402462401613823e-05 | 0.007384416704990844
16 | 16 | 3770 | 2820 | 1459 | 7336 | 0.000168335749431199 | 0.007438441329006982
17 | 17 | 3770 | 2820 | 33 | 3110 | 0.0005521787334122599 | 0.007606777078438181
18 | 18 | 3770 | 2820 | 2540 | 152 | 0.0005936025436522815 | 0.00815895581185044
19 | 19 | 3770 | 2820 | 102 | 2993 | 0.0008816231493763614 | 0.008752558355502722
20 | 20 | 3770 | 2820 | 2450 | 3416 | 0.0007587908254733535 | 0.009634181504879083
21 | 21 | 3770 | 2820 | 2792 | 2938 | 0.0002137904223262414 | 0.010392972330352437
22 | 22 | 3770 | 2820 | 2411 | 2909 | 0.00031504804703230325 | 0.010606762752678679
23 | 23 | 3770 | 2820 | 2392 | 2910 | 0.0001213873963805966 | 0.010921810799710982
24 | 24 | 3770 | 2820 | 2395 | 2913 | 0.0005904536425075705 | 0.01104319819609158
25 | 25 | 3770 | 2820 | 2810 | 3435 | 0.00036324338947932376 | 0.01163365183859915
26 | 26 | 3770 | 2820 | 2427 | 2964 | 0.00011568111341010259 | 0.011996895228078473
27 | 27 | 3770 | 2820 | 2399 | 2919 | 0.0004066729162192657 | 0.012112576341488575
28 | 28 | 3770 | 2820 | 2405 | 2928 | 0.0005362549188534863 | 0.01251924925770784
29 | 29 | 3770 | 2820 | 333 | 2943 | 0.001925304976053417 | 0.013055504176561327
30 | 30 | 3770 | 2820 | 2414 | 2954 | 0.00010782954140603501 | 0.014980809152614745
31 | 31 | 3770 | 2820 | 2421 | 3419 | 0.0002499105250478044 | 0.01508863869402078
32 | 32 | 3770 | 2820 | 2820 | -1 | 0 | 0.015338549219068584
(32 rows)
6.1.2. Exercise: 2 (Chapter: Pedestrian)¶
Exercise 2: Many Pedestrians going to the same destination
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+--------------------+--------------------
1 | 1 | 2592 | 2820 | 2592 | 2931 | 6.7536248033073605 | 0
2 | 2 | 2592 | 2820 | 2406 | 2930 | 36.53433593109525 | 6.7536248033073605
3 | 3 | 2592 | 2820 | 909 | 1153 | 6.759546296994868 | 43.28796073440261
4 | 4 | 2592 | 2820 | 2398 | 2917 | 63.72346396077792 | 50.04750703139748
5 | 5 | 2592 | 2820 | 2397 | 2915 | 49.22199621339028 | 113.77097099217539
6 | 6 | 2592 | 2820 | 103 | 3397 | 9.016431398753904 | 162.99296720556566
7 | 7 | 2592 | 2820 | 2819 | 3447 | 21.078787596558126 | 172.00939860431956
8 | 8 | 2592 | 2820 | 2823 | 3420 | 100.0966971526558 | 193.0881862008777
9 | 9 | 2592 | 2820 | 2820 | -1 | 0 | 293.1848833535335
10 | 1 | 3770 | 2820 | 3770 | 4500 | 6.9723264134404666 | 0
11 | 2 | 3770 | 2820 | 3776 | 7282 | 44.61205137354944 | 6.9723264134404666
12 | 3 | 3770 | 2820 | 6331 | 7281 | 94.91203888570905 | 51.584377786989904
13 | 4 | 3770 | 2820 | 3779 | 4503 | 29.325284596881446 | 146.49641667269896
14 | 5 | 3770 | 2820 | 3792 | 4522 | 67.1652340862928 | 175.8217012695804
15 | 6 | 3770 | 2820 | 5932 | 6871 | 27.084970826320614 | 242.9869353558732
16 | 7 | 3770 | 2820 | 5153 | 7389 | 89.70403088207236 | 270.0719061821938
17 | 8 | 3770 | 2820 | 1200 | 1505 | 47.90327630714417 | 359.77593706426615
18 | 9 | 3770 | 2820 | 1199 | 1503 | 97.26693373987683 | 407.6792133714103
19 | 10 | 3770 | 2820 | 2713 | 3318 | 8.702014508747755 | 504.94614711128713
20 | 11 | 3770 | 2820 | 4901 | 5768 | 169.66308305211936 | 513.6481616200349
21 | 12 | 3770 | 2820 | 2723 | 3330 | 9.28214727341565 | 683.3112446721543
22 | 13 | 3770 | 2820 | 2715 | 3321 | 20.556063954315675 | 692.5933919455699
23 | 14 | 3770 | 2820 | 2720 | 4576 | 1.2120613291845297 | 713.1494558998857
24 | 15 | 3770 | 2820 | 3838 | 1851 | 5.299297480980079 | 714.3615172290702
25 | 16 | 3770 | 2820 | 1459 | 7336 | 17.902891690423896 | 719.6608147100502
26 | 17 | 3770 | 2820 | 33 | 3110 | 55.790230570295094 | 737.5637064004741
27 | 18 | 3770 | 2820 | 2540 | 152 | 57.88259681382042 | 793.3539369707692
28 | 19 | 3770 | 2820 | 102 | 2993 | 95.34069392298983 | 851.2365337845896
29 | 20 | 3770 | 2820 | 2450 | 3416 | 81.07366178870542 | 946.5772277075794
30 | 21 | 3770 | 2820 | 2792 | 2938 | 23.050000105468474 | 1027.650889496285
31 | 22 | 3770 | 2820 | 2411 | 2909 | 29.086086287490694 | 1050.7008896017533
32 | 23 | 3770 | 2820 | 2392 | 2910 | 10.068787274730918 | 1079.7869758892439
33 | 24 | 3770 | 2820 | 2395 | 2913 | 51.76222094150203 | 1089.8557631639749
34 | 25 | 3770 | 2820 | 2810 | 3435 | 31.34159994334504 | 1141.6179841054768
35 | 26 | 3770 | 2820 | 2427 | 2964 | 9.95198649029962 | 1172.9595840488219
36 | 27 | 3770 | 2820 | 2399 | 2919 | 34.82654765584812 | 1182.9115705391214
37 | 28 | 3770 | 2820 | 2405 | 2928 | 46.66039712595698 | 1217.7381181949695
38 | 29 | 3770 | 2820 | 333 | 2943 | 164.89491748857662 | 1264.3985153209264
39 | 30 | 3770 | 2820 | 2414 | 2954 | 9.305114560649153 | 1429.293432809503
40 | 31 | 3770 | 2820 | 2421 | 3419 | 27.38598660544258 | 1438.5985473701521
41 | 32 | 3770 | 2820 | 2820 | -1 | 0 | 1465.9845339755948
(41 rows)
6.1.3. Exercise: 3 (Chapter: Pedestrian)¶
Exercise 3: Many Pedestrians departing from the same location
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+--------------------+--------------------
1 | 1 | 2820 | 2592 | 2820 | 3420 | 76.99745934819677 | 0
2 | 2 | 2820 | 2592 | 2823 | 3447 | 16.214451997352405 | 76.99745934819677
3 | 3 | 2820 | 2592 | 2819 | 3397 | 6.9357164605799255 | 93.21191134554917
4 | 4 | 2820 | 2592 | 103 | 2915 | 37.86307401030021 | 100.1476278061291
5 | 5 | 2820 | 2592 | 2397 | 2917 | 49.0180492005984 | 138.0107018164293
6 | 6 | 2820 | 2592 | 2398 | 1153 | 5.19965099768836 | 187.0287510170277
7 | 7 | 2820 | 2592 | 909 | 2930 | 28.103335331611728 | 192.22840201471607
8 | 8 | 2820 | 2592 | 2406 | 2931 | 5.1950960025441235 | 220.3317373463278
9 | 9 | 2820 | 2592 | 2592 | -1 | 0 | 225.52683334887192
10 | 1 | 2820 | 3770 | 2820 | 3419 | 21.06614354264814 | 0
11 | 2 | 2820 | 3770 | 2421 | 2954 | 7.157780431268579 | 21.06614354264814
12 | 3 | 2820 | 3770 | 2414 | 2943 | 126.84224422198201 | 28.22392397391672
13 | 4 | 2820 | 3770 | 333 | 2928 | 35.89261317381306 | 155.06616819589874
14 | 5 | 2820 | 3770 | 2405 | 2919 | 26.78965204296009 | 190.9587813697118
15 | 6 | 2820 | 3770 | 2399 | 2964 | 7.655374223307399 | 217.7484334126719
16 | 7 | 2820 | 3770 | 2427 | 3435 | 24.10892303334234 | 225.4038076359793
17 | 8 | 2820 | 3770 | 2810 | 2913 | 39.817093031924635 | 249.51273066932163
18 | 9 | 2820 | 3770 | 2395 | 2910 | 7.745220980562244 | 289.32982370124626
19 | 10 | 2820 | 3770 | 2392 | 2909 | 22.373912528838996 | 297.0750446818085
20 | 11 | 2820 | 3770 | 2411 | 2938 | 17.730769311898825 | 319.4489572106475
21 | 12 | 2820 | 3770 | 2792 | 3416 | 62.364355222081095 | 337.17972652254633
22 | 13 | 2820 | 3770 | 2450 | 2993 | 73.33899532537679 | 399.5440817446274
23 | 14 | 2820 | 3770 | 102 | 152 | 44.52507447216955 | 472.8830770700042
24 | 15 | 2820 | 3770 | 2540 | 3110 | 42.91556197715007 | 517.4081515421738
25 | 16 | 2820 | 3770 | 33 | 7336 | 13.77145514647992 | 560.3237135193239
26 | 17 | 2820 | 3770 | 1459 | 1851 | 4.076382677676984 | 574.0951686658037
27 | 18 | 2820 | 3770 | 3838 | 4576 | 0.9323548686034844 | 578.1715513434807
28 | 19 | 2820 | 3770 | 2720 | 3321 | 15.812356887935135 | 579.1039062120842
29 | 20 | 2820 | 3770 | 2715 | 3330 | 7.140113287242807 | 594.9162631000194
30 | 21 | 2820 | 3770 | 2723 | 5768 | 130.51006388624566 | 602.0563763872622
31 | 22 | 2820 | 3770 | 4901 | 3318 | 6.69385731442135 | 732.5664402735079
32 | 23 | 2820 | 3770 | 2713 | 1503 | 74.82071826144372 | 739.2602975879292
33 | 24 | 2820 | 3770 | 1199 | 1505 | 36.84867408241859 | 814.081015849373
34 | 25 | 2820 | 3770 | 1200 | 7389 | 69.0031006785172 | 850.9296899317916
35 | 26 | 2820 | 3770 | 5153 | 6871 | 20.83459294332355 | 919.9327906103088
36 | 27 | 2820 | 3770 | 5932 | 4522 | 51.66556468176369 | 940.7673835536323
37 | 28 | 2820 | 3770 | 3792 | 4503 | 22.55791122837034 | 992.432948235396
38 | 29 | 2820 | 3770 | 3779 | 7281 | 73.00926068131466 | 1014.9908594637664
39 | 30 | 2820 | 3770 | 6331 | 7282 | 34.31696259503803 | 1088.000120145081
40 | 31 | 2820 | 3770 | 3776 | 4500 | 5.363328010338821 | 1122.3170827401188
41 | 32 | 2820 | 3770 | 3770 | -1 | 0 | 1127.6804107504577
(41 rows)
6.1.4. Exercise: 4 (Chapter: Pedestrian)¶
Exercise 4: Many Pedestrians going to different destinations
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+----------------------+---------------------
1 | 1 | 2592 | 3829 | 2592 | 3168 | 0.9988341469376568 | 0
2 | 2 | 2592 | 3829 | 2549 | 171 | 0.7717166274214478 | 0.9988341469376568
3 | 3 | 2592 | 3829 | 114 | 173 | 0.4561735719190132 | 1.7705507743591045
4 | 4 | 2592 | 3829 | 5225 | 6110 | 0.5804781467058256 | 2.2267243462781177
5 | 5 | 2592 | 3829 | 2805 | 3430 | 1.2115777527600988 | 2.807202492983943
6 | 6 | 2592 | 3829 | 104 | 154 | 0.7622775314496067 | 4.018780245744042
7 | 7 | 2592 | 3829 | 324 | 470 | 0.14485636343956315 | 4.781057777193649
8 | 8 | 2592 | 3829 | 2428 | 2965 | 0.5348817450766162 | 4.925914140633211
9 | 9 | 2592 | 3829 | 2411 | 2938 | 0.2955128218649804 | 5.460795885709827
10 | 10 | 2592 | 3829 | 2792 | 3416 | 1.0394059203680182 | 5.756308707574807
11 | 11 | 2592 | 3829 | 2450 | 2993 | 1.2223165887562797 | 6.795714627942825
12 | 12 | 2592 | 3829 | 102 | 152 | 0.7420845745361592 | 8.018031216699105
13 | 13 | 2592 | 3829 | 2540 | 3110 | 0.7152593662858345 | 8.760115791235265
14 | 14 | 2592 | 3829 | 33 | 7336 | 0.22952425244133198 | 9.4753751575211
15 | 15 | 2592 | 3829 | 1459 | 1852 | 1.2326831786923476 | 9.704899409962431
16 | 16 | 2592 | 3829 | 4477 | 5276 | 1.0993067265140584 | 10.937582588654779
17 | 17 | 2592 | 3829 | 5152 | 6031 | 0.1088111309823814 | 12.036889315168837
18 | 18 | 2592 | 3829 | 3754 | 4478 | 0.05622007803317889 | 12.145700446151219
19 | 19 | 2592 | 3829 | 4785 | 5644 | 0.35348491323014763 | 12.201920524184398
20 | 20 | 2592 | 3829 | 3829 | -1 | 0 | 12.555405437414546
21 | 1 | 2592 | 5912 | 2592 | 2931 | 0.08658493337573539 | 0
22 | 2 | 2592 | 5912 | 2406 | 2947 | 2.2142648644851466 | 0.08658493337573539
23 | 3 | 2592 | 5912 | 2416 | 7377 | 0.2632975423579356 | 2.300849797860882
24 | 4 | 2592 | 5912 | 3686 | 3902 | 0.488928518678103 | 2.5641473402188177
25 | 5 | 2592 | 5912 | 3239 | 7277 | 1.311535193589397 | 3.053075858896921
26 | 6 | 2592 | 5912 | 6327 | 3945 | 1.3150335691620738 | 4.3646110524863175
27 | 7 | 2592 | 5912 | 3276 | 7396 | 1.4474459990691722 | 5.679644621648391
28 | 8 | 2592 | 5912 | 5927 | 7373 | 0.028663930905052714 | 7.1270906207175635
29 | 9 | 2592 | 5912 | 3274 | 7371 | 0.6267924654409215 | 7.155754551622616
30 | 10 | 2592 | 5912 | 3262 | 3926 | 0.09448353491099692 | 7.782547017063537
31 | 11 | 2592 | 5912 | 3267 | 3933 | 0.06268955728291785 | 7.877030551974534
32 | 12 | 2592 | 5912 | 3243 | 3906 | 0.37170097387838474 | 7.939720109257452
33 | 13 | 2592 | 5912 | 5912 | -1 | 0 | 8.311421083135837
34 | 1 | 3770 | 3829 | 3770 | 4500 | 0.08938880017231368 | 0
35 | 2 | 3770 | 3829 | 3776 | 7282 | 0.5719493765839672 | 0.08938880017231368
36 | 3 | 3770 | 3829 | 6331 | 7281 | 1.2168210113552442 | 0.6613381767562808
37 | 4 | 3770 | 3829 | 3779 | 4503 | 0.37596518713950566 | 1.8781591881115252
38 | 5 | 3770 | 3829 | 3792 | 4522 | 0.8610927446960616 | 2.2541243752510307
39 | 6 | 3770 | 3829 | 5932 | 6871 | 0.3472432157220592 | 3.115217119947092
40 | 7 | 3770 | 3829 | 5153 | 6033 | 0.10476605767237325 | 3.4624603356691512
41 | 8 | 3770 | 3829 | 5154 | 6032 | 0.14585226228330223 | 3.5672263933415245
42 | 9 | 3770 | 3829 | 29 | 38 | 0.3275533101860614 | 3.7130786556248268
43 | 10 | 3770 | 3829 | 1363 | 1723 | 0.030355010308947026 | 4.0406319658108885
44 | 11 | 3770 | 3829 | 4817 | 5685 | 0.09336451568112399 | 4.070986976119835
45 | 12 | 3770 | 3829 | 17 | 3323 | 1.2471661394301004 | 4.164351491800959
46 | 13 | 3770 | 3829 | 2717 | 3414 | 0.7842802169961682 | 5.411517631231059
47 | 14 | 3770 | 3829 | 2791 | 4564 | 0.9117781949423808 | 6.195797848227228
48 | 15 | 3770 | 3829 | 3829 | -1 | 0 | 7.107576043169608
49 | 1 | 3770 | 5912 | 3770 | 4500 | 0.08938880017231368 | 0
50 | 2 | 3770 | 5912 | 3776 | 7282 | 0.5719493765839672 | 0.08938880017231368
51 | 3 | 3770 | 5912 | 6331 | 7281 | 1.2168210113552442 | 0.6613381767562808
52 | 4 | 3770 | 5912 | 3779 | 4503 | 0.37596518713950566 | 1.8781591881115252
53 | 5 | 3770 | 5912 | 3792 | 4522 | 0.8610927446960616 | 2.2541243752510307
54 | 6 | 3770 | 5912 | 5932 | 6871 | 0.3472432157220592 | 3.115217119947092
55 | 7 | 3770 | 5912 | 5153 | 7389 | 1.1500516779752867 | 3.4624603356691512
56 | 8 | 3770 | 5912 | 1200 | 1505 | 0.6141445680403098 | 4.612512013644438
57 | 9 | 3770 | 5912 | 1199 | 1503 | 1.247011971024062 | 5.226656581684748
58 | 10 | 3770 | 5912 | 2713 | 3318 | 0.11156428857368916 | 6.473668552708809
59 | 11 | 3770 | 5912 | 4901 | 5768 | 2.1751677314374276 | 6.585232841282498
60 | 12 | 3770 | 5912 | 2723 | 3330 | 0.11900188812071345 | 8.760400572719925
61 | 13 | 3770 | 5912 | 2715 | 3321 | 0.2635392814655856 | 8.87940246084064
62 | 14 | 3770 | 5912 | 2720 | 4576 | 0.015539247810058074 | 9.142941742306224
63 | 15 | 3770 | 5912 | 3838 | 1851 | 0.06793971129461639 | 9.158480990116281
64 | 16 | 3770 | 5912 | 1459 | 7336 | 0.22952425244133198 | 9.226420701410898
65 | 17 | 3770 | 5912 | 33 | 3110 | 0.7152593662858345 | 9.45594495385223
66 | 18 | 3770 | 5912 | 2540 | 152 | 0.7420845745361592 | 10.171204320138065
67 | 19 | 3770 | 5912 | 102 | 2993 | 1.2223165887562797 | 10.913288894674224
68 | 20 | 3770 | 5912 | 2450 | 3416 | 1.0394059203680182 | 12.135605483430504
69 | 21 | 3770 | 5912 | 2792 | 2938 | 0.2955128218649804 | 13.175011403798523
70 | 22 | 3770 | 5912 | 2411 | 2909 | 0.3728985421473166 | 13.470524225663503
71 | 23 | 3770 | 5912 | 2392 | 2910 | 0.12908701634270406 | 13.84342276781082
72 | 24 | 3770 | 5912 | 2395 | 2913 | 0.6636182171987439 | 13.972509784153523
73 | 25 | 3770 | 5912 | 2810 | 3435 | 0.401815383889039 | 14.636128001352267
74 | 26 | 3770 | 5912 | 2427 | 2964 | 0.12758957038845667 | 15.037943385241306
75 | 27 | 3770 | 5912 | 2399 | 2919 | 0.4464942007160015 | 15.165532955629763
76 | 28 | 3770 | 5912 | 2405 | 2928 | 0.598210219563551 | 15.612027156345764
77 | 29 | 3770 | 5912 | 333 | 3451 | 1.1543303314976359 | 16.210237375909315
78 | 30 | 3770 | 5912 | 2829 | 153 | 0.22345661181885992 | 17.36456770740695
79 | 31 | 3770 | 5912 | 103 | 3397 | 0.11559527434299875 | 17.588024319225813
80 | 32 | 3770 | 5912 | 2819 | 3447 | 0.2702408666225401 | 17.70361959356881
81 | 33 | 3770 | 5912 | 2823 | 59 | 0.4462478673509816 | 17.97386046019135
82 | 34 | 3770 | 5912 | 46 | 2996 | 0.5589679540793616 | 18.42010832754233
83 | 35 | 3770 | 5912 | 2452 | 2946 | 1.2311427884566288 | 18.97907628162169
84 | 36 | 3770 | 5912 | 2416 | 7377 | 0.2632975423579356 | 20.21021907007832
85 | 37 | 3770 | 5912 | 3686 | 3902 | 0.488928518678103 | 20.473516612436256
86 | 38 | 3770 | 5912 | 3239 | 7277 | 1.311535193589397 | 20.96244513111436
87 | 39 | 3770 | 5912 | 6327 | 3945 | 1.3150335691620738 | 22.273980324703757
88 | 40 | 3770 | 5912 | 3276 | 7396 | 1.4474459990691722 | 23.589013893865832
89 | 41 | 3770 | 5912 | 5927 | 7373 | 0.028663930905052714 | 25.036459892935003
90 | 42 | 3770 | 5912 | 3274 | 7371 | 0.6267924654409215 | 25.065123823840057
91 | 43 | 3770 | 5912 | 3262 | 3926 | 0.09448353491099692 | 25.69191628928098
92 | 44 | 3770 | 5912 | 3267 | 3933 | 0.06268955728291785 | 25.786399824191975
93 | 45 | 3770 | 5912 | 3243 | 3906 | 0.37170097387838474 | 25.849089381474894
94 | 46 | 3770 | 5912 | 5912 | -1 | 0 | 26.22079035535328
(94 rows)
6.1.5. Exercise: 5 (Chapter: Pedestrian)¶
Exercise 5: Time for many Pedestrians going to different destinations
start_vid | end_vid | agg_cost
-----------+---------+--------------------
2592 | 3829 | 12.555405437414546
2592 | 5912 | 8.311421083135837
3770 | 3829 | 7.107576043169608
3770 | 5912 | 26.22079035535328
(4 rows)
6.1.6. Exercise: 6 (Chapter: Pedestrian)¶
start_vid | sum
-----------+--------------------
2592 | 20.866826520550383
3770 | 33.32836639852289
(2 rows)
6.2. Solutions to Vehicle Routing¶
6.2.1. Exercise: 1 (Chapter: Vehicle)¶
Exercise 1: Vehicle routing - going
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------------------------+-----------------------
1 | 1 | 3770 | 2820 | 3770 | 4500 | 8.388535900537716e-05 | 0
2 | 2 | 3770 | 2820 | 3776 | 7282 | 0.00042957455346101254 | 8.388535900537716e-05
3 | 3 | 3770 | 2820 | 6331 | 7281 | 0.0010443881660275542 | 0.0005134599124663897
4 | 4 | 3770 | 2820 | 3779 | 4503 | 0.000328368536714787 | 0.001557848078493944
5 | 5 | 3770 | 2820 | 3792 | 4522 | 0.0006891345977438102 | 0.0018862166152087308
6 | 6 | 3770 | 2820 | 5932 | 6871 | 0.0002524941385430749 | 0.002575351212952541
7 | 7 | 3770 | 2820 | 5153 | 6033 | 9.826372677722964e-05 | 0.002827845351495616
8 | 8 | 3770 | 2820 | 5154 | 6032 | 0.00010299796114649973 | 0.0029261090782728453
9 | 9 | 3770 | 2820 | 29 | 38 | 0.00023002225978721737 | 0.003029107039419345
10 | 10 | 3770 | 2820 | 1363 | 1723 | 2.1328384842893176e-05 | 0.0032591292992065622
11 | 11 | 3770 | 2820 | 4817 | 5685 | 6.710470435823465e-05 | 0.0032804576840494556
12 | 12 | 3770 | 2820 | 17 | 23 | 0.0022424154908164424 | 0.0033475623884076902
13 | 13 | 3770 | 2820 | 4790 | 5652 | 8.258117218987748e-05 | 0.005589977879224132
14 | 14 | 3770 | 2820 | 1458 | 1850 | 0.0017118576535768337 | 0.00567255905141401
15 | 15 | 3770 | 2820 | 3838 | 1851 | 5.402462401613823e-05 | 0.007384416704990844
16 | 16 | 3770 | 2820 | 1459 | 7336 | 0.000168335749431199 | 0.007438441329006982
17 | 17 | 3770 | 2820 | 33 | 3110 | 0.0005521787334122599 | 0.007606777078438181
18 | 18 | 3770 | 2820 | 2540 | 152 | 0.0005936025436522815 | 0.00815895581185044
19 | 19 | 3770 | 2820 | 102 | 2993 | 0.0008816231493763614 | 0.008752558355502722
20 | 20 | 3770 | 2820 | 2450 | 3416 | 0.0007587908254733535 | 0.009634181504879083
21 | 21 | 3770 | 2820 | 2792 | 2938 | 0.0002137904223262414 | 0.010392972330352437
22 | 22 | 3770 | 2820 | 2411 | 2909 | 0.00031504804703230325 | 0.010606762752678679
23 | 23 | 3770 | 2820 | 2392 | 2910 | 0.0001213873963805966 | 0.010921810799710982
24 | 24 | 3770 | 2820 | 2395 | 2913 | 0.0005904536425075705 | 0.01104319819609158
25 | 25 | 3770 | 2820 | 2810 | 3435 | 0.00036324338947932376 | 0.01163365183859915
26 | 26 | 3770 | 2820 | 2427 | 2964 | 0.00011568111341010259 | 0.011996895228078473
27 | 27 | 3770 | 2820 | 2399 | 2919 | 0.0004066729162192657 | 0.012112576341488575
28 | 28 | 3770 | 2820 | 2405 | 2928 | 0.0005362549188534863 | 0.01251924925770784
29 | 29 | 3770 | 2820 | 333 | 2943 | 0.001925304976053417 | 0.013055504176561327
30 | 30 | 3770 | 2820 | 2414 | 2954 | 0.00010782954140603501 | 0.014980809152614745
31 | 31 | 3770 | 2820 | 2421 | 3419 | 0.0002499105250478044 | 0.01508863869402078
32 | 32 | 3770 | 2820 | 2820 | -1 | 0 | 0.015338549219068584
(32 rows)
6.2.2. Exercise: 2 (Chapter: Vehicle)¶
Exercise 2: Vehicle routing - returning
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------------------------+-----------------------
1 | 1 | 2820 | 3770 | 2820 | 3419 | 0.0002499105250478044 | 0
2 | 2 | 2820 | 3770 | 2421 | 2954 | 0.00010782954140603501 | 0.0002499105250478044
3 | 3 | 2820 | 3770 | 2414 | 2951 | 0.0007630336691617203 | 0.0003577400664538394
4 | 4 | 2820 | 3770 | 2418 | 2981 | 6.247719583746634e-05 | 0.0011207737356155596
5 | 5 | 2820 | 3770 | 2440 | 6112 | 0.00045942617338782393 | 0.0011832509314530259
6 | 6 | 2820 | 3770 | 5227 | 5147 | 0.00021950760492748676 | 0.00164267710484085
7 | 7 | 2820 | 3770 | 4340 | 3459 | 0.0003004510493254666 | 0.0018621847097683368
8 | 8 | 2820 | 3770 | 2835 | 3460 | 0.00034827173567306423 | 0.0021626357590938034
9 | 9 | 2820 | 3770 | 2837 | 475 | 6.693818043583121e-05 | 0.002510907494766868
10 | 10 | 2820 | 3770 | 330 | 4107 | 0.00036818931590407535 | 0.002577845675202699
11 | 11 | 2820 | 3770 | 3420 | 299 | 0.0006632316472481694 | 0.002946034991106774
12 | 12 | 2820 | 3770 | 200 | 4122 | 0.00016172275659127278 | 0.0036092666383549434
13 | 13 | 2820 | 3770 | 3431 | 169 | 8.935379118894934e-05 | 0.003770989394946216
14 | 14 | 2820 | 3770 | 113 | 170 | 0.0003822234555890624 | 0.0038603431861351655
15 | 15 | 2820 | 3770 | 5150 | 6028 | 0.0008019880107842333 | 0.004242566641724228
16 | 16 | 2820 | 3770 | 2401 | 6026 | 5.048762224228642e-05 | 0.005044554652508462
17 | 17 | 2820 | 3770 | 5148 | 3412 | 0.0002011836225932666 | 0.005095042274750748
18 | 18 | 2820 | 3770 | 2788 | 2924 | 0.00046168579142433913 | 0.005296225897344015
19 | 19 | 2820 | 3770 | 2403 | 2926 | 0.0005080769676952968 | 0.005757911688768354
20 | 20 | 2820 | 3770 | 2404 | 5596 | 3.570616193701053e-05 | 0.006265988656463651
21 | 21 | 2820 | 3770 | 4737 | 5597 | 0.00021015889930509966 | 0.0063016948184006615
22 | 22 | 2820 | 3770 | 6357 | 7819 | 0.00021246011390679622 | 0.006511853717705761
23 | 23 | 2820 | 3770 | 6678 | 7524 | 0.00025720775474079164 | 0.0067243138316125575
24 | 24 | 2820 | 3770 | 6358 | 7309 | 0.00047453453075570217 | 0.006981521586353349
25 | 25 | 2820 | 3770 | 5060 | 5927 | 0.0009677127844785529 | 0.007456056117109051
26 | 26 | 2820 | 3770 | 2723 | 5768 | 0.0016380829017515765 | 0.008423768901587603
27 | 27 | 2820 | 3770 | 4901 | 3318 | 9.267988218251992e-05 | 0.01006185180333918
28 | 28 | 2820 | 3770 | 2713 | 1503 | 0.0009863204720853792 | 0.0101545316855217
29 | 29 | 2820 | 3770 | 1199 | 1505 | 0.000526205016435997 | 0.01114085215760708
30 | 30 | 2820 | 3770 | 1200 | 7389 | 0.0010306385594923433 | 0.011667057174043077
31 | 31 | 2820 | 3770 | 5153 | 6871 | 0.0002524941385430749 | 0.01269769573353542
32 | 32 | 2820 | 3770 | 5932 | 4522 | 0.0006891345977438102 | 0.012950189872078495
33 | 33 | 2820 | 3770 | 3792 | 4503 | 0.000328368536714787 | 0.013639324469822306
34 | 34 | 2820 | 3770 | 3779 | 7281 | 0.0010443881660275542 | 0.013967693006537092
35 | 35 | 2820 | 3770 | 6331 | 7282 | 0.00042957455346101254 | 0.015012081172564647
36 | 36 | 2820 | 3770 | 3776 | 4500 | 8.388535900537716e-05 | 0.015441655726025658
37 | 37 | 2820 | 3770 | 3770 | -1 | 0 | 0.015525541085031035
(37 rows)
6.2.3. Exercise: 3 (Chapter: Vehicle)¶
Exercise 3: Vehicle routing when time is money
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+----------------------+----------------------
1 | 1 | 3770 | 2820 | 3770 | 4500 | 0.013944652826880934 | 0
2 | 2 | 3770 | 2820 | 3776 | 7282 | 0.08922410274709888 | 0.013944652826880934
3 | 3 | 3770 | 2820 | 6331 | 7281 | 0.18982407777141808 | 0.10316875557397981
4 | 4 | 3770 | 2820 | 3779 | 4503 | 0.058650569193762894 | 0.2929928333453979
5 | 5 | 3770 | 2820 | 3792 | 4522 | 0.1343304681725856 | 0.35164340253916077
6 | 6 | 3770 | 2820 | 5932 | 6871 | 0.05416994165264122 | 0.48597387071174636
7 | 7 | 3770 | 2820 | 5153 | 6033 | 0.04085876249222557 | 0.5401438123643876
8 | 8 | 3770 | 2820 | 5154 | 6032 | 0.02275295291619514 | 0.5810025748566131
9 | 9 | 3770 | 2820 | 29 | 38 | 0.12774579097256392 | 0.6037555277728083
10 | 10 | 3770 | 2820 | 1363 | 1723 | 0.004735381608195736 | 0.7315013187453722
11 | 11 | 3770 | 2820 | 4817 | 5685 | 0.014564864446255344 | 0.7362367003535679
12 | 12 | 3770 | 2820 | 17 | 23 | 0.4064367928379377 | 0.7508015647998233
13 | 13 | 3770 | 2820 | 4790 | 5652 | 0.016780827914217922 | 1.157238357637761
14 | 14 | 3770 | 2820 | 1458 | 1850 | 0.35975624951369073 | 1.1740191855519788
15 | 15 | 3770 | 2820 | 3838 | 1851 | 0.010598594961960158 | 1.5337754350656696
16 | 16 | 3770 | 2820 | 1459 | 7336 | 0.04475722922605974 | 1.5443740300276299
17 | 17 | 3770 | 2820 | 33 | 3110 | 0.13947557642573777 | 1.5891312592536897
18 | 18 | 3770 | 2820 | 2540 | 152 | 0.14470649203455105 | 1.7286068356794275
19 | 19 | 3770 | 2820 | 102 | 3197 | 0.3481679377130594 | 1.8733133277139786
20 | 20 | 3770 | 2820 | 2614 | 2934 | 0.04667553806973164 | 2.221481265427038
21 | 21 | 3770 | 2820 | 2408 | 3181 | 0.00899158527654532 | 2.2681568034967694
22 | 22 | 3770 | 2820 | 2600 | 3180 | 0.08096185561495212 | 2.277148388773315
23 | 23 | 3770 | 2820 | 328 | 4121 | 0.06976010852831643 | 2.358110244388267
24 | 24 | 3770 | 2820 | 3431 | 4122 | 0.04489412790369416 | 2.4278703529165835
25 | 25 | 3770 | 2820 | 200 | 229 | 0.2046027065666068 | 2.4727644808202776
26 | 26 | 3770 | 2820 | 151 | 230 | 0.1878673019274676 | 2.6773671873868845
27 | 27 | 3770 | 2820 | 3422 | 6113 | 0.045467491630725346 | 2.865234489314352
28 | 28 | 3770 | 2820 | 5227 | 6112 | 0.12720533331467954 | 2.9107019809450776
29 | 29 | 3770 | 2820 | 2440 | 2981 | 0.016757090436877432 | 3.037907314259757
30 | 30 | 3770 | 2820 | 2418 | 2951 | 0.2076963543435805 | 3.0546644046966343
31 | 31 | 3770 | 2820 | 2414 | 2954 | 0.023262786401622885 | 3.262360759040215
32 | 32 | 3770 | 2820 | 2421 | 3419 | 0.05477197321088515 | 3.2856235454418377
33 | 33 | 3770 | 2820 | 2820 | -1 | 0 | 3.340395518652723
(33 rows)
6.2.4. Exercise: 4 (Chapter: Vehicle)¶
Exercise 4: Vehicle routing without penalization
ALTER TABLE
UPDATE 36
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+--------------------+--------------------
1 | 1 | 2820 | 3770 | 2820 | 3419 | 1.9717910355918655 | 0
2 | 2 | 2820 | 3770 | 2421 | 2954 | 0.8374603104584238 | 1.9717910355918655
3 | 3 | 2820 | 3770 | 2414 | 2951 | 7.477068756368898 | 2.8092513460502895
4 | 4 | 2820 | 3770 | 2418 | 2981 | 0.6032552557275876 | 10.286320102419188
5 | 5 | 2820 | 3770 | 2440 | 6112 | 4.579391999328463 | 10.889575358146775
6 | 6 | 2820 | 3770 | 5227 | 6113 | 1.6368296987061124 | 15.468967357475238
7 | 7 | 2820 | 3770 | 3422 | 230 | 6.763222869388833 | 17.105797056181352
8 | 8 | 2820 | 3770 | 151 | 229 | 7.365697436397846 | 23.869019925570186
9 | 9 | 2820 | 3770 | 200 | 4122 | 1.6161886045329898 | 31.23471736196803
10 | 10 | 2820 | 3770 | 3431 | 169 | 0.8930265956799612 | 32.85090596650102
11 | 11 | 2820 | 3770 | 113 | 170 | 3.7907100751592884 | 33.74393256218098
12 | 12 | 2820 | 3770 | 5150 | 6028 | 7.009794962505772 | 37.53464263734027
13 | 13 | 2820 | 3770 | 2401 | 6026 | 0.4776263506398542 | 44.54443759984604
14 | 14 | 2820 | 3770 | 5148 | 3412 | 1.903546386183266 | 45.022063950485894
15 | 15 | 2820 | 3770 | 2788 | 2924 | 4.216002057126904 | 46.92561033666916
16 | 16 | 2820 | 3770 | 2403 | 2926 | 4.551438202679301 | 51.141612393796066
17 | 17 | 2820 | 3770 | 2404 | 2927 | 6.415982638613019 | 55.693050596475366
18 | 18 | 2820 | 3770 | 4303 | 5106 | 2.638711888543413 | 62.10903323508838
19 | 19 | 2820 | 3770 | 3492 | 4193 | 3.863628802719188 | 64.7477451236318
20 | 20 | 2820 | 3770 | 3753 | 4477 | 2.347570348121095 | 68.61137392635099
21 | 21 | 2820 | 3770 | 1459 | 1851 | 0.3815494186305657 | 70.95894427447209
22 | 22 | 2820 | 3770 | 3838 | 1850 | 12.951224982492866 | 71.34049369310266
23 | 23 | 2820 | 3770 | 1458 | 5652 | 0.6041098049118452 | 84.29171867559552
24 | 24 | 2820 | 3770 | 4790 | 23 | 14.631724542165756 | 84.89582848050736
25 | 25 | 2820 | 3770 | 17 | 5685 | 0.5243351200651923 | 99.52755302267312
26 | 26 | 2820 | 3770 | 4817 | 1723 | 0.1704737378950465 | 100.0518881427383
27 | 27 | 2820 | 3770 | 1363 | 38 | 4.598848475012302 | 100.22236188063336
28 | 28 | 2820 | 3770 | 29 | 6032 | 0.8191063049830252 | 104.82121035564566
29 | 29 | 2820 | 3770 | 5154 | 6033 | 1.4709154497201207 | 105.64031666062868
30 | 30 | 2820 | 3770 | 5153 | 6871 | 1.950117899495084 | 107.11123211034881
31 | 31 | 2820 | 3770 | 5932 | 4522 | 4.835896854213082 | 109.06135000984389
32 | 32 | 2820 | 3770 | 3792 | 4503 | 2.111420490975464 | 113.89724686405697
33 | 33 | 2820 | 3770 | 3779 | 7281 | 6.833666799771051 | 116.00866735503243
34 | 34 | 2820 | 3770 | 6331 | 7282 | 3.21206769889556 | 122.84233415480348
35 | 35 | 2820 | 3770 | 3776 | 4500 | 0.5020075017677136 | 126.05440185369905
36 | 36 | 2820 | 3770 | 3770 | -1 | 0 | 126.55640935546676
(36 rows)
6.2.5. Exercise: 5 (Chapter: Vehicle)¶
Exercise 5: Vehicle routing with penalization
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+---------------------+--------------------
1 | 1 | 2820 | 3770 | 2820 | 3420 | 7.206962194991218 | 0
2 | 2 | 2820 | 3770 | 2823 | 3447 | 1.8970908836902314 | 7.206962194991218
3 | 3 | 2820 | 3770 | 2819 | 3397 | 0.8114788258878514 | 9.10405307868145
4 | 4 | 2820 | 3770 | 103 | 2915 | 3.5439837273641 | 9.915531904569301
5 | 5 | 2820 | 3770 | 2397 | 2917 | 4.58808940517601 | 13.459515631933401
6 | 6 | 2820 | 3770 | 2398 | 1153 | 0.4866873333836305 | 18.047605037109413
7 | 7 | 2820 | 3770 | 909 | 2930 | 2.630472187038858 | 18.534292370493045
8 | 8 | 2820 | 3770 | 2406 | 2931 | 0.48626098583812993 | 21.164764557531903
9 | 9 | 2820 | 3770 | 2592 | 3169 | 0.721839323232583 | 21.651025543370032
10 | 10 | 2820 | 3770 | 4213 | 4989 | 3.7405886037445413 | 22.372864866602615
11 | 11 | 2820 | 3770 | 4211 | 4987 | 0.2978735299020452 | 26.113453470347157
12 | 12 | 2820 | 3770 | 4208 | 4983 | 2.8139192865005347 | 26.4113270002492
13 | 13 | 2820 | 3770 | 2591 | 4410 | 2.4099569612778935 | 29.225246286749737
14 | 14 | 2820 | 3770 | 3696 | 1666 | 0.5646137765280664 | 31.63520324802763
15 | 15 | 2820 | 3770 | 1321 | 2948 | 0.38273552188958043 | 32.1998170245557
16 | 16 | 2820 | 3770 | 2417 | 2959 | 1.2409251393914407 | 32.58255254644528
17 | 17 | 2820 | 3770 | 2424 | 7256 | 2.546625001916182 | 33.82347768583672
18 | 18 | 2820 | 3770 | 6308 | 2896 | 2.6304075630210377 | 36.370102687752905
19 | 19 | 2820 | 3770 | 2381 | 245 | 0.11432300810626747 | 39.00051025077394
20 | 20 | 2820 | 3770 | 167 | 249 | 0.4481261328132668 | 39.11483325888021
21 | 21 | 2820 | 3770 | 171 | 248 | 0.15206642390995265 | 39.562959391693475
22 | 22 | 2820 | 3770 | 170 | 7356 | 0.9828759413814041 | 39.715025815603425
23 | 23 | 2820 | 3770 | 2377 | 2890 | 1.7346731110618834 | 40.69790175698483
24 | 24 | 2820 | 3770 | 3081 | 3694 | 0.13855562345754494 | 42.43257486804671
25 | 25 | 2820 | 3770 | 2613 | 3195 | 1.4635276610936072 | 42.57113049150425
26 | 26 | 2820 | 3770 | 1327 | 1746 | 0.7195136930719923 | 44.034658152597856
27 | 27 | 2820 | 3770 | 1380 | 6065 | 0.33736276385308556 | 44.75417184566985
28 | 28 | 2820 | 3770 | 5185 | 2901 | 0.395256477134105 | 45.09153460952294
29 | 29 | 2820 | 3770 | 2384 | 5067 | 0.9994665254527622 | 45.48679108665704
30 | 30 | 2820 | 3770 | 4269 | 4656 | 1.114245444010365 | 46.4862576121098
31 | 31 | 2820 | 3770 | 3907 | 6572 | 0.08010330792578281 | 47.60050305612017
32 | 32 | 2820 | 3770 | 5658 | 27 | 0.5906672444527908 | 47.68060636404595
33 | 33 | 2820 | 3770 | 20 | 7338 | 0.5092577090362062 | 48.27127360849874
34 | 34 | 2820 | 3770 | 38 | 209 | 4.11261565388657 | 48.780531317534944
35 | 35 | 2820 | 3770 | 137 | 25 | 0.4236145374284703 | 52.893146971421515
36 | 36 | 2820 | 3770 | 19 | 63 | 1.0507974431010978 | 53.316761508849986
37 | 37 | 2820 | 3770 | 49 | 62 | 1.845842809305115 | 54.36755895195108
38 | 38 | 2820 | 3770 | 48 | 24 | 1.541336449541437 | 56.2134017612562
39 | 39 | 2820 | 3770 | 18 | 2493 | 5.061164208688517 | 57.75473821079764
40 | 40 | 2820 | 3770 | 2013 | 5512 | 1.6286533206298321 | 62.81590241948616
41 | 41 | 2820 | 3770 | 4667 | 7099 | 0.4369804353398344 | 64.44455574011599
42 | 42 | 2820 | 3770 | 6154 | 5511 | 0.43302387387076 | 64.88153617545582
43 | 43 | 2820 | 3770 | 4666 | 7156 | 1.8514953912984633 | 65.31456004932659
44 | 44 | 2820 | 3770 | 6200 | 3374 | 0.28143393615345996 | 67.16605544062504
45 | 45 | 2820 | 3770 | 2753 | 7214 | 0.9529503504291313 | 67.4474893767785
46 | 46 | 2820 | 3770 | 6258 | 6622 | 3.4432196322260147 | 68.40043972720764
47 | 47 | 2820 | 3770 | 5704 | 7158 | 1.123782657330997 | 71.84365935943366
48 | 48 | 2820 | 3770 | 6202 | 3044 | 0.7743956414358557 | 72.96744201676465
49 | 49 | 2820 | 3770 | 2494 | 243 | 0.4344432802284327 | 73.74183765820051
50 | 50 | 2820 | 3770 | 165 | 3031 | 0.5848680525756187 | 74.17628093842895
51 | 51 | 2820 | 3770 | 2487 | 215 | 0.5696166411603891 | 74.76114899100456
52 | 52 | 2820 | 3770 | 163 | 1690 | 0.34614990970942944 | 75.33076563216495
53 | 53 | 2820 | 3770 | 1340 | 6968 | 0.672779617745295 | 75.67691554187438
54 | 54 | 2820 | 3770 | 6021 | 6967 | 0.5708133830309033 | 76.34969515961967
55 | 55 | 2820 | 3770 | 6020 | 3028 | 1.5847930188993133 | 76.92050854265058
56 | 56 | 2820 | 3770 | 2485 | 5399 | 0.7864921974425638 | 78.5053015615499
57 | 57 | 2820 | 3770 | 4572 | 162 | 0.3853140375024063 | 79.29179375899247
58 | 58 | 2820 | 3770 | 162 | 4377 | 0.5523204714544689 | 79.67710779649488
59 | 59 | 2820 | 3770 | 3665 | 4393 | 0.18709230408291003 | 80.22942826794934
60 | 60 | 2820 | 3770 | 3683 | 4995 | 2.1509892873074583 | 80.41652057203225
61 | 61 | 2820 | 3770 | 4219 | 1735 | 0.8995462673505799 | 82.56750985933971
62 | 62 | 2820 | 3770 | 1373 | 3361 | 1.216906003945362 | 83.4670561266903
63 | 63 | 2820 | 3770 | 2742 | 184 | 0.2369033845510244 | 84.68396213063566
64 | 64 | 2820 | 3770 | 121 | 4383 | 1.146593722717297 | 84.92086551518669
65 | 65 | 2820 | 3770 | 3675 | 174 | 0.3947658355393937 | 86.06745923790399
66 | 66 | 2820 | 3770 | 115 | 3356 | 1.74702399364692 | 86.46222507344338
67 | 67 | 2820 | 3770 | 2739 | 286 | 1.1083655579246459 | 88.2092490670903
68 | 68 | 2820 | 3770 | 194 | 7359 | 1.362821191723194 | 89.31761462501494
69 | 69 | 2820 | 3770 | 2484 | 283 | 1.9425850940894076 | 90.68043581673814
70 | 70 | 2820 | 3770 | 192 | 284 | 0.16501136878173142 | 92.62302091082755
71 | 71 | 2820 | 3770 | 193 | 5732 | 1.2683470617644983 | 92.78803227960928
72 | 72 | 2820 | 3770 | 4864 | 3348 | 0.577803792546957 | 94.05637934137378
73 | 73 | 2820 | 3770 | 2735 | 5733 | 1.8878202450951556 | 94.63418313392074
74 | 74 | 2820 | 3770 | 4867 | 6506 | 0.49693761827909455 | 96.5220033790159
75 | 75 | 2820 | 3770 | 5614 | 1726 | 0.31244189418655005 | 97.01894099729499
76 | 76 | 2820 | 3770 | 1365 | 3050 | 2.430102583205955 | 97.33138289148154
77 | 77 | 2820 | 3770 | 2497 | 65 | 0.15186470557682377 | 99.76148547468749
78 | 78 | 2820 | 3770 | 50 | 1724 | 0.37974182296189646 | 99.91335018026432
79 | 79 | 2820 | 3770 | 1364 | 1725 | 0.8049802702152841 | 100.2930920032262
80 | 80 | 2820 | 3770 | 6141 | 70 | 0.20042295357567141 | 101.09807227344149
81 | 81 | 2820 | 3770 | 52 | 6965 | 0.42070862663487035 | 101.29849522701716
82 | 82 | 2820 | 3770 | 6019 | 1490 | 5.573730408455535 | 101.71920385365203
83 | 83 | 2820 | 3770 | 1187 | 1495 | 6.187373969390731 | 107.29293426210756
84 | 84 | 2820 | 3770 | 1191 | 47 | 6.984103391281582 | 113.4803082314983
85 | 85 | 2820 | 3770 | 36 | 1798 | 5.2926633625667305 | 120.46441162277988
86 | 86 | 2820 | 3770 | 1420 | 1484 | 2.3470703229242895 | 125.7570749853466
87 | 87 | 2820 | 3770 | 1182 | 1579 | 3.0657080856801153 | 128.1041453082709
88 | 88 | 2820 | 3770 | 1255 | 1580 | 3.944529992897953 | 131.169853393951
89 | 89 | 2820 | 3770 | 2458 | 5273 | 4.290678790638005 | 135.11438338684894
90 | 90 | 2820 | 3770 | 4475 | 1626 | 2.7304305131392246 | 139.40506217748694
91 | 91 | 2820 | 3770 | 1292 | 1614 | 0.4667641066653388 | 142.13549269062617
92 | 92 | 2820 | 3770 | 1281 | 1625 | 4.954170085344215 | 142.60225679729152
93 | 93 | 2820 | 3770 | 1291 | 1618 | 0.9087420830704018 | 147.55642688263572
94 | 94 | 2820 | 3770 | 1285 | 4537 | 3.2114745444347443 | 148.4651689657061
95 | 95 | 2820 | 3770 | 3806 | 7106 | 0.766301788571003 | 151.67664351014085
96 | 96 | 2820 | 3770 | 6158 | 5850 | 2.834731235138574 | 152.44294529871186
97 | 97 | 2820 | 3770 | 4983 | 4493 | 10.056224877551259 | 155.27767653385044
98 | 98 | 2820 | 3770 | 3770 | -1 | 0 | 165.3339014114017
(98 rows)
6.3. Solutions to SQL function¶
6.3.1. Exercise: 1 (Chapter: SQL)¶
Exercise 1: Creating a view for routing
CREATE VIEW
count
-------
7829
(1 row)
count
-------
7326
(1 row)
6.3.2. Exercise: 2 (Chapter: SQL)¶
Exercise 2: Limiting the road network within an area
CREATE VIEW
count
-------
1817
(1 row)
6.3.3. Exercise: 3 (Chapter: SQL)¶
Exercise 3: Creating a materialized view for routing pedestrians
SELECT 7235
count
-------
7235
(1 row)
6.3.4. Exercise: 4 (Chapter: SQL)¶
Exercise 4: Testing the views for routing
seq | id | seconds
-----+------+---------------------
1 | 5644 | 2.4814640908756367
2 | 4478 | 0.39466494779291583
3 | 28 | 0.3411414239505941
4 | 29 | 0.13774865546252965
5 | 23 | 14.631724542165756
6 | 5685 | 0.5243351200651923
7 | 1723 | 0.1704737378950465
8 | 38 | 4.598848475012302
9 | 6032 | 0.8191063049830252
10 | 6033 | 1.4709154497201207
11 | 6871 | 1.950117899495084
12 | 4522 | 4.835896854213082
13 | 4503 | 2.111420490975464
14 | 7281 | 6.833666799771051
15 | 7282 | 3.21206769889556
16 | 4500 | 0.5020075017677136
17 | -1 | 0
(17 rows)
seq | id | seconds
-----+----+---------
(0 rows)
seq | id | seconds
-----+------+--------------------
1 | 4565 | 24.259723823069486
2 | 4285 | 18.62484046026228
3 | 7337 | 22.236985620177713
4 | 7365 | 13.424144008682614
5 | 3199 | 32.09841522116294
6 | 3200 | 13.474794579303445
7 | 7081 | 13.61868912202662
8 | 7079 | 2.534343982860758
9 | 7363 | 34.58996374919731
10 | 7398 | 17.16950898760336
11 | 7364 | 2.5552596343623692
12 | 7089 | 45.794051703954004
13 | 1723 | 1.183845402048934
14 | 38 | 12.774579097256394
15 | 6032 | 5.688238229048786
16 | 6033 | 4.085876249222557
17 | 6871 | 13.542485413160307
18 | 4522 | 33.5826170431464
19 | 4503 | 14.662642298440723
20 | 7281 | 47.456019442854526
21 | 7282 | 22.30602568677472
22 | 4500 | 3.4861632067202333
23 | -1 | 0
(23 rows)
6.3.5. Exercise: 5 (Chapter: SQL)¶
Exercise 5: Get additional information
seq | id | seconds | name | length_m
-----+------+---------------------+------------------+--------------------
1 | 5644 | 2.4814640908756367 | Mehmet Pasha | 27.571823231951516
2 | 4478 | 0.39466494779291583 | Mehmet Pasha | 4.385166086587954
3 | 28 | 0.3411414239505941 | Mehmet Pasha | 3.7904602661177123
4 | 29 | 0.13774865546252965 | | 1.9131757703129117
5 | 23 | 14.631724542165756 | | 203.21839641896884
6 | 5685 | 0.5243351200651923 | | 7.282432223127671
7 | 1723 | 0.1704737378950465 | | 2.367690804097868
8 | 38 | 4.598848475012302 | Ura e Arastës | 25.54915819451279
9 | 6032 | 0.8191063049830252 | | 11.376476458097573
10 | 6033 | 1.4709154497201207 | Vatrat Shqiptare | 8.171752498445114
11 | 6871 | 1.950117899495084 | Gasper Karaqi | 27.084970826320614
12 | 4522 | 4.835896854213082 | Gasper Karaqi | 67.1652340862928
13 | 4503 | 2.111420490975464 | Zita Bashota | 29.325284596881446
14 | 7281 | 6.833666799771051 | Nuri Serifi | 94.91203888570905
15 | 7282 | 3.21206769889556 | | 44.61205137354944
16 | 4500 | 0.5020075017677136 | Shën Flori | 6.9723264134404666
17 | -1 | 0 | |
(17 rows)
6.3.6. Exercise: 6 (Chapter: SQL)¶
Exercise 6: Route geometry (human readable)
seq | id | seconds | route_readable
-----+------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | 5644 | 2.4814640908756367 | LINESTRING(20.7432037 42.2109494,20.7432788 42.2109494,20.7433673 42.2109255,20.7434397 42.2108957,20.7434853 42.2108719,20.7435087 42.2108632)
2 | 4478 | 0.39466494779291583 | LINESTRING(20.7435087 42.2108632,20.7435561 42.2108454)
3 | 28 | 0.3411414239505941 | LINESTRING(20.7435561 42.2108454,20.7435965 42.2108292)
4 | 29 | 0.13774865546252965 | LINESTRING(20.7436179 42.2108226,20.7435965 42.2108292)
5 | 23 | 14.631724542165756 | LINESTRING(20.7436179 42.2108226,20.743432 42.2106206,20.7432301 42.2104375,20.7429588 42.2102273,20.7426248 42.2100324,20.7423608 42.2099184,20.7420331 42.2098229,20.7418578 42.2097892,20.7417003 42.2097764)
6 | 5685 | 0.5243351200651923 | LINESTRING(20.7417003 42.2097764,20.7417138 42.2097405,20.7417215 42.2097128)
7 | 1723 | 0.1704737378950465 | LINESTRING(20.7417215 42.2097128,20.7417226 42.2096915)
8 | 38 | 4.598848475012302 | LINESTRING(20.7417226 42.2096915,20.7417194 42.2094615)
9 | 6032 | 0.8191063049830252 | LINESTRING(20.7417194 42.2094615,20.7417357 42.2093598)
10 | 6033 | 1.4709154497201207 | LINESTRING(20.7418331 42.2093728,20.7417357 42.2093598)
11 | 6871 | 1.950117899495084 | LINESTRING(20.7418331 42.2093728,20.7419311 42.2091401)
12 | 4522 | 4.835896854213082 | LINESTRING(20.7419311 42.2091401,20.7420182 42.2090345,20.7422441 42.2088228,20.7424244 42.2086604)
13 | 4503 | 2.111420490975464 | LINESTRING(20.7424244 42.2086604,20.7426375 42.2087652,20.7427163 42.2088105)
14 | 7281 | 6.833666799771051 | LINESTRING(20.7427163 42.2088105,20.7429218 42.2087004,20.7429952 42.2086796,20.7430534 42.2086529,20.743078 42.2086274,20.7431343 42.2085728,20.7432579 42.2084863,20.7432909 42.2084688,20.7433449 42.2084625,20.7433955 42.2084641,20.743424 42.2084725,20.7434558 42.2085028,20.7434696 42.208542,20.7434838 42.2085655,20.7435155 42.2085823,20.743597 42.2086076)
15 | 7282 | 3.21206769889556 | LINESTRING(20.7438231 42.2082437,20.7437857 42.2083032,20.7437455 42.2083588,20.7437026 42.2084253,20.7436758 42.2084571,20.7436449 42.2085108,20.7436118 42.2085768,20.743597 42.2086076)
16 | 4500 | 0.5020075017677136 | LINESTRING(20.7437399 42.208235,20.7437911 42.2082373,20.7438231 42.2082437)
17 | -1 | 0 |
(17 rows)
6.3.7. Exercise: 7 (Chapter: SQL)¶
Exercise 7: Route geometry (binary format)
seq | id | seconds | route_geom
-----+------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | 5644 | 2.4814640908756367 | 0102000020E61000000600000025C4019942BE34402F0ED363001B454064CFF98447BE34402F0ED363001B4540736AC2514DBE34403C32569BFF1A45404D0C6E1052BE3440FB2E5BA1FE1A4540878E780D55BE34409C12B5D9FD1A45409ECA0E9656BE344007F7B990FD1A4540
2 | 4478 | 0.39466494779291583 | 0102000020E6100000020000009ECA0E9656BE344007F7B990FD1A4540C63D4CB159BE34408AC168FBFC1A4540
3 | 28 | 0.3411414239505941 | 0102000020E610000002000000C63D4CB159BE34408AC168FBFC1A454089EB18575CBE34405B858373FC1A4540
4 | 29 | 0.13774865546252965 | 0102000020E6100000020000005F3821BE5DBE3440FC20263CFC1A454089EB18575CBE34405B858373FC1A4540
5 | 23 | 14.631724542165756 | 0102000020E6100000090000005F3821BE5DBE3440FC20263CFC1A4540C4CC3E8F51BE3440946EA69DF51A45401FE7EC5344BE34400E2DB29DEF1A45408146448C32BE3440FA1C69BAE81A4540D432ADA81CBE3440DA8C7857E21A454018D57D5B0BBE3440116A2B9BDE1A4540AC0C99E1F5BD3440D7B90E7ADB1A454052488D64EABD3440EA865C5FDA1A4540F3EE2312E0BD34407FBCFCF3D91A4540
6 | 5685 | 0.5243351200651923 | 0102000020E610000003000000F3EE2312E0BD34407FBCFCF3D91A4540ECFDA1F4E0BD3440C712D6C6D81A4540753DD175E1BD3440BBC678DED71A4540
7 | 1723 | 0.1704737378950465 | 0102000020E610000002000000753DD175E1BD3440BBC678DED71A45403FB44588E1BD3440E55FCB2BD71A4540
8 | 38 | 4.598848475012302 | 0102000020E6100000020000003FB44588E1BD3440E55FCB2BD71A45400ACF9552E1BD3440B4226AA2CF1A4540
9 | 6032 | 0.8191063049830252 | 0102000020E6100000020000000ACF9552E1BD3440B4226AA2CF1A454092C60D64E2BD34406E0C4B4DCC1A4540
10 | 6033 | 1.4709154497201207 | 0102000020E6100000020000001E9727C6E8BD3440025658BACC1A454092C60D64E2BD34406E0C4B4DCC1A4540
11 | 6871 | 1.950117899495084 | 0102000020E6100000020000001E9727C6E8BD3440025658BACC1A4540A3625232EFBD34401EE4501AC51A4540
12 | 4522 | 4.835896854213082 | 0102000020E610000004000000A3625232EFBD34401EE4501AC51A45406B099EE7F4BD34402C9E7AA4C11A4540222697B503BE34405F549CB4BA1A45409FC085860FBE344032FC4C62B51A4540
13 | 4503 | 2.111420490975464 | 0102000020E6100000030000009FC085860FBE344032FC4C62B51A45408048BF7D1DBE34407E456DD1B81A4540C5B4CAA722BE3440DC476E4DBA1A4540
14 | 7281 | 6.833666799771051 | 0102000020E610000010000000C5B4CAA722BE3440DC476E4DBA1A4540477C821F30BE3440C054D8B1B61A4540C115F5EE34BE3440D3AB5C03B61A45407E2E64BF38BE344097DB6223B51A454089601C5C3ABE344056067A4DB41A45402E09AB0C3EBE3440E66A7583B21A45405FFE542646BE3440FF14D8ADAF1A454017EAFA4F48BE3440411E0B1BAF1A4540FD25F3D94BBE3440A1F831E6AE1A45405A7EE02A4FBE3440EEF19DF3AE1A4540BF0F070951BE3440C4CE143AAF1A454082058B1E53BE3440ED8F4138B01A4540F991110654BE3440D5EB1681B11A4540161B4EF454BE344075C93846B21A4540B091240857BE3440228326D3B21A45405A677C5F5CBE344039D961A7B31A4540
15 | 7282 | 3.21206769889556 | 0102000020E6100000080000006482D0306BBE34408C8CC4BAA71A454083BB58BD68BE34407AD3E3ADA91A4540130CE71A66BE3440BAEA4B80AB1A45403EF3284B63BE34405A9423AEAD1A45409ED3878961BE34403B8FE5B8AE1A454052561D835FBE3440706E5D7BB01A454071EBC9575DBE3440285A03A5B21A45405A677C5F5CBE344039D961A7B31A4540
16 | 4500 | 0.5020075017677136 | 0102000020E610000003000000F63AF3BC65BE3440F870C971A71A45404D8EF11769BE344057A71485A71A45406482D0306BBE34408C8CC4BAA71A4540
17 | -1 | 0 |
(17 rows)
6.3.8. Exercise: 8 (Chapter: SQL)¶
Exercise 8: Route geometry directionality
seq | id | seconds | route_readable | route_geom
-----+------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | 5644 | 2.4814640908756367 | LINESTRING(20.7432037 42.2109494,20.7432788 42.2109494,20.7433673 42.2109255,20.7434397 42.2108957,20.7434853 42.2108719,20.7435087 42.2108632) | 0102000020E61000000600000025C4019942BE34402F0ED363001B454064CFF98447BE34402F0ED363001B4540736AC2514DBE34403C32569BFF1A45404D0C6E1052BE3440FB2E5BA1FE1A4540878E780D55BE34409C12B5D9FD1A45409ECA0E9656BE344007F7B990FD1A4540
2 | 4478 | 0.39466494779291583 | LINESTRING(20.7435087 42.2108632,20.7435561 42.2108454) | 0102000020E6100000020000009ECA0E9656BE344007F7B990FD1A4540C63D4CB159BE34408AC168FBFC1A4540
3 | 28 | 0.3411414239505941 | LINESTRING(20.7435561 42.2108454,20.7435965 42.2108292) | 0102000020E610000002000000C63D4CB159BE34408AC168FBFC1A454089EB18575CBE34405B858373FC1A4540
4 | 29 | 0.13774865546252965 | LINESTRING(20.7435965 42.2108292,20.7436179 42.2108226) | 0102000020E61000000200000089EB18575CBE34405B858373FC1A45405F3821BE5DBE3440FC20263CFC1A4540
5 | 23 | 14.631724542165756 | LINESTRING(20.7436179 42.2108226,20.743432 42.2106206,20.7432301 42.2104375,20.7429588 42.2102273,20.7426248 42.2100324,20.7423608 42.2099184,20.7420331 42.2098229,20.7418578 42.2097892,20.7417003 42.2097764) | 0102000020E6100000090000005F3821BE5DBE3440FC20263CFC1A4540C4CC3E8F51BE3440946EA69DF51A45401FE7EC5344BE34400E2DB29DEF1A45408146448C32BE3440FA1C69BAE81A4540D432ADA81CBE3440DA8C7857E21A454018D57D5B0BBE3440116A2B9BDE1A4540AC0C99E1F5BD3440D7B90E7ADB1A454052488D64EABD3440EA865C5FDA1A4540F3EE2312E0BD34407FBCFCF3D91A4540
6 | 5685 | 0.5243351200651923 | LINESTRING(20.7417003 42.2097764,20.7417138 42.2097405,20.7417215 42.2097128) | 0102000020E610000003000000F3EE2312E0BD34407FBCFCF3D91A4540ECFDA1F4E0BD3440C712D6C6D81A4540753DD175E1BD3440BBC678DED71A4540
7 | 1723 | 0.1704737378950465 | LINESTRING(20.7417215 42.2097128,20.7417226 42.2096915) | 0102000020E610000002000000753DD175E1BD3440BBC678DED71A45403FB44588E1BD3440E55FCB2BD71A4540
8 | 38 | 4.598848475012302 | LINESTRING(20.7417226 42.2096915,20.7417194 42.2094615) | 0102000020E6100000020000003FB44588E1BD3440E55FCB2BD71A45400ACF9552E1BD3440B4226AA2CF1A4540
9 | 6032 | 0.8191063049830252 | LINESTRING(20.7417194 42.2094615,20.7417357 42.2093598) | 0102000020E6100000020000000ACF9552E1BD3440B4226AA2CF1A454092C60D64E2BD34406E0C4B4DCC1A4540
10 | 6033 | 1.4709154497201207 | LINESTRING(20.7417357 42.2093598,20.7418331 42.2093728) | 0102000020E61000000200000092C60D64E2BD34406E0C4B4DCC1A45401E9727C6E8BD3440025658BACC1A4540
11 | 6871 | 1.950117899495084 | LINESTRING(20.7418331 42.2093728,20.7419311 42.2091401) | 0102000020E6100000020000001E9727C6E8BD3440025658BACC1A4540A3625232EFBD34401EE4501AC51A4540
12 | 4522 | 4.835896854213082 | LINESTRING(20.7419311 42.2091401,20.7420182 42.2090345,20.7422441 42.2088228,20.7424244 42.2086604) | 0102000020E610000004000000A3625232EFBD34401EE4501AC51A45406B099EE7F4BD34402C9E7AA4C11A4540222697B503BE34405F549CB4BA1A45409FC085860FBE344032FC4C62B51A4540
13 | 4503 | 2.111420490975464 | LINESTRING(20.7424244 42.2086604,20.7426375 42.2087652,20.7427163 42.2088105) | 0102000020E6100000030000009FC085860FBE344032FC4C62B51A45408048BF7D1DBE34407E456DD1B81A4540C5B4CAA722BE3440DC476E4DBA1A4540
14 | 7281 | 6.833666799771051 | LINESTRING(20.7427163 42.2088105,20.7429218 42.2087004,20.7429952 42.2086796,20.7430534 42.2086529,20.743078 42.2086274,20.7431343 42.2085728,20.7432579 42.2084863,20.7432909 42.2084688,20.7433449 42.2084625,20.7433955 42.2084641,20.743424 42.2084725,20.7434558 42.2085028,20.7434696 42.208542,20.7434838 42.2085655,20.7435155 42.2085823,20.743597 42.2086076) | 0102000020E610000010000000C5B4CAA722BE3440DC476E4DBA1A4540477C821F30BE3440C054D8B1B61A4540C115F5EE34BE3440D3AB5C03B61A45407E2E64BF38BE344097DB6223B51A454089601C5C3ABE344056067A4DB41A45402E09AB0C3EBE3440E66A7583B21A45405FFE542646BE3440FF14D8ADAF1A454017EAFA4F48BE3440411E0B1BAF1A4540FD25F3D94BBE3440A1F831E6AE1A45405A7EE02A4FBE3440EEF19DF3AE1A4540BF0F070951BE3440C4CE143AAF1A454082058B1E53BE3440ED8F4138B01A4540F991110654BE3440D5EB1681B11A4540161B4EF454BE344075C93846B21A4540B091240857BE3440228326D3B21A45405A677C5F5CBE344039D961A7B31A4540
15 | 7282 | 3.21206769889556 | LINESTRING(20.743597 42.2086076,20.7436118 42.2085768,20.7436449 42.2085108,20.7436758 42.2084571,20.7437026 42.2084253,20.7437455 42.2083588,20.7437857 42.2083032,20.7438231 42.2082437) | 0102000020E6100000080000005A677C5F5CBE344039D961A7B31A454071EBC9575DBE3440285A03A5B21A454052561D835FBE3440706E5D7BB01A45409ED3878961BE34403B8FE5B8AE1A45403EF3284B63BE34405A9423AEAD1A4540130CE71A66BE3440BAEA4B80AB1A454083BB58BD68BE34407AD3E3ADA91A45406482D0306BBE34408C8CC4BAA71A4540
16 | 4500 | 0.5020075017677136 | LINESTRING(20.7438231 42.2082437,20.7437911 42.2082373,20.7437399 42.208235) | 0102000020E6100000030000006482D0306BBE34408C8CC4BAA71A45404D8EF11769BE344057A71485A71A4540F63AF3BC65BE3440F870C971A71A4540
17 | -1 | 0 | |
(17 rows)
6.3.9. Exercise: 9 (Chapter: SQL)¶
Exercise 9: Using the geometry
seq | id | seconds | azimuth
-----+------+---------------------+--------------------
1 | 5644 | 2.4814640908756367 | 285.78150965916143
2 | 4478 | 0.39466494779291583 | 290.58253425341195
3 | 28 | 0.3411414239505941 | 291.85029665547216
4 | 29 | 0.13774865546252965 | 107.14034930118599
5 | 23 | 14.631724542165756 | 61.38409770812769
6 | 5685 | 0.5243351200651923 | 341.5650511789983
7 | 1723 | 0.1704737378950465 | 357.0436893461656
8 | 38 | 4.598848475012302 | 0.7971072409848137
9 | 6032 | 0.8191063049830252 | 350.89434284283374
10 | 6033 | 1.4709154497201207 | 82.39765040955503
11 | 6871 | 1.950117899495084 | 337.1619429422876
12 | 4522 | 4.835896854213082 | 314.19920671883756
13 | 4503 | 2.111420490975464 | 242.78699311189231
14 | 7281 | 6.833666799771051 | 282.97370904587746
15 | 7282 | 3.21206769889556 | 148.14630133307207
16 | 4500 | 0.5020075017677136 | 264.03042925257193
17 | -1 | 0 |
(17 rows)
6.3.10. Exercise: 10 (Chapter: SQL)¶
Exercise 10: Function for an application
CREATE FUNCTION
6.3.11. Exercise: 11 (Chapter: SQL)¶
Exercise 11: Using the function
seq | id | seconds | name | length_m | route_readable | route_geom | azimuth
-----+------+---------------------+------------------+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------
1 | 5644 | 2.4814640908756367 | Mehmet Pasha | 27.571823231951516 | LINESTRING(20.7435087 42.2108632,20.7434853 42.2108719,20.7434397 42.2108957,20.7433673 42.2109255,20.7432788 42.2109494,20.7432037 42.2109494) | 0102000020E61000000600000025C4019942BE34402F0ED363001B454064CFF98447BE34402F0ED363001B4540736AC2514DBE34403C32569BFF1A45404D0C6E1052BE3440FB2E5BA1FE1A4540878E780D55BE34409C12B5D9FD1A45409ECA0E9656BE344007F7B990FD1A4540 | 105.78150965916143
2 | 4478 | 0.39466494779291583 | Mehmet Pasha | 4.385166086587954 | LINESTRING(20.7435561 42.2108454,20.7435087 42.2108632) | 0102000020E6100000020000009ECA0E9656BE344007F7B990FD1A4540C63D4CB159BE34408AC168FBFC1A4540 | 110.58253425341198
3 | 28 | 0.3411414239505941 | Mehmet Pasha | 3.7904602661177123 | LINESTRING(20.7435965 42.2108292,20.7435561 42.2108454) | 0102000020E610000002000000C63D4CB159BE34408AC168FBFC1A454089EB18575CBE34405B858373FC1A4540 | 111.85029665547218
4 | 29 | 0.13774865546252965 | | 1.9131757703129117 | LINESTRING(20.7435965 42.2108292,20.7436179 42.2108226) | 0102000020E61000000200000089EB18575CBE34405B858373FC1A45405F3821BE5DBE3440FC20263CFC1A4540 | 107.14034930118599
5 | 23 | 14.631724542165756 | | 203.21839641896884 | LINESTRING(20.7417003 42.2097764,20.7418578 42.2097892,20.7420331 42.2098229,20.7423608 42.2099184,20.7426248 42.2100324,20.7429588 42.2102273,20.7432301 42.2104375,20.743432 42.2106206,20.7436179 42.2108226) | 0102000020E6100000090000005F3821BE5DBE3440FC20263CFC1A4540C4CC3E8F51BE3440946EA69DF51A45401FE7EC5344BE34400E2DB29DEF1A45408146448C32BE3440FA1C69BAE81A4540D432ADA81CBE3440DA8C7857E21A454018D57D5B0BBE3440116A2B9BDE1A4540AC0C99E1F5BD3440D7B90E7ADB1A454052488D64EABD3440EA865C5FDA1A4540F3EE2312E0BD34407FBCFCF3D91A4540 | 241.38409770812768
6 | 5685 | 0.5243351200651923 | | 7.282432223127671 | LINESTRING(20.7417215 42.2097128,20.7417138 42.2097405,20.7417003 42.2097764) | 0102000020E610000003000000F3EE2312E0BD34407FBCFCF3D91A4540ECFDA1F4E0BD3440C712D6C6D81A4540753DD175E1BD3440BBC678DED71A4540 | 161.56505117899835
7 | 1723 | 0.1704737378950465 | | 2.367690804097868 | LINESTRING(20.7417226 42.2096915,20.7417215 42.2097128) | 0102000020E610000002000000753DD175E1BD3440BBC678DED71A45403FB44588E1BD3440E55FCB2BD71A4540 | 177.04368934616565
8 | 38 | 4.598848475012302 | Ura e Arastës | 25.54915819451279 | LINESTRING(20.7417194 42.2094615,20.7417226 42.2096915) | 0102000020E6100000020000003FB44588E1BD3440E55FCB2BD71A45400ACF9552E1BD3440B4226AA2CF1A4540 | 180.79710724098481
9 | 6032 | 0.8191063049830252 | | 11.376476458097573 | LINESTRING(20.7417357 42.2093598,20.7417194 42.2094615) | 0102000020E6100000020000000ACF9552E1BD3440B4226AA2CF1A454092C60D64E2BD34406E0C4B4DCC1A4540 | 170.89434284283368
10 | 6033 | 1.4709154497201207 | Vatrat Shqiptare | 8.171752498445114 | LINESTRING(20.7417357 42.2093598,20.7418331 42.2093728) | 0102000020E61000000200000092C60D64E2BD34406E0C4B4DCC1A45401E9727C6E8BD3440025658BACC1A4540 | 82.39765040955503
11 | 6871 | 1.950117899495084 | Gasper Karaqi | 27.084970826320614 | LINESTRING(20.7419311 42.2091401,20.7418331 42.2093728) | 0102000020E6100000020000001E9727C6E8BD3440025658BACC1A4540A3625232EFBD34401EE4501AC51A4540 | 157.1619429422876
12 | 4522 | 4.835896854213082 | Gasper Karaqi | 67.1652340862928 | LINESTRING(20.7424244 42.2086604,20.7422441 42.2088228,20.7420182 42.2090345,20.7419311 42.2091401) | 0102000020E610000004000000A3625232EFBD34401EE4501AC51A45406B099EE7F4BD34402C9E7AA4C11A4540222697B503BE34405F549CB4BA1A45409FC085860FBE344032FC4C62B51A4540 | 134.1992067188376
13 | 4503 | 2.111420490975464 | Zita Bashota | 29.325284596881446 | LINESTRING(20.7427163 42.2088105,20.7426375 42.2087652,20.7424244 42.2086604) | 0102000020E6100000030000009FC085860FBE344032FC4C62B51A45408048BF7D1DBE34407E456DD1B81A4540C5B4CAA722BE3440DC476E4DBA1A4540 | 62.78699311189232
14 | 7281 | 6.833666799771051 | Nuri Serifi | 94.91203888570905 | LINESTRING(20.743597 42.2086076,20.7435155 42.2085823,20.7434838 42.2085655,20.7434696 42.208542,20.7434558 42.2085028,20.743424 42.2084725,20.7433955 42.2084641,20.7433449 42.2084625,20.7432909 42.2084688,20.7432579 42.2084863,20.7431343 42.2085728,20.743078 42.2086274,20.7430534 42.2086529,20.7429952 42.2086796,20.7429218 42.2087004,20.7427163 42.2088105) | 0102000020E610000010000000C5B4CAA722BE3440DC476E4DBA1A4540477C821F30BE3440C054D8B1B61A4540C115F5EE34BE3440D3AB5C03B61A45407E2E64BF38BE344097DB6223B51A454089601C5C3ABE344056067A4DB41A45402E09AB0C3EBE3440E66A7583B21A45405FFE542646BE3440FF14D8ADAF1A454017EAFA4F48BE3440411E0B1BAF1A4540FD25F3D94BBE3440A1F831E6AE1A45405A7EE02A4FBE3440EEF19DF3AE1A4540BF0F070951BE3440C4CE143AAF1A454082058B1E53BE3440ED8F4138B01A4540F991110654BE3440D5EB1681B11A4540161B4EF454BE344075C93846B21A4540B091240857BE3440228326D3B21A45405A677C5F5CBE344039D961A7B31A4540 | 102.97370904587748
15 | 7282 | 3.21206769889556 | | 44.61205137354944 | LINESTRING(20.743597 42.2086076,20.7436118 42.2085768,20.7436449 42.2085108,20.7436758 42.2084571,20.7437026 42.2084253,20.7437455 42.2083588,20.7437857 42.2083032,20.7438231 42.2082437) | 0102000020E6100000080000005A677C5F5CBE344039D961A7B31A454071EBC9575DBE3440285A03A5B21A454052561D835FBE3440706E5D7BB01A45409ED3878961BE34403B8FE5B8AE1A45403EF3284B63BE34405A9423AEAD1A4540130CE71A66BE3440BAEA4B80AB1A454083BB58BD68BE34407AD3E3ADA91A45406482D0306BBE34408C8CC4BAA71A4540 | 148.14630133307207
16 | 4500 | 0.5020075017677136 | Shën Flori | 6.9723264134404666 | LINESTRING(20.7438231 42.2082437,20.7437911 42.2082373,20.7437399 42.208235) | 0102000020E6100000030000006482D0306BBE34408C8CC4BAA71A45404D8EF11769BE344057A71485A71A4540F63AF3BC65BE3440F870C971A71A4540 | 264.03042925257193
17 | -1 | 0 | | | | |
(17 rows)
seq | id | seconds | name | length_m | route_readable | route_geom | azimuth
-----+----+---------+------+----------+----------------+------------+---------
(0 rows)
seq | id | seconds | name | length_m | route_readable | route_geom | azimuth
-----+------+--------------------+------------------+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------
1 | 4565 | 24.259723823069486 | Mehmet Pasha | 48.51944764613897 | LINESTRING(20.7432037 42.2109494,20.7431584 42.210994,20.7430993 42.2110556,20.7430308 42.2111176,20.7429376 42.2111848,20.7428133 42.2112741) | 0102000020E61000000600000025C4019942BE34402F0ED363001B454068BFFFA03FBE34407CD3F4D9011B4540352E77C13BBE34409FD1B1DE031B4540B4EB394437BE344015CEC9E6051B4540FFF7962831BE3440C6B4801A081B4540AA882E0329BE3440F4FE9A070B1B4540 | 309.75067210474543
2 | 4285 | 18.62484046026228 | Mehmet Pasha | 37.24968092052456 | LINESTRING(20.7428133 42.2112741,20.742489 42.2115072) | 0102000020E610000002000000AA882E0329BE3440F4FE9A070B1B4540C6A354C213BE34402C6FFDAA121B4540 | 305.70778351855733
3 | 7337 | 22.236985620177713 | Xhevdet Doda | 44.473971240355425 | LINESTRING(20.742489 42.2115072,20.7422053 42.2113778,20.7420197 42.2113118) | 0102000020E610000003000000C6A354C213BE34402C6FFDAA121B4540F96AA22A01BE3440DB0C816D0E1B4540DC7CC800F5BD34402321DB430C1B4540 | 247.39487385002653
4 | 7365 | 13.424144008682614 | Xhevdet Doda | 26.848288017365228 | LINESTRING(20.7420197 42.2113118,20.7420096 42.2113082,20.7417068 42.2112466) | 0102000020E610000003000000DC7CC800F5BD34402321DB430C1B45406B515557F4BD34403530A8250C1B45408738317FE0BD34401332EB200A1B4540 | 258.2295173972831
5 | 3199 | 32.09841522116294 | 12 Qershori | 64.19683044232588 | LINESTRING(20.7409961 42.2110395,20.7410352 42.2110456,20.741082 42.2110688,20.7411732 42.2111324,20.7412215 42.2111483,20.7413207 42.2111681,20.7414356 42.2111856,20.7415502 42.2112104,20.7417068 42.2112466) | 0102000020E6100000090000008738317FE0BD34401332EB200A1B45409E57E13BD6BD34409C4940F1081B4540131736B9CEBD34406DB13621081B45400B598231C7BD3440AFBA698E071B4540929735B1C0BD3440918D51E8061B4540F2ABDE86BDBD34402090F062061B45407EA7C98CB7BD34405D9A6C4D041B4540502F9D7BB4BD34407BFBCE8A031B4540ABF69FEBB1BD34400455A357031B4540 | 253.75372720730564
6 | 3200 | 13.474794579303445 | | 26.94958915860689 | LINESTRING(20.7409961 42.2110395,20.7409027 42.2109255,20.7408927 42.2108645,20.7408705 42.2108401,20.7408484 42.2108322) | 0102000020E610000005000000ABF69FEBB1BD34400455A357031B4540A204A2CCABBD34403C32569BFF1A45405B58DC24ABBD344096B1A19BFD1A4540381268B0A9BD3440BA17F3CEFC1A45403F4BA13DA8BD3440CCF8AD8CFC1A4540 | 215.46964301081258
7 | 7081 | 13.61868912202662 | | 27.23737824405324 | LINESTRING(20.7408484 42.2108322,20.7407371 42.2108307,20.7406466 42.2108138,20.7406211 42.210789,20.7406117 42.2107572,20.7406128 42.2107279) | 0102000020E6100000060000003F4BA13DA8BD3440CCF8AD8CFC1A4540136F53F2A0BD344014BF1880FC1A4540C3E4FC039BBD3440D24554F2FB1A4540403A2B5899BD3440A3AD4A22FB1A4540F38876BA98BD3440C2B28817FA1A4540BEFFEACC98BD3440696DBF21F91A4540 | 246.12105225951376
8 | 7079 | 2.534343982860758 | | 5.068687965721516 | LINESTRING(20.7406128 42.2107279,20.7406151 42.2106823) | 0102000020E610000002000000BEFFEACC98BD3440696DBF21F91A45407C6C81F398BD34404C2C3AA3F71A4540 | 177.11252826554818
9 | 7363 | 34.58996374919731 | Teqeja Halveti | 69.17992749839462 | LINESTRING(20.7406151 42.2106823,20.7406178 42.210611,20.7406232 42.2105236,20.7406312 42.2102395,20.7406271 42.2100597) | 0102000020E6100000050000007C6C81F398BD34404C2C3AA3F71A4540E1D5CD2099BD3440C4961E4DF51A4540ACA8667B99BD3440A284F46FF21A4540B1659E019ABD3440E869C020E91A45400508D5BC99BD344092DA7A3CE31A4540 | 178.89581716262322
10 | 7398 | 17.16950898760336 | Teqeja Halveti | 34.33901797520672 | LINESTRING(20.7406271 42.2100597,20.74062 42.2097506) | 0102000020E6100000020000000508D5BC99BD344092DA7A3CE31A454076C3B64599BD34407FA88F1BD91A4540 | 181.3158476622323
11 | 7364 | 2.5552596343623692 | Teqeja Halveti | 5.1105192687247385 | LINESTRING(20.74062 42.2097506,20.7406188 42.2097046) | 0102000020E61000000200000076C3B64599BD34407FA88F1BD91A454082CD943199BD34400F69AF99D71A4540 | 181.4943335911127
12 | 7089 | 45.794051703954004 | | 91.58810340790801 | LINESTRING(20.7406188 42.2097046,20.7409979 42.2096782,20.7411829 42.2096679,20.7415402 42.2096869,20.7417215 42.2097128) | 0102000020E61000000500000082CD943199BD34400F69AF99D71A454099E7D209B2BD344092D739BCD61A4540BCDA9B29BEBD3440B0C2D265D61A454057AB1B94D5BD344027F33405D71A4540753DD175E1BD3440BBC678DED71A4540 | 89.57393966456881
13 | 1723 | 1.183845402048934 | | 2.367690804097868 | LINESTRING(20.7417226 42.2096915,20.7417215 42.2097128) | 0102000020E610000002000000753DD175E1BD3440BBC678DED71A45403FB44588E1BD3440E55FCB2BD71A4540 | 177.04368934616565
14 | 38 | 12.774579097256394 | Ura e Arastës | 25.54915819451279 | LINESTRING(20.7417194 42.2094615,20.7417226 42.2096915) | 0102000020E6100000020000003FB44588E1BD3440E55FCB2BD71A45400ACF9552E1BD3440B4226AA2CF1A4540 | 180.79710724098481
15 | 6032 | 5.688238229048786 | | 11.376476458097573 | LINESTRING(20.7417357 42.2093598,20.7417194 42.2094615) | 0102000020E6100000020000000ACF9552E1BD3440B4226AA2CF1A454092C60D64E2BD34406E0C4B4DCC1A4540 | 170.89434284283368
16 | 6033 | 4.085876249222557 | Vatrat Shqiptare | 8.171752498445114 | LINESTRING(20.7417357 42.2093598,20.7418331 42.2093728) | 0102000020E61000000200000092C60D64E2BD34406E0C4B4DCC1A45401E9727C6E8BD3440025658BACC1A4540 | 82.39765040955503
17 | 6871 | 13.542485413160307 | Gasper Karaqi | 27.084970826320614 | LINESTRING(20.7419311 42.2091401,20.7418331 42.2093728) | 0102000020E6100000020000001E9727C6E8BD3440025658BACC1A4540A3625232EFBD34401EE4501AC51A4540 | 157.1619429422876
18 | 4522 | 33.5826170431464 | Gasper Karaqi | 67.1652340862928 | LINESTRING(20.7424244 42.2086604,20.7422441 42.2088228,20.7420182 42.2090345,20.7419311 42.2091401) | 0102000020E610000004000000A3625232EFBD34401EE4501AC51A45406B099EE7F4BD34402C9E7AA4C11A4540222697B503BE34405F549CB4BA1A45409FC085860FBE344032FC4C62B51A4540 | 134.1992067188376
19 | 4503 | 14.662642298440723 | Zita Bashota | 29.325284596881446 | LINESTRING(20.7427163 42.2088105,20.7426375 42.2087652,20.7424244 42.2086604) | 0102000020E6100000030000009FC085860FBE344032FC4C62B51A45408048BF7D1DBE34407E456DD1B81A4540C5B4CAA722BE3440DC476E4DBA1A4540 | 62.78699311189232
20 | 7281 | 47.456019442854526 | Nuri Serifi | 94.91203888570905 | LINESTRING(20.743597 42.2086076,20.7435155 42.2085823,20.7434838 42.2085655,20.7434696 42.208542,20.7434558 42.2085028,20.743424 42.2084725,20.7433955 42.2084641,20.7433449 42.2084625,20.7432909 42.2084688,20.7432579 42.2084863,20.7431343 42.2085728,20.743078 42.2086274,20.7430534 42.2086529,20.7429952 42.2086796,20.7429218 42.2087004,20.7427163 42.2088105) | 0102000020E610000010000000C5B4CAA722BE3440DC476E4DBA1A4540477C821F30BE3440C054D8B1B61A4540C115F5EE34BE3440D3AB5C03B61A45407E2E64BF38BE344097DB6223B51A454089601C5C3ABE344056067A4DB41A45402E09AB0C3EBE3440E66A7583B21A45405FFE542646BE3440FF14D8ADAF1A454017EAFA4F48BE3440411E0B1BAF1A4540FD25F3D94BBE3440A1F831E6AE1A45405A7EE02A4FBE3440EEF19DF3AE1A4540BF0F070951BE3440C4CE143AAF1A454082058B1E53BE3440ED8F4138B01A4540F991110654BE3440D5EB1681B11A4540161B4EF454BE344075C93846B21A4540B091240857BE3440228326D3B21A45405A677C5F5CBE344039D961A7B31A4540 | 102.97370904587748
21 | 7282 | 22.30602568677472 | | 44.61205137354944 | LINESTRING(20.743597 42.2086076,20.7436118 42.2085768,20.7436449 42.2085108,20.7436758 42.2084571,20.7437026 42.2084253,20.7437455 42.2083588,20.7437857 42.2083032,20.7438231 42.2082437) | 0102000020E6100000080000005A677C5F5CBE344039D961A7B31A454071EBC9575DBE3440285A03A5B21A454052561D835FBE3440706E5D7BB01A45409ED3878961BE34403B8FE5B8AE1A45403EF3284B63BE34405A9423AEAD1A4540130CE71A66BE3440BAEA4B80AB1A454083BB58BD68BE34407AD3E3ADA91A45406482D0306BBE34408C8CC4BAA71A4540 | 148.14630133307207
22 | 4500 | 3.4861632067202333 | Shën Flori | 6.9723264134404666 | LINESTRING(20.7438231 42.2082437,20.7437911 42.2082373,20.7437399 42.208235) | 0102000020E6100000030000006482D0306BBE34408C8CC4BAA71A45404D8EF11769BE344057A71485A71A4540F63AF3BC65BE3440F870C971A71A4540 | 264.03042925257193
23 | -1 | 0 | | | | |
(23 rows)
6.4. Solutions to pl/pgsql function¶
6.4.1. Exercise: 1 (Chapter: pl/pgsql)¶
Exercise 1: Number of vertices
For ways_vertices_pgr:
count
-------
6707
(1 row)
For vehicle_net:
count
-------
6412
(1 row)
For taxi_net:
count
-------
1516
(1 row)
For walk_net:
count
-------
6577
(1 row)
6.4.2. Exercise: 2 (Chapter: pl/pgsql)¶
Exercise 2: Vertices on a table
For vehicle_net:
SELECT 6412
For taxi_net:
SELECT 1516
For walk_net:
SELECT 6577
6.4.3. Exercise: 3 (Chapter: pl/pgsql)¶
For ways_vertices_pgr:
osm_id
------------
1838183980
(1 row)
For vehicle_net_vertices_pgr:
id
------------
1838183980
(1 row)
For taxi_net_vertices_pgr:
id
------------
8405249999
(1 row)
For walk_net_vertices_pgr:
id
------------
7580762688
(1 row)
6.4.4. Exercise: 4 (Chapter: pl/pgsql)¶
Exercise 4: Nearest vertex function
CREATE FUNCTION
6.4.5. Exercise: 5 (Chapter: pl/pgsql)¶
Exercise 5: Test nearest vertex function
For ways_vertices_pgr:
wrk_nearestosm
----------------
2487
(1 row)
For vehicle_net_vertices_pgr:
wrk_nearestosm
----------------
1838183980
(1 row)
For taxi_net_vertices_pgr:
wrk_nearestosm
----------------
8405249999
(1 row)
For walk_net_vertices_pgr:
wrk_nearestosm
----------------
7580762688
(1 row)
6.4.6. Exercise: 6 (Chapter: pl/pgsql)¶
Exercise 6: Creating the main function
CREATE FUNCTION
6.4.7. Exercise: 7 (Chapter: pl/pgsql)¶
Exercise 7: Using the main function
For vehicle_net
seq | id | seconds | name | length_m | route_readable | route_geom | azimuth
-----+------+--------------------+-----------------+--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------
1 | 215 | 1.8987221372012972 | | 21.096912635569968 | LINESTRING(20.7293697 42.2149521,20.7293118 42.2149724,20.7292641 42.215004,20.7292305 42.2150443,20.7292139 42.2150901) | 0102000020E610000005000000E3491CC3ADBA3440C0988812881B4540E8BE9CD9AEBA34407AD85592861B45409AA5530DB1BA34402D414640851B45403F96992DB4BA344075C53137841B45407E3100F9B7BA34406FDAE78C831B4540 | 131.53294833420603
2 | 6972 | 1.4694011841675363 | | 16.326679824083737 | LINESTRING(20.7295592 42.2149734,20.7295155 42.2149563,20.7294677 42.2149468,20.7294183 42.2149454,20.7293697 42.2149521) | 0102000020E6100000050000007E3100F9B7BA34406FDAE78C831B45409A9A5F28BBBA34407BB6B354831B454004FD2A65BEBA34409F307260831B4540D36C1E87C1BA3440DA4823B0831B4540F57E4864C4BA34404541953F841B4540 | 83.58681158581581
3 | 242 | 0.3669958465826967 | | 4.07773162869663 | LINESTRING(20.7295968 42.2149972,20.7295592 42.2149734) | 0102000020E610000002000000F57E4864C4BA34404541953F841B454029441BDBC6BA3440A45D3B07851B4540 | 57.667059923116426
4 | 6970 | 2.2298784372546203 | | 30.970533850758613 | LINESTRING(20.7298939 42.2148675,20.7298367 42.2148945,20.7297899 42.2149111,20.7297713 42.2149353,20.7297579 42.2149652,20.7297286 42.2149794,20.7296584 42.2150049,20.7295968 42.2149972) | 0102000020E61000000800000029441BDBC6BA3440A45D3B07851B45406F4095E4CABA344068FDD247851B4540B4F4577ECFBA34402728EA71841B4540657FEA69D1BA344099E3CBFA831B4540350FBB4AD2BA3440C320FAFF821B45407C73C982D3BA34401106F934821B4540AAEBF593D6BA34408ECBB8A9811B4540C60C9E53DABA344094BC3AC7801B4540 | 113.58383662815014
5 | 332 | 1.6598946656944242 | | 23.054092579089225 | LINESTRING(20.730038 42.2147028,20.7300062 42.2147341,20.7299496 42.2147617,20.7299218 42.2147867,20.7299212 42.2148267,20.7298939 42.2148675) | 0102000020E610000006000000C60C9E53DABA344094BC3AC7801B454036A8A21DDCBA34406067F9707F1B454030A3B327DCBA3440D10E6E217E1B454071BA1BFADDBA344078F7B64F7D1B454092E0B2AFE1BA3440026B30687C1B454056D636C5E3BA3440082EA0617B1B4540 | 138.81653779160783
6 | 331 | 3.685575356299558 | Xhemajl Bjediqi | 51.18854661527164 | LINESTRING(20.730038 42.2147028,20.7300726 42.2147049,20.7302182 42.2146663,20.7305794 42.214492) | 0102000020E61000000400000056D636C5E3BA3440082EA0617B1B4540A8B4B409E6BA34403EE53D737B1B4540A9F17794EFBA3440D306712F7A1B45409D21664007BB344077A04E79741B4540 | 111.27399407915908
7 | 3042 | 2.0774491312290846 | Jani Vreto | 28.85346015595951 | LINESTRING(20.7305794 42.214492,20.730674 42.2145903,20.7307585 42.2146847,20.7307881 42.2146976) | 0102000020E6100000040000009D21664007BB344077A04E79741B45409A0986730DBB3440F844E8B1771B454026C632FD12BB3440CDB9CAC97A1B454055CECDED14BB3440CD4301367B1B4540 | 45.42870770419127
8 | 3046 | 2.8795929493404806 | Jani Vreto | 39.99434651861779 | LINESTRING(20.7307881 42.2146976,20.7308216 42.2146946,20.731051 42.2146551,20.7311998 42.2146253,20.7312606 42.2146213) | 0102000020E61000000500000055CECDED14BB3440CD4301367B1B4540DC35D71F17BB34405CD0D61C7B1B454046B5882826BB3440B6357DD1791B45407CD7FBE82FBB3440753282D7781B454074DA09E533BB34403343F4B5781B4540 | 99.17302162072632
9 | -1 | 0 | | | | |
(9 rows)
For taxi_net
The
WARNINGmessage:
SELECT *
FROM wrk_dijkstra(
'taxi_net',
(SELECT wrk_NearestOSM(
'taxi_net_vertices_pgr',
42.2151, 20.729354)),
(SELECT wrk_NearestOSM('taxi_net_vertices_pgr', 42.2147, 20.7312))
)
The query results:
seq | id | seconds | name | length_m | route_readable | route_geom | azimuth
-----+------+--------------------+-----------------+--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------
1 | 103 | 16.430436541850607 | Hasan Prishtina | 202.84489557840257 | LINESTRING(20.7322886 42.2149295,20.7302559 42.2151518,20.7300553 42.2151681,20.7299028 42.215177,20.7298556 42.2151788) | 0102000020E6100000050000006ECD0CD1D7BA344071659AFA8A1B45404342EFE8DABA3440FAEC80EB8A1B45407FC575E7E4BA34403C52D8A08A1B45400736F80CF2BA344078561C188A1B4540CF3B044477BB34400AB952CF821B4540 | 95.8504563533979
2 | 3033 | 2.1113200878004212 | Ali Dida | 32.58210012037687 | LINESTRING(20.7322573 42.2146371,20.7322706 42.2147703,20.7322886 42.2149295) | 0102000020E610000003000000CF3B044477BB34400AB952CF821B454082D2061676BB34407853DB977D1B4540DCC1E33675BB34400F817E3A791B4540 | 186.10996847751744
3 | 3034 | 5.3668899481124415 | Jani Vreto | 82.82237574247596 | LINESTRING(20.7312606 42.2146213,20.7313595 42.2146254,20.7314484 42.2146426,20.7315204 42.2146571,20.7317193 42.2146545,20.7319125 42.2146465,20.7321404 42.2146426,20.7322573 42.2146371) | 0102000020E610000008000000DCC1E33675BB34400F817E3A791B45409214A28D6DBB344009AAA168791B45409A081B9E5EBB3440B6D95889791B45401E5ABFF451BB344039B874CC791B45405A5BC2EB44BB3440562D44E2791B454027B6CC3340BB344009AAA168791B4540711E4E603ABB34400AF258D8781B454074DA09E533BB34403343F4B5781B4540 | 269.0918054607552
4 | -1 | 0 | | | | |
(4 rows)
For walk_net
SELECT 14
seq | id | seconds | name | length_m | route_readable | route_geom | azimuth
-----+------+--------------------+-------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------
1 | 6973 | 6.0089713286267505 | Zanat | 12.017942657253501 | LINESTRING(20.7295592 42.2149734,20.7294893 42.2148785) | 0102000020E610000002000000F57E4864C4BA34404541953F841B45402D488ECFBFBA3440880E8123811B4540 | 216.37405311713016
2 | 1689 | 66.09412312011098 | Zanat | 132.18824624022196 | LINESTRING(20.7294893 42.2148785,20.7294251 42.2148115,20.7292534 42.2146452,20.7290768 42.2144806,20.7289326 42.2143083,20.7289052 42.2142228,20.7288994 42.2141383,20.7289108 42.21398,20.7289269 42.2138268) | 0102000020E6100000090000002D488ECFBFBA3440880E8123811B4540AC61759ABBBA344001A777F17E1B45402F7FCF59B0BA3440271F717E791B4540B845F4C6A4BA34403050AD19741B4540FEFCAD539BBA344074E151746E1B454064E2FB8799BA34405E0718A76B1B4540F312AD2699BA344018A941E2681B454082B3EFE599BA3440C1FF56B2631B4540B7AC0CF49ABA3440118134AD5E1B4540 | 208.1357887326561
3 | 5398 | 65.30057870072591 | Zanat | 130.60115740145181 | LINESTRING(20.7305009 42.2139423,20.7289269 42.2138268) | 0102000020E610000002000000B7AC0CF49ABA3440118134AD5E1B4540D532631B02BB344092DD1676621B4540 | 85.80316206460051
4 | 5332 | 4.191469051629715 | Nazim Pulaj | 8.38293810325943 | LINESTRING(20.7305825 42.2138974,20.7305009 42.2139423) | 0102000020E610000002000000D532631B02BB344092DD1676621B4540A987687407BB344087D970FD601B4540 | 118.82157426386289
5 | 5328 | 5.32798742612871 | | 10.65597485225742 | LINESTRING(20.7306742 42.2139649,20.7305825 42.2138974) | 0102000020E610000002000000A987687407BB344087D970FD601B4540ED07E1760DBB3440F7FEAB33631B4540 | 53.64336541201075
6 | 5330 | 5.835725032287688 | | 11.671450064575376 | LINESTRING(20.7308032 42.2139719,20.7307815 42.2139589,20.730756 42.213952,20.7307265 42.213951,20.7306943 42.2139549,20.7306742 42.2139649) | 0102000020E610000006000000ED07E1760DBB3440F7FEAB33631B4540A5DF19C80EBB3440D328C9DF621B45400FD253E410BB344027F911BF621B4540145B41D312BB3440F77475C7621B45409605137F14BB344015185701631B4540E9CF23EB15BB3440A961646E631B4540 | 86.89397290594468
7 | 5326 | 3.1632099261301367 | | 6.3264198522602735 | LINESTRING(20.7308561 42.2139307,20.7308032 42.2139719) | 0102000020E610000002000000E9CF23EB15BB3440A961646E631B45400595A76219BB3440210EC814621B4540 | 127.91245635847007
8 | 5325 | 3.0778324170878166 | | 6.155664834175633 | LINESTRING(20.7308561 42.2139307,20.7308775 42.2139401,20.7309018 42.213944,20.7309265 42.2139421) | 0102000020E6100000040000000595A76219BB3440210EC814621B4540DAE1AFC91ABB3440C866A263621B454069965F611CBB344074965984621B45409D47C5FF1DBB3440685E6974621B4540 | 80.80183350346843
9 | 5334 | 1.0241021973154523 | | 2.0482043946309045 | LINESTRING(20.7309265 42.2139421,20.7309491 42.2139345) | 0102000020E6100000020000009D47C5FF1DBB3440685E6974621B4540678AEF7A1FBB3440397EA834621B4540 | 108.58692665246318
10 | 5340 | 9.40500026407347 | | 18.81000052814694 | LINESTRING(20.7311108 42.2140537,20.7310537 42.2140145,20.7309491 42.2139345) | 0102000020E610000003000000678AEF7A1FBB3440397EA834621B4540AB1ED55526BB3440552FBFD3641B45409DC0CF132ABB34403D8B941C661B4540 | 53.60356327152084
11 | 90 | 7.224717756134362 | | 14.449435512268725 | LINESTRING(20.7312337 42.2141459,20.7311999 42.2141149,20.7311108 42.2140537) | 0102000020E6100000030000009DC0CF132ABB34403D8B941C661B4540A656A9EA2FBB34400C8BF61D681B4540AB3BBB2132BB344047890222691B4540 | 53.12264390951696
12 | 91 | 8.96620583448509 | Jani Vreto | 17.93241166897018 | LINESTRING(20.7312337 42.2141459,20.7312478 42.214307) | 0102000020E610000002000000AB3BBB2132BB344047890222691B45409E454A0E33BB3440E5266A696E1B4540 | 5.001968161194623
13 | 6185 | 17.468387544800013 | Jani Vreto | 34.936775089600026 | LINESTRING(20.7312478 42.214307,20.7312561 42.2144017,20.7312606 42.2146213) | 0102000020E6100000030000009E454A0E33BB3440E5266A696E1B454021808A9933BB344079DAD083711B454074DA09E533BB34403343F4B5781B4540 | 2.332106032204861
14 | -1 | 0 | | | | |
(14 rows)