13. Appendix: Workshop Solutions

13.1. pgRouting Algorithms Solutions

13.1.1. Solution to Exercise 1

Exercise 1

 seq | path_seq | node | edge |        cost         |      agg_cost
-----+----------+------+------+---------------------+---------------------
   1 |        1 | 1639 | 1924 | 0.00203726472015596 |                   0
   2 |        2 | 1500 | 1370 | 0.00195102527671287 | 0.00203726472015596
   3 |        3 | 1166 | 1407 | 0.00141014024221734 | 0.00398828999686883
   4 |        4 | 1195 |   -1 |                   0 | 0.00539843023908617
(4 rows)

13.1.2. Solution to Exercise 2

Exercise 2

 seq | path_seq | start_vid | node | edge |       cost       |     agg_cost
-----+----------+-----------+------+------+------------------+------------------
   1 |        1 |      1195 | 1195 | 1724 | 265.567165528973 |                0
   2 |        2 |      1195 | 1460 |  485 | 158.953675313931 | 265.567165528973
   3 |        3 |      1195 |  410 |  484 | 167.449458836267 | 424.520840842905
   4 |        4 |      1195 |  856 |   -1 |                0 | 591.970299679172
   5 |        1 |      1639 | 1639 | 1924 | 225.258862979722 |                0
   6 |        2 |      1639 | 1500 | 1772 | 211.661459249381 | 225.258862979722
   7 |        3 |      1639 |  184 |  219 | 217.965421167396 | 436.920322229102
   8 |        4 |      1639 | 1723 | 2029 | 120.209032368071 | 654.885743396498
   9 |        5 |      1639 | 1460 |  485 | 158.953675313931 | 775.094775764569
  10 |        6 |      1639 |  410 |  484 | 167.449458836267 |   934.0484510785
  11 |        7 |      1639 |  856 |   -1 |                0 | 1101.49790991477
(11 rows)

13.1.3. Solution to Exercise 3

Exercise 3

 seq | path_seq | end_vid | node | edge |       cost       |     agg_cost
-----+----------+---------+------+------+------------------+------------------
   1 |        1 |    1195 |  856 |  484 | 128.807276027898 |                0
   2 |        2 |    1195 |  410 |  485 | 122.272057933793 | 128.807276027898
   3 |        3 |    1195 | 1460 | 1724 | 204.282435022287 | 251.079333961691
   4 |        4 |    1195 | 1195 |   -1 |                0 | 455.361768983978
   5 |        1 |    1639 |  856 |  484 | 128.807276027898 |                0
   6 |        2 |    1639 |  410 |  485 | 122.272057933793 | 128.807276027898
   7 |        3 |    1639 | 1460 | 2029 | 92.4684864369775 | 251.079333961691
   8 |        4 |    1639 | 1723 |  219 | 167.665708590305 | 343.547820398669
   9 |        5 |    1639 |  184 | 1772 | 162.816507114908 | 511.213528988973
  10 |        6 |    1639 | 1500 | 1924 |  173.27604844594 | 674.030036103881
  11 |        7 |    1639 | 1639 |   -1 |                0 | 847.306084549821
(11 rows)

13.1.4. Solution to Exercise 4

Exercise 4

 seq | path_seq | start_vid | end_vid | node | edge |       cost        |     agg_cost
-----+----------+-----------+---------+------+------+-------------------+------------------
   1 |        1 |      1195 |     856 | 1195 | 1724 |  3.40470725037145 |                0
   2 |        2 |      1195 |     856 | 1460 |  485 |  2.03786763222989 | 3.40470725037145
   3 |        3 |      1195 |     856 |  410 |  484 |   2.1467879337983 | 5.44257488260134
   4 |        4 |      1195 |     856 |  856 |   -1 |                 0 | 7.58936281639964
   5 |        1 |      1195 |    1256 | 1195 | 1724 |  3.40470725037145 |                0
   6 |        2 |      1195 |    1256 | 1460 |  485 |  2.03786763222989 | 3.40470725037145
   7 |        3 |      1195 |    1256 |  410 | 3504 |  1.93508343932859 | 5.44257488260134
   8 |        4 |      1195 |    1256 | 1461 |   10 |  3.66361602123479 | 7.37765832192994
   9 |        5 |      1195 |    1256 |    7 |  850 | 0.416463263772614 | 11.0412743431647
  10 |        6 |      1195 |    1256 |  728 | 1585 |  0.71458770101113 | 11.4577376069373
  11 |        7 |      1195 |    1256 | 1343 | 1410 | 0.305281604994012 | 12.1723253079485
  12 |        8 |      1195 |    1256 | 1198 | 2072 | 0.640502762968939 | 12.4776069129425
  13 |        9 |      1195 |    1256 | 1755 | 3371 |  2.43553647686408 | 13.1181096759114
  14 |       10 |      1195 |    1256 |  498 | 3642 |  2.02572210685056 | 15.5536461527755
  15 |       11 |      1195 |    1256 | 2324 | 2747 |  3.01748018392712 | 17.5793682596261
  16 |       12 |      1195 |    1256 | 1256 |   -1 |                 0 | 20.5968484435532
  17 |        1 |      1639 |     856 | 1639 | 1924 |  2.88793414076566 |                0
  18 |        2 |      1639 |     856 | 1500 | 1772 |  2.71360845191514 | 2.88793414076566
  19 |        3 |      1639 |     856 |  184 |  219 |  2.79442847650508 |  5.6015425926808
  20 |        4 |      1639 |     856 | 1723 | 2029 |  1.54114144061629 | 8.39597106918587
  21 |        5 |      1639 |     856 | 1460 |  485 |  2.03786763222989 | 9.93711250980217
  22 |        6 |      1639 |     856 |  410 |  484 |   2.1467879337983 | 11.9749801420321
  23 |        7 |      1639 |     856 |  856 |   -1 |                 0 | 14.1217680758304
  24 |        1 |      1639 |    1256 | 1639 | 1924 |  2.88793414076566 |                0
  25 |        2 |      1639 |    1256 | 1500 | 1772 |  2.71360845191514 | 2.88793414076566
  26 |        3 |      1639 |    1256 |  184 |  220 |  2.09983765887622 |  5.6015425926808
  27 |        4 |      1639 |    1256 | 2543 | 3007 |  1.91356518477422 | 7.70138025155702
  28 |        5 |      1639 |    1256 | 2092 | 3325 |  2.45447687938298 | 9.61494543633124
  29 |        6 |      1639 |    1256 |  241 | 1957 |  2.13676131594056 | 12.0694223157142
  30 |        7 |      1639 |    1256 | 1668 | 1958 |  1.71804746216162 | 14.2061836316548
  31 |        8 |      1639 |    1256 | 2105 | 2501 |  1.29698598632806 | 15.9242310938164
  32 |        9 |      1639 |    1256 |    7 |  850 | 0.416463263772614 | 17.2212170801445
  33 |       10 |      1639 |    1256 |  728 | 1585 |  0.71458770101113 | 17.6376803439171
  34 |       11 |      1639 |    1256 | 1343 | 1410 | 0.305281604994012 | 18.3522680449282
  35 |       12 |      1639 |    1256 | 1198 | 2072 | 0.640502762968939 | 18.6575496499222
  36 |       13 |      1639 |    1256 | 1755 | 3371 |  2.43553647686408 | 19.2980524128912
  37 |       14 |      1639 |    1256 |  498 | 3642 |  2.02572210685056 | 21.7335888897552
  38 |       15 |      1639 |    1256 | 2324 | 2747 |  3.01748018392712 | 23.7593109966058
  39 |       16 |      1639 |    1256 | 1256 |   -1 |                 0 | 26.7767911805329
(39 rows)

13.1.5. Solution to Exercise 5

Exercise 5

 start_vid | end_vid |     agg_cost
-----------+---------+------------------
      1195 |     856 | 7.58936281639964
      1195 |    1256 | 20.5968484435532
      1639 |     856 | 14.1217680758304
      1639 |    1256 | 26.7767911805329
(4 rows)

13.1.6. Solution to Exercise 6

Exercise 6

 end_vid |       sum
---------+------------------
     856 |   21.71113089223
    1256 | 47.3736396240861
(2 rows)

13.2. Advanced Routing Queries Solutions

13.2.1. Solution to Exercise 7

Exercise 7

 seq | path_seq | node | edge |       cost       |     agg_cost
-----+----------+------+------+------------------+------------------
   1 |        1 | 1639 | 1924 |   16.21863813454 |                0
   2 |        2 | 1500 | 1772 | 15.2396250659554 |   16.21863813454
   3 |        3 |  184 |  220 | 11.7926882922489 | 31.4582632004954
   4 |        4 | 2543 | 3007 |  10.746582077692 | 43.2509514927442
   5 |        5 | 2092 | 3325 | 13.7843421546148 | 53.9975335704362
   6 |        6 |  241 | 1957 | 12.0000515503222 | 67.7818757250511
   7 |        7 | 1668 | 1958 | 9.64855454749966 | 79.7819272753732
   8 |        8 | 2105 | 2501 | 7.28387329921837 | 89.4304818228729
   9 |        9 |    7 |  850 |   2.338857689347 | 96.7143551220913
  10 |       10 |  728 | 1585 | 4.01312452887851 | 99.0532128114383
  11 |       11 | 1343 | 1410 | 1.71446149364637 | 103.066337340317
  12 |       12 | 1198 | 2072 | 3.59706351683356 | 104.780798833963
  13 |       13 | 1755 | 3371 | 13.6779728540687 | 108.377862350797
  14 |       14 |  498 | 3642 | 11.3764553520728 | 122.055835204865
  15 |       15 | 2324 | 2747 | 16.9461687129347 | 133.432290556938
  16 |       16 | 1256 |   -1 |                0 | 150.378459269873
(16 rows)

13.2.2. Solution to Exercise 8

Exercise 8

 seq | path_seq | node | edge |       cost       |     agg_cost
-----+----------+------+------+------------------+------------------
   1 |        1 | 1256 | 2747 | 16.9461687129347 |                0
   2 |        2 | 2324 | 3642 | 11.3764553520728 | 16.9461687129347
   3 |        3 |  498 | 3371 | 13.6779728540687 | 28.3226240650075
   4 |        4 | 1755 | 2072 | 3.59706351683356 | 42.0005969190761
   5 |        5 | 1198 | 1411 | 7.62056168933039 | 45.5976604359097
   6 |        6 | 2605 | 3081 | 5.02090630047807 | 53.2182221252401
   7 |        7 |  626 | 3917 | 8.38742205443017 | 58.2391284257181
   8 |        8 | 3465 | 5000 |  2.4740977338142 | 66.6265504801483
   9 |        9 | 1008 | 3447 | 9.13457674028614 | 69.1006482139625
  10 |       10 | 1546 | 3518 | 1.86833722718625 | 78.2352249542487
  11 |       11 |  230 | 3941 | 6.14055277216272 | 80.1035621814349
  12 |       12 | 3550 | 3942 | 10.4943852265419 | 86.2441149535976
  13 |       13 | 2092 | 3007 |  10.746582077692 | 96.7385001801395
  14 |       14 | 2543 |  220 | 11.7926882922489 | 107.485082257832
  15 |       15 |  184 | 1772 | 15.2396250659554 |  119.27777055008
  16 |       16 | 1500 | 1924 |   16.21863813454 | 134.517395616036
  17 |       17 | 1639 |   -1 |                0 | 150.736033750576
(17 rows)

13.2.3. Solution to Exercise 9

Exercise 9

 seq | path_seq | node | edge |        cost        |     agg_cost
-----+----------+------+------+--------------------+-------------------
   1 |        1 | 1256 | 2747 |  0.470726908692631 |                 0
   2 |        2 | 2324 | 3642 |  0.316012648668688 | 0.470726908692631
   3 |        3 |  498 | 3371 |  0.379943690390796 | 0.786739557361319
   4 |        4 | 1755 | 2072 | 0.0999184310231545 |  1.16668324775211
   5 |        5 | 1198 | 1411 |  0.211682269148066 |  1.26660167877527
   6 |        6 | 2605 | 3081 |  0.139469619457724 |  1.47828394792334
   7 |        7 |  626 | 3917 |  0.232983945956394 |  1.61775356738106
   8 |        8 | 3465 | 5000 | 0.0687249370503943 |  1.85073751333745
   9 |        9 | 1008 | 3447 |  0.253738242785726 |  1.91946245038785
  10 |       10 | 1546 | 3518 | 0.0518982563107291 |  2.17320069317357
  11 |       11 |  230 | 3941 |  0.170570910337853 |   2.2250989494843
  12 |       12 | 3550 | 3942 |  0.291510700737276 |  2.39566985982216
  13 |       13 | 2092 | 3007 |  0.298516168824778 |  2.68718056055943
  14 |       14 | 2543 |  220 |  0.327574674784691 |  2.98569672938421
  15 |       15 |  184 | 1772 |  0.423322918498761 |   3.3132714041689
  16 |       16 | 1500 | 1924 |  0.450517725959443 |  3.73659432266766
  17 |       17 | 1639 |   -1 |                  0 |   4.1871120486271
(17 rows)

13.2.4. Solution to Exercise 10

Exercise 10

ALTER TABLE
UPDATE 36
 seq | path_seq | node | edge |       cost       |     agg_cost
-----+----------+------+------+------------------+------------------
   1 |        1 |  856 |  484 | 12.0563610362112 |                0
   2 |        2 |  410 | 3504 | 10.8674285952694 | 12.0563610362112
   3 |        3 | 1461 |   -1 |                0 | 22.9237896314806
(3 rows)

13.2.5. Solution to Exercise 11

Exercise 11

 seq | path_seq | node | edge |       cost        |     agg_cost
-----+----------+------+------+-------------------+------------------
   1 |        1 |  856 |  484 |  6.02818051810562 |                0
   2 |        2 |  410 | 3463 |  4.39092341742284 | 6.02818051810562
   3 |        3 | 1193 | 3671 |  7.72021374468571 | 10.4191039355285
   4 |        4 | 2543 | 3007 |  3.22397462330761 | 18.1393176802142
   5 |        5 | 2092 | 3325 |  6.89217107730741 | 21.3632923035218
   6 |        6 |  241 | 3621 | 0.953512053439165 | 28.2554633808292
   7 |        7 | 2201 | 3505 |   4.2974761211037 | 29.2089754342683
   8 |        8 | 1461 |   -1 |                 0 | 33.5064515553721
(8 rows)

13.3. Solutions to Writing a SQL Stored Procedures Chapter

13.3.1. Solution to Chapter 7 Exercise 1

Exercise 1

CREATE VIEW
 count 
-------
  5650
(1 row)

 count 
-------
  3775
(1 row)

13.3.2. Solution to Chapter 7 Exercise 2

Exercise 2

CREATE VIEW
 count 
-------
  1881
(1 row)

13.3.3. Solution to Chapter 7 Exercise 3

Exercise 3

 seq | path_seq | node | edge |       cost        |     agg_cost
-----+----------+------+------+-------------------+-------------------
   1 |        1 | 1195 | 1407 |  0.18710649413388 |                 0
   2 |        2 | 1166 | 1370 | 0.258853919144829 |  0.18710649413388
   3 |        3 | 1500 | 1924 | 0.270310635575666 | 0.445960413278709
   4 |        4 | 1639 |   -1 |                 0 | 0.716271048854375
(4 rows)

13.3.4. Solution to Chapter 7 Exercise 4

Exercise 4

 seq | path_seq | node | edge |       cost        |     agg_cost      |     name
-----+----------+------+------+-------------------+-------------------+--------------
   1 |        1 | 1195 | 1407 |  0.18710649413388 |                 0 | Ghana street
   2 |        2 | 1166 | 1370 | 0.258853919144829 |  0.18710649413388 | Ghana street
   3 |        3 | 1500 | 1924 | 0.270310635575666 | 0.445960413278709 | Ohio Street
   4 |        4 | 1639 |   -1 |                 0 | 0.716271048854375 |
(4 rows)

13.3.5. Solution to Chapter 7 Exercise 5

Exercise 5

 seq | path_seq | node | edge |       cost        |     agg_cost      |     name     |                                              st_astext
-----+----------+------+------+-------------------+-------------------+--------------+-----------------------------------------------------------------------------------------------------
   1 |        1 | 1195 | 1407 |  0.18710649413388 |                 0 | Ghana street | LINESTRING(39.2902655 -6.8114116,39.2903151 -6.8111861,39.2904794 -6.8109834,39.2910718 -6.8102817)
   2 |        2 | 1166 | 1370 | 0.258853919144829 |  0.18710649413388 | Ghana street | LINESTRING(39.2888771 -6.8127504,39.2895542 -6.8121531,39.2902262 -6.8115544,39.2902655 -6.8114116)
   3 |        3 | 1500 | 1924 | 0.270310635575666 | 0.445960413278709 | Ohio Street  | LINESTRING(39.2888771 -6.8127504,39.2875816 -6.8111781)
   4 |        4 | 1639 |   -1 |                 0 | 0.716271048854375 |              |
(4 rows)

13.3.6. Solution to Chapter 7 Exercise 6

Exercise 6

 seq | path_seq | node | edge |       cost        |     agg_cost      |     name     |                                                                         route_geom
-----+----------+------+------+-------------------+-------------------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------
   1 |        1 | 1195 | 1407 |  0.18710649413388 |                 0 | Ghana street | 0102000020E6100000040000001BD47E6B27A543405061B6AEE23E1BC07A04920B29A543400FE1A991A73E1BC0B394D16D2EA543404265B26E723E1BC0D8E43AD741A543406B30687CBA3D1BC0
   2 |        2 | 1166 | 1370 | 0.258853919144829 |  0.18710649413388 | Ghana street | 0102000020E6100000040000001B88C0ECF9A44340613E0FA441401BC0B0B5AD1C10A543404C61EF0FA53F1BC09FB8D22126A543401AB3DB1D083F1BC01BD47E6B27A543405061B6AEE23E1BC0
   3 |        3 | 1500 | 1924 | 0.270310635575666 | 0.445960413278709 | Ohio Street  | 0102000020E6100000020000001B88C0ECF9A44340613E0FA441401BC037774F79CFA44340F9ECCA78A53E1BC0
   4 |        4 | 1639 |   -1 |                 0 | 0.716271048854375 |              |
(4 rows)

13.3.7. Solution to Chapter 7 Exercise 7

Exercise 7

 seq |     name     |       cost        |     azimuth      |                                              st_astext                                              |                                                                         route_geom
-----+--------------+-------------------+------------------+-----------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------
   1 | Ghana street |  0.18710649413388 | 35.5117668657488 | LINESTRING(39.2902655 -6.8114116,39.2903151 -6.8111861,39.2904794 -6.8109834,39.2910718 -6.8102817) | 0102000020E6100000040000001BD47E6B27A543405061B6AEE23E1BC07A04920B29A543400FE1A991A73E1BC0B394D16D2EA543404265B26E723E1BC0D8E43AD741A543406B30687CBA3D1BC0
   2 | Ghana street | 0.258853919144829 | 46.0419321605528 | LINESTRING(39.2888771 -6.8127504,39.2895542 -6.8121531,39.2902262 -6.8115544,39.2902655 -6.8114116) | 0102000020E6100000040000001B88C0ECF9A44340613E0FA441401BC0B0B5AD1C10A543404C61EF0FA53F1BC09FB8D22126A543401AB3DB1D083F1BC01BD47E6B27A543405061B6AEE23E1BC0
   3 | Ohio Street  | 0.270310635575666 | 320.513109606255 | LINESTRING(39.2888771 -6.8127504,39.2875816 -6.8111781)                                             | 0102000020E6100000020000001B88C0ECF9A44340613E0FA441401BC037774F79CFA44340F9ECCA78A53E1BC0
(3 rows)

13.3.8. Solution to Chapter 7 Exercise 8

Exercise 8

 seq |     name     |       cost        |     azimuth      |                                              st_astext                                              |                                                                         route_geom
-----+--------------+-------------------+------------------+-----------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------
   1 | Ghana street |  0.18710649413388 | 215.511766865749 | LINESTRING(39.2910718 -6.8102817,39.2904794 -6.8109834,39.2903151 -6.8111861,39.2902655 -6.8114116) | 0102000020E610000004000000D8E43AD741A543406B30687CBA3D1BC0B394D16D2EA543404265B26E723E1BC07A04920B29A543400FE1A991A73E1BC01BD47E6B27A543405061B6AEE23E1BC0
   2 | Ghana street | 0.258853919144829 | 226.041932160553 | LINESTRING(39.2902655 -6.8114116,39.2902262 -6.8115544,39.2895542 -6.8121531,39.2888771 -6.8127504) | 0102000020E6100000040000001BD47E6B27A543405061B6AEE23E1BC09FB8D22126A543401AB3DB1D083F1BC0B0B5AD1C10A543404C61EF0FA53F1BC01B88C0ECF9A44340613E0FA441401BC0
   3 | Ohio Street  | 0.270310635575666 | 320.513109606255 | LINESTRING(39.2888771 -6.8127504,39.2875816 -6.8111781)                                             | 0102000020E6100000020000001B88C0ECF9A44340613E0FA441401BC037774F79CFA44340F9ECCA78A53E1BC0
(3 rows)

13.3.9. Solution to Chapter 7 Exercise 9

Exercise 9

CREATE FUNCTION

13.3.10. Solution to Chapter 7 Exercise 10

Exercise 10

 seq | gid  |     name     |       cost        |     azimuth      |                                           route_readable                                            |                                                                         route_geom
-----+------+--------------+-------------------+------------------+-----------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------
   1 | 1407 | Ghana street |  0.18710649413388 | 215.511766865749 | LINESTRING(39.2910718 -6.8102817,39.2904794 -6.8109834,39.2903151 -6.8111861,39.2902655 -6.8114116) | 0102000020E610000004000000D8E43AD741A543406B30687CBA3D1BC0B394D16D2EA543404265B26E723E1BC07A04920B29A543400FE1A991A73E1BC01BD47E6B27A543405061B6AEE23E1BC0
   2 | 1370 | Ghana street | 0.258853919144829 | 226.041932160553 | LINESTRING(39.2902655 -6.8114116,39.2902262 -6.8115544,39.2895542 -6.8121531,39.2888771 -6.8127504) | 0102000020E6100000040000001BD47E6B27A543405061B6AEE23E1BC09FB8D22126A543401AB3DB1D083F1BC0B0B5AD1C10A543404C61EF0FA53F1BC01B88C0ECF9A44340613E0FA441401BC0
   3 | 1924 | Ohio Street  | 0.270310635575666 | 320.513109606255 | LINESTRING(39.2888771 -6.8127504,39.2875816 -6.8111781)                                             | 0102000020E6100000020000001B88C0ECF9A44340613E0FA441401BC037774F79CFA44340F9ECCA78A53E1BC0
(3 rows)

13.4. Solutions to Writing a pl/pgsql Stored Procedures Chapter

13.4.1. Solution to Chapter 8 Exercise 1

Exercise 1: Number of Vertices

 count 
-------
  3962
(1 row)

 count 
-------
  2728
(1 row)

 count 
-------
  1228
(1 row)

13.4.2. Solution to Chapter 8 Exercise 2

Exercise 2: Nearest Vertex

  osm_id
-----------
 282841506
(1 row)

  osm_id
-----------
 282841506
(1 row)

   osm_id   
------------
  302057437
  274009107
  273346262
 1829140390
  302057502
  252643253
 3733493388
 4457110973
  252942388
  272637936
  496299056
 4457025820
  252496428
 3860665802
  282843557
 1756160343
 4687855896
 4167229372
  367770113
  272637933
 4457069061
  252643679
  302057424
  302057757
 4433005450
 4457025809
  275626394
 4432889291
 4185800168
 4813893897
  302057697
 4167229384
  302057733
  282862578
  274950324
 4457055592
  368751228
  302057415
 4613609985
 4167393514
  302057748
  272669252
 3600350737
  273335914
  272637204
  368756274
  282838865
  368759207
  302057443
  275101670
 4167414050
 4601987611
 3685265274
 5559956956
 3602172328
  302056901
 5254212918
 3703558984
  302057644
 4167393534
  252643681
  274008976
  368755468
  252643647
  275098723
  302057699
  368762955
  302057425
 4167229102
  369758456
 3742814017
  302057751
 4167393546
 3861170168
 4454270078
 3728108949
 4185965156
  368750815
 4167229298
  368750901
  273345341
 4613609977
 4601987613
  252945440
  282838857
 3733493383
  302056357
 4457216602
 4457219492
  252643975
  302057551
 3741152583
 1484145915
  302057295
 3766869170
  252642904
 4167229381
  282838869
 4613629901
  302057716
  273336102
  252965218
 3532490072
 2764158611
 2035455574
 3735815062
 3727485768
  368756306
  252644244
 4185777339
 4605391961
  368764179
  302057535
  282842767
 4613638496
  302057434
  282836164
 4696422127
 4813893918
  252947702
 4167414008
  282861322
  302057322
  302057784
  302056539
  302057123
  302057422
 4823300380
  272667142
   34635575
  273345338
 3860680110
 4185829432
 4433033372
 1645807250
 4605391939
  302057319
  272662776
 4605583547
  282841508
  273330934
  282862581
  302057741
  252847229
  302057740
 3703576282
 4823300377
 1583653340
  272638084
  302057122
  252642909
 4696434967
  282836114
  302057653
 4452608090
 3732852891
  496299041
 1928567171
  302057736
  252945455
  302057519
 4823260824
 4446390436
  272668872
  273336103
  274008878
 3734410732
  302057772
  252678507
  302057432
 3860680153
  582862495
 4446363943
 1640289548
 4454264160
  282836122
 3721337807
 4444785277
  252847409
 3711663442
  252496431
 3610490415
  368756346
 3727485767
  368764901
 4601987418
  368751232
  252942386
 4687855891
 3765568255
  272668870
  302057769
 4454007762
 4444787380
  302057643
  252945433
  252496522
 3703599769
  302056405
  252643265
 4167229305
 4813893896
 4167414013
  302057291
 3767768995
 4454249843
  282861941
 4813893900
  302057756
 3703576283
  302057796
  275098658
 4454011703
 3700146835
 4454011699
  368743049
  302057695
 2033167381
 4605583562
 4603425720
 1831755119
  302057734
 4605391964
  302057815
 4185965172
 4823300378
  282838842
 1831755087
  368762405
 1831755121
 4185965169
 4167413997
 3600350742
  252964732
  302057536
  274008532
  274008943
  338936229
 3733493389
 1694678212
 4167229153
 1484145910
  273331109
  368750876
  302057683
 3700146830
  252941773
  272668438
 4454249838
 4457170673
 4454249831
  302057754
 4613638497
  252942372
 4167414045
 3767768997
 1484145898
  282843560
  252847408
  252965094
  282836121
  368750592
  338936139
  369758497
  338935783
  302057732
  273336502
 3765490218
  368760829
 3727480248
 4453705622
  302057438
  282838810
 1645787956
  368761372
  302057674
 5112941048
  282843028
  282842847
   34635577
 4433033377
  302057527
  275625817
  302057363
  252945425
  252643266
 4452591042
 4452608415
 4457220503
  252643624
 5113077599
 4601987400
 3739108701
  302057685
  302057798
  302057693
 4613638502
 4453696482
 4457025851
 3735815063
  302057755
 1484145900
  368758883
  282836137
 4167229615
 4446363944
 3728334220
  302057705
  252643683
 2035455567
  273344422
 4823300376
  302057799
 5085225554
  302057711
 3766869173
 3741152581
  302057678
 1694678210
  302057390
 4167229302
  282842187
 3600350743
  275099418
  273336553
 4457173390
  368750959
 3072006635
 3813575062
  272638096
  252940805
  252679546
  302057186
 1831755132
 1831755116
  252643974
 4185846502
 4009327851
  275102184
  302057735
 4167414001
 3892846011
 3709201825
  302057373
  272668437
  302057714
 3732852906
  368756000
  274950326
 1831755120
 1736928387
 3734665246
 4457170671
  368755484
 4687855939
  302057607
  302057436
  252940804
  302057444
 3698436414
 3742813847
  302057375
  302057505
 4454249833
  302057717
 3598378118
  252642972
  252643345
 3765568254
  302057752
 4454249835
  252594944
  302057794
 3610490417
  282861929
  302056551
  368760946
  252965188
  252643343
  302056708
 1871355604
  273336101
  272666361
  282861938
 4185965158
 2822457702
  282838855
 5085622786
 4453960613
 3728170255
  252643872
  302057366
 4187943256
 1645804358
  368744687
 2830896959
  368756133
 4603425727
 4454011698
 3610549415
  282862577
  368758214
  302056515
 3700146827
  368750992
 2145964480
 4613638501
  252942370
 4457048575
 3727501869
 4167393537
  252497434
  302057803
  252644174
  302057671
 1907132869
 1736928388
 4457173394
  282861943
 4457170672
  252940785
  302057520
 4601987420
  252678466
  273345283
  275102178
 4009327849
 4444785276
 1907132921
  282842186
 3886310290
  368750533
 4167229115
 2830896960
  302056703
 3732852901
  282836116
  302057441
 4453682789
 4601986834
  282843553
 3860659040
  252643258
 2145964483
  275627185
 4454024006
 4605391911
  368758485
 4454245768
  368756400
 4601987478
 4167248191
 3718201790
 1645779374
 4454245770
 3728271271
 4167248204
 2035442302
  252643653
 3738023726
 1645743802
 3698499841
 3728335034
  302057522
  272662779
  252847228
 1694678193
 3739911953
 3703599768
  368750527
 4613624940
  272666362
  302056811
 5085225543
 3703558976
 3711663443
 1831755114
  252643622
  282841506
  252963461
 3890229026
 4687855910
 4687855894
 4167393528
 1907132879
  252644211
  582862233
 4687837656
  302056690
  252643346
 3727485766
  252642897
  302056410
  302057658
  282838844
   34635569
 4452607302
 3860680276
  282844139
 4167393513
 4454249846
 4167413989
 4603425713
 4167229300
 2035455572
  275098657
 4457055593
  252642910
  270675169
 3892905400
 4167229161
 3703576284
 4167414016
 4700195177
 3685265282
 4452608414
  252643654
 4167229150
  282836165
  282838848
 4696445154
  302057782
  282838860
 4454270192
 4185965164
 4601987627
  273345340
 3698315271
  444390277
  252644185
  368758971
  272638089
  273331161
  302057309
 5085504991
  302057533
  302057431
  302057340
  275101667
  252679506
 3734254879
  252644177
 3727522143
  496299038
  282843555
 1736930934
 4167229170
 4605701038
  282861323
  252967035
  368758836
  274950327
  273335845
  282843024
 3732852892
  272668433
 4167393520
 4185965160
  302057663
  252643646
 4823300383
  252644253
 4432965523
 3716672144
  302057612
  302056392
  252847239
 3711746784
  252642892
  252643304
 4167229126
  252642970
  272638085
  302057362
  368757738
 4813893898
  272668223
  368759128
  302056968
 4457216598
  368765384
  368764933
 4605391944
 4185777342
 4167414022
 3532490073
  252644259
  274009228
 4433033370
  252643865
  252595003
 3745587445
  275623442
 4457170670
  302057802
 3728335033
 3892905407
  282861327
 1912524109
 3732852894
 4167229376
  252848019
 3741152582
  282836160
 3735788682
  273345280
 4605583525
 4452608411
  302057531
 3735707992
  302057614
 4601987406
 3861897463
  302057813
  302056347
  368755455
  273335915
 3698554303
 3727904706
 4167414031
  302057429
 3598378115
  302057378
 1907132820
 4187943275
  302057442
 4167393539
  368755460
  273330933
  252846928
  282836115
  252644187
  275623444
 4823300381
 4457170674
  252644373
  252644519
  302057713
  274008650
 1645804400
 4613624942
 4167414043
  302057428
 4167414033
 2035442316
  273336899
  302056820
  275627184
  302057376
  368759076
  302057435
 2379184061
  302057753
  272668221
 3861860655
 1645788016
 3598378113
  302057646
  252644209
 4167413995
  275098342
 4457199675
  302057738
 3598378120
  252847471
 4295730622
 5114501919
 3728170244
 1736930935
  368756157
  252643256
  252671583
 3610461008
  302057387
  252941766
 4432895610
 3716672142
 4185829426
  252965190
  252644254
 4452608412
 4187943254
  273345565
  302057400
  252643347
 3892846013
  252644258
  277049902
 4167414057
  582862531
 4457219494
  302057710
  252847472
  282862582
  302057688
  252643860
  252963460
  302057739
 3861860633
  302057648
  252966756
  252847023
  282838862
  302057423
  273336900
  275622449
  302056383
  368758222
 4444785275
  274950322
  302057528
  302057308
  272668871
 4167393538
 3735815064
  275626387
  302057649
 4185965162
  252643378
  252642902
  302057737
 4813893899
 3860680138
  302057420
 3727895703
 4167229144
 4452607299
  302057560
 3892905392
 3610490416
  252643243
 4185965150
  252965211
 3861860601
  252963457
  368757919
 4605583529
  252643864
  252643303
 5100184835
 1645743810
  302057758
 4167414020
  282842848
  277494810
 3860679977
 3600350748
 4167393527
  275626014
  282843549
 4601986814
 4167393542
  252642885
   34635572
 1583653541
 4167229312
  302057776
 3818345224
  302056816
 4603425709
 3860679991
  302057703
  275622448
  368756508
  368764488
 3598378122
 3698446004
  368750382
  302057419
  368760759
  302057532
 4167229388
  302057427
 4457170668
  302057797
  302057812
 4167393519
 1484145977
 3861909861
 4823260826
  252644186
 4601904668
  368750737
 3698416424
 4613609987
 5102901326
  272668226
 3861209654
 3860680103
  252941777
  582862447
  302057793
  274950325
  302057770
 3598378116
  252642891
 5102901325
 1736924823
  302057746
  275098340
  252965189
 3500168854
 3727501870
 4605391958
 4601987458
  252671584
  274009049
 4453931551
  252965025
  252643648
  252644374
 2035455568
 4687855893
  252847227
   34635565
 4452614247
  302057715
  252643613
  302057690
 3860679961
 4823300382
  252497485
 4454249845
 4167413992
  302057610
 3711677866
 1871355514
  273336625
  302056388
  252643625
  252643966
  252947721
  368759305
 3742720947
  252643376
 4605701036
 4167229368
  275623443
  302057121
 3698499840
  302056830
 1583653337
  272669251
 1640289563
  272637206
 4687855892
  302057521
 3728108990
  272667820
  302057335
 3861864658
  282838832
  282838812
  252643650
  274008855
  275627176
 4432965521
 4070546204
  302057786
 3116555931
 3735690988
 4061723027
  282838805
  302057777
 1831755144
  302057645
 3738023291
 4454024013
 4185965182
 3754802065
 4454261753
  368756227
 4167393536
 4613610493
  282861926
 3861209650
 3860665760
 4601986875
 3754896438
 4452607296
 4601904666
 4687892999
 4603425734
 3709241004
 3861864681
 4613645624
 4687855905
 4601904671
  252940806
 4185966892
 3727485769
 4454261754
 3727591700
 4457173392
 5086163041
 4613610494
  273335844
 3735788679
 4454249834
 4601987402
 3860680071
 4601904667
 4167393564
 3860659053
  302057530
 4446390439
 4167229290
 3861209656
 4187943271
 3727591702
 5085622785
 4696422128
 3727591713
 4601987591
 4452608418
 4605391936
 4457170675
 4167488999
 4613624933
 3732820991
  273335916
 4696422135
 4601987464
 5085225548
 4457025818
 4454024003
 4605390249
 4452614248
 5085816278
 4603425695
 3727591711
 3734665240
 3754896435
 4605430713
 4687837660
 4601904672
 4452614245
 4603425749
 3727522146
 3742740512
 4813893903
 4601987640
 3860680008
  302057613
  274008308
 4432965522
  367770211
 3739165968
  252643874
  252963462
 4613690401
  252594988
 4687855938
  273336897
 4687837662
 4613693494
 3727591710
 4454249832
 4687855897
 4613638509
 3735788678
 3698444727
 4167393543
 4601987401
 4454249836
 3727456375
 4433005451
 4452591043
 4453931554
 5160963791
 4601986865
 4454011702
  252594989
  302057439
 3860680042
 4452607305
 4167488998
 4605391909
 4601987405
 4687855903
 4601986843
 4601987474
 5254212914
 4167414058
 3861860621
 4687855907
  252945434
  252940800
 4098058757
  368755489
 3727591712
 4167393524
  273335846
 3729506150
 4590631593
  282838850
 4185965173
 4432895612
 4601987590
 4167393552
 4167229604
  252637264
 4605433822
 4452608088
 4454011701
 4457170666
 4167393523
 4167248190
 3818345223
 4601987643
 3860659003
  444402545
 4167413990
 4454249829
 3767768996
 4613629287
 4601987408
 3727591705
  282838875
 4457199673
 4167229614
 2625902096
 3860680045
 3768050272
 4613690400
 4457220498
 4452607303
 3734665247
 4185966894
 4603425717
 4167229610
 4185846505
 4603425726
 5086163037
 4613638492
 4167393506
 4687855900
 3739111326
 4613645621
 4167414054
 4687855899
 4452608416
 4167414039
 4453705615
 4613638491
 4601987410
  252643971
 4823260825
  282838837
 4605391946
 3861897467
 4454249981
 4613624927
 3727895684
  302057702
 3765570364
 3861897466
 5085506846
 4601987642
 4601986847
 4454261761
  302057768
 4613609978
 4452608089
 3860680038
 4605334769
 4605334910
 4613610492
 4167229303
 4605603442
 4457048562
 4601986828
 3727557361
 4823300379
 4457216604
 4605391928
 4457048557
 3822392515
  282838870
 4687855906
 4453931556
 5160963788
 4687855898
 4167248194
 4454261760
 4605391943
 4605391935
 3727456366
 3861209651
 4167393544
 4601987635
 3738023698
 4454249830
 4457055591
 4687855901
  252941761
 4167229295
 4605583530
 4687855895
 3860665813
 4601987592
 4452607313
 3860665789
 4605391938
 3890229027
 3766228232
 4185846497
 4061723033
 3825741157
 5062806306
 4605583363
  252945435
 4601986829
 4167229132
 4167229101
 4457055590
 4601987634
 4167229165
 5112575082
  302057386
 4601904670
  302057503
 4167229311
 3860665804
  252942374
 4687855909
 4167414024
 3860680052
 3818345226
 4185846508
 4452607300
 5112547197
 3729436037
  272663892
 4605430712
 4454011700
 4454264161
 5085622784
 3713460399
 4457170667
 4601986835
 4605583521
 3766727503
 3818345225
 3727485765
 4454048168
 4601986840
 4185846504
 4601986858
 4601904676
 4453626419
 4185966957
 4687855908
 4601987429
 4605701039
 4167488992
 3860680050
 4601987598
 4457048539
 4454249837
 4605433826
 4452608417
 4603425705
 4613645625
 4601986849
 4457219495
 4457173389
 4601986823
 4613629288
 4687855902
 4613624930
 4457025817
 4454249840
 5085506847
 3727591717
 4601987600
  282861927
 4167229366
 4605603437
 4167229354
 4185829438
 4603425696
 4605334909
 5160963792
 4167229171
 4603425731
 4457036258
 4452607310
 3739911911
 3721337803
  252643863
 4454261758
  272667140
 3728271275
 4167229605
 4598072047
 3727563836
 4613629898
 4432895603
 4823300384
 4453696481
 4601987428
 4601987475
 3735592831
 4452524755
 3754802111
 4605549662
 4605583522
 4185966956
 4601986855
 4457170669
 4167229159
 4613610495
 4601987638
 5086162588
 4452607306
  252678464
 4167229178
 5085225547
 4601987607
 4823260827
 4605334917
 4605549657
 4167229314
 4616004731
  302057609
  368756072
 4167414040
(1228 rows)

13.4.3. Solution to Chapter 8 Exercise 3

Exercise 3: Creating the function

CREATE FUNCTION

13.4.4. Solution to Chapter 8 Exercise 4

Exercise 4: Using the function

 seq | gid  |     name      |      length       |     the_time      |     azimuth      |                                                            geom
-----+------+---------------+-------------------+-------------------+------------------+----------------------------------------------------------------------------------------------------------------------------
   1 | 2029 | Garden Avenue | 0.120209032368071 | 0.144250838841685 | 231.186295092475 | 0102000020E610000002000000BB5F05F86EA5434064AF777FBC3F1BC0353A313553A54340B58828266F401BC0
   2 |  219 | Garden Avenue | 0.217965421167396 | 0.261558505400875 | 229.165120382668 | 0102000020E610000002000000353A313553A54340B58828266F401BC0369EBE5422A543402CE63114C1411BC0
   3 | 1772 | Ohio Street   | 0.211661459249381 | 0.253993751099257 | 319.868106058538 | 0102000020E610000003000000369EBE5422A543402CE63114C1411BC0774D486B0CA54340CBD8D0CDFE401BC01B88C0ECF9A44340613E0FA441401BC0
   4 | 1924 | Ohio Street   | 0.225258862979722 | 0.270310635575666 | 320.513109606255 | 0102000020E6100000020000001B88C0ECF9A44340613E0FA441401BC037774F79CFA44340F9ECCA78A53E1BC0
(4 rows)

 seq | gid  |     name      |      length       |     the_time      |     azimuth      |                                                            geom
-----+------+---------------+-------------------+-------------------+------------------+----------------------------------------------------------------------------------------------------------------------------
   1 | 2029 | Garden Avenue | 0.120209032368071 | 0.144250838841685 | 231.186295092475 | 0102000020E610000002000000BB5F05F86EA5434064AF777FBC3F1BC0353A313553A54340B58828266F401BC0
   2 |  219 | Garden Avenue | 0.217965421167396 | 0.261558505400875 | 229.165120382668 | 0102000020E610000002000000353A313553A54340B58828266F401BC0369EBE5422A543402CE63114C1411BC0
   3 | 1772 | Ohio Street   | 0.211661459249381 | 0.253993751099257 | 319.868106058538 | 0102000020E610000003000000369EBE5422A543402CE63114C1411BC0774D486B0CA54340CBD8D0CDFE401BC01B88C0ECF9A44340613E0FA441401BC0
   4 | 1924 | Ohio Street   | 0.225258862979722 | 0.270310635575666 | 320.513109606255 | 0102000020E6100000020000001B88C0ECF9A44340613E0FA441401BC037774F79CFA44340F9ECCA78A53E1BC0
(4 rows)

SELECT 4