5. SQL function¶
pgRouting functions provide low level interface.
When developing for a higher level application, the requirements need to be represented in the SQL queries. As these SQL queries get more complex, it is desirable to store them in postgreSQL stored procedures or functions. Stored procedures or functions are an effective way to wrap application logic, in this case, related to routing logic and requirements.
5.1. The application requirements¶
- A front end needs the following routing information:
seq- A unique identifier of the rowsid- The segment’s identifiername- The segment’s namelength- The segment’s lengthseconds- Number of seconds to traverse the segmentazimuth- The azimuth of the segmentroute_geom- The routing geometryroute_readable- The geometry in human readable form.
Design of the function
The function to be created wrk_dijkstra with the following input parameters and
output columns:
Input parameters
Parameter |
Type |
Description |
|---|---|---|
|
REGCLASS |
The table/view that is going to be used for processing |
|
BIGINT |
The OSM identifier of the departure location. |
|
BIGINT |
The OSM identifier of the destination location. |
output columns
Name |
Type |
Description |
|---|---|---|
|
INTEGER |
A unique number for each result row. |
|
BIGINT |
The edge identifier. |
|
TEXT |
The name of the segment. |
|
FLOAT |
The number of seconds it takes to traverse the segment. |
|
FLOAT |
The azimuth of the segment. |
|
FLOAT |
The leng in meters of the segment. |
|
TEXT |
The geometry in human readable form. |
|
geometry |
The geometry of the segment in the correct direction. |
Note
For the following exercises only vehicle_net will be used, but
you can test the queries with the other views.
5.2. Additional information handling¶
When the application needs additional information, like the name of the street,
JOIN the results with other tables.
5.2.1. Exercise 1: Get additional information¶
Problem
From Estação das Docas to Hangar Convention Center, using OSM identifiers.
Get the following information:
seqidnamesecondslength
Solution
The columns asked (line 2).
Rename
pgr_dijkstraresults to application requirements names. (line 4).LEFT JOINthe results withvehicle_netto get the additional information. (line 9)LEFTto include the row withid = -1because it does not exist onvehicle_net
1SELECT
2 seq, id, seconds, name, length
3FROM (
4 SELECT seq, edge AS id, node, cost AS seconds
5 FROM pgr_dijkstra(
6 'SELECT * FROM vehicle_net',
7 5661895682, 10982869752)
8 ) AS results
9LEFT JOIN vehicle_net USING (id)
10ORDER BY seq;
seq | id | seconds | name | length
-----+-------+---------------------+-----------------------------+--------------------
1 | 12332 | 15.524784130497913 | | 215.622001812471
2 | 21128 | 1.3832547478053674 | | 19.21187149729677
3 | 21130 | 1.9280336595020124 | | 26.778245270861284
4 | 21111 | 1.5797574712432596 | | 21.94107598948972
5 | 27014 | 2.7562902149372817 | Avenida Presidente Vargas | 38.28180854079558
6 | 25651 | 1.257276586974714 | Avenida Presidente Vargas | 17.46217481909325
7 | 1566 | 0.8261818244747761 | Avenida Presidente Vargas | 11.474747562149668
8 | 1468 | 2.5336780711955815 | Avenida Presidente Vargas | 35.18997321104975
9 | 25649 | 1.0902230020328585 | Rua Gaspar Viana | 15.141986139345258
10 | 1524 | 6.902771289375067 | Rua Gaspar Viana | 95.8718234635426
11 | 21106 | 11.379450793071967 | Rua Gaspar Viana | 158.0479276815551
12 | 21109 | 0.29521747905042584 | Rua Gaspar Viana | 4.100242764589248
13 | 1564 | 4.998629529373359 | Rua Gaspar Viana | 69.42541013018554
14 | 7904 | 4.282593276611086 | Travessa Piedade | 59.48046217515398
15 | 1160 | 6.290079099189824 | Travessa Piedade | 87.36220971096978
16 | 1174 | 3.902541587180251 | Travessa Piedade | 54.201966488614595
17 | 1527 | 5.366307517699673 | Travessa Piedade | 74.53204885693991
18 | 1526 | 3.847332889043566 | Travessa Piedade | 53.435179014493976
19 | 10180 | 1.1883313673895817 | Travessa Piedade | 16.504602324855302
20 | 1525 | 1.8206285826215138 | Travessa Piedade | 25.28650809196547
21 | 8012 | 4.3867454079934305 | Rua Aristides Lobo | 60.92701955546431
22 | 740 | 12.271269518712748 | Rua Aristides Lobo | 170.43429887101038
23 | 1544 | 8.736671095846257 | Rua Aristides Lobo | 121.34265410897578
24 | 737 | 8.768099590855243 | Rua Aristides Lobo | 121.77916098410061
25 | 16630 | 16.273468016800575 | Rua Aristides Lobo | 226.02038912223023
26 | 11105 | 0.8132364758273772 | Rua Aristides Lobo | 11.294951053158018
27 | 10427 | 1.190985214714288 | Rua Aristides Lobo | 16.541461315476223
28 | 25789 | 0.20484476263187593 | Travessa Diogo Móia | 2.8450661476649435
29 | 25786 | 0.31479771404687557 | Travessa Diogo Móia | 4.372190472873272
30 | 25785 | 0.8251417120487653 | Travessa Diogo Móia | 11.460301556232851
31 | 25788 | 0.13115464365250873 | Travessa Diogo Móia | 1.8215922729515102
32 | 493 | 0.6341116902606293 | Travessa Diogo Móia | 8.807106809175407
33 | 15074 | 0.513224475695611 | Rua Diogo Móia | 7.1281177179945985
34 | 16586 | 0.9521505332823582 | Rua Diogo Móia | 13.224312962254976
35 | 10291 | 6.79308438400355 | Rua Diogo Móia | 94.34839422227154
36 | 19918 | 4.042078698838966 | Rua Diogo Móia | 56.13998192831898
37 | 497 | 5.202097168286554 | Rua Diogo Móia | 72.25134955953547
38 | 13565 | 4.941927315603786 | Rua Diogo Móia | 68.63787938338592
39 | 7999 | 3.288842647190927 | Rua Diogo Móia | 45.67837009987399
40 | 21318 | 1.8920872342564647 | Rua Diogo Móia | 26.278989364673123
41 | 15902 | 5.824022206882501 | Rua Diogo Móia | 80.88919731781252
42 | 21316 | 1.993560974295293 | Rua Diogo Móia | 27.688346865212406
43 | 17311 | 0.4378734082628067 | Rua Diogo Móia | 6.081575114761204
44 | 21314 | 2.509473129812086 | Rua Diogo Móia | 34.853793469612306
45 | 121 | 4.244742767046388 | Rua Diogo Móia | 58.954760653422056
46 | 429 | 7.098003188105837 | Rua Diogo Móia | 98.58337761258107
47 | 21278 | 12.800597760163162 | Rua Diogo Móia | 177.78608000226615
48 | 423 | 6.233102186262337 | Rua Diogo Móia | 86.57086369808802
49 | 10264 | 16.956996232401103 | Rua Diogo Móia | 235.51383656112642
50 | 12981 | 10.652203704142801 | Rua Diogo Móia | 147.94727366865
51 | 23223 | 3.009517623726086 | Rua Diogo Móia | 41.79885588508453
52 | 13646 | 0.6546714011398842 | Rua Diogo Móia | 9.092658349165058
53 | 20637 | 5.091397734617478 | Rua Diogo Móia | 70.71385742524275
54 | 20326 | 6.2518419353429895 | Rua Diogo Móia | 86.83113799087485
55 | 20328 | 0.23016236153556516 | Rua Diogo Móia | 3.1966994657717382
56 | 20325 | 1.7318862463975737 | Rua Diogo Móia | 24.053975644410748
57 | 10426 | 0.523787790511925 | Rua Diogo Móia | 7.274830423776736
58 | 10710 | 3.7802204381742057 | Rua Diogo Móia | 52.50306164130841
59 | 10707 | 2.9621272391801856 | Rua Diogo Móia | 41.1406560997248
60 | 10708 | 9.713772946965593 | Passagem Ademar de Barros | 134.9135131522999
61 | 1643 | 4.866093309963141 | Rua Oliveira Belo | 67.58462930504362
62 | 1637 | 1.8450236691710133 | Rua Oliveira Belo | 25.625328738486296
63 | 26481 | 7.492375612832364 | Travessa Antônio Baena | 104.06077240044951
64 | 28009 | 1.2762150674274921 | | 17.72520926982628
65 | 30980 | 8.974698136787968 | Avenida Visconde de Inhaúma | 124.64858523316623
66 | 30967 | 11.426821303797581 | Avenida Visconde de Inhaúma | 158.7058514416331
67 | 31047 | 6.843449979048051 | Avenida Visconde de Inhaúma | 95.04791637566738
68 | 30968 | 5.371463742537426 | Avenida Visconde de Inhaúma | 74.60366309079758
69 | 13805 | 6.891165135191777 | Avenida Visconde de Inhaúma | 95.71062687766357
70 | 20575 | 5.029367334518458 | Avenida Visconde de Inhaúma | 69.85232409053414
71 | 27292 | 11.424085973770163 | Travessa Vileta | 158.66786074680783
72 | 18870 | 9.415570236830515 | Travessa Vileta | 130.77180884486827
73 | 7968 | 3.1844332113173706 | Travessa Vileta | 44.22823904607459
74 | 3254 | 0.8113013928027758 | Travessa Vileta | 11.268074900038552
75 | 17709 | 0.7922380389137641 | Travessa Vileta | 11.003306096024502
76 | 3155 | 0.9929476188366739 | Travessa Vileta | 13.790939150509361
77 | 18322 | 0.589059340205777 | Avenida Duque de Caxias | 9.817655670096284
78 | 15309 | 7.802332894772054 | Avenida Duque de Caxias | 130.03888157953423
79 | 3160 | 1.451915421445249 | Avenida Duque de Caxias | 24.19859035742082
80 | 11148 | 4.1291968838076585 | Avenida Duque de Caxias | 68.81994806346098
81 | 18328 | 5.071210660887403 | Avenida Duque de Caxias | 84.52017768145673
82 | 3165 | 0.6588005157910573 | Avenida Duque de Caxias | 10.98000859651762
83 | 15302 | 0.6624379620171468 | Avenida Duque de Caxias | 11.04063270028578
84 | 15299 | 7.64331893402423 | Avenida Duque de Caxias | 127.38864890040385
85 | 372 | 1.6185624439136164 | Avenida Duque de Caxias | 26.976040731893608
86 | 15291 | 1.5426950808720525 | Avenida Duque de Caxias | 25.71158468120088
87 | 10414 | 8.10018215092719 | Avenida Duque de Caxias | 135.0030358487865
88 | 10412 | 0.8010340812788996 | Avenida Duque de Caxias | 13.350568021314995
89 | 10755 | 0.32174055743410845 | Avenida Duque de Caxias | 5.362342623901808
90 | 15284 | 5.603200219924484 | Avenida Duque de Caxias | 93.38667033207474
91 | 1845 | 3.4154739303977477 | Avenida Duque de Caxias | 56.92456550662913
92 | 3174 | 4.719295193747098 | Avenida Duque de Caxias | 78.65491989578497
93 | 3172 | 2.494985805447332 | Avenida Duque de Caxias | 41.583096757455536
94 | 7938 | 1.1336853637583644 | Avenida Duque de Caxias | 18.89475606263941
95 | 27846 | 0.8904302856085814 | Avenida Duque de Caxias | 14.840504760143025
96 | 339 | 0.698022009641036 | Avenida Duque de Caxias | 11.633700160683935
97 | 27848 | 0.6564041634973928 | Avenida Duque de Caxias | 10.940069391623215
98 | 10348 | 0.9834944305474466 | Avenida Duque de Caxias | 16.391573842457444
99 | 10056 | 2.9402965329150215 | Avenida Duque de Caxias | 49.00494221525036
100 | 1727 | 5.355184246550181 | Avenida Duque de Caxias | 89.25307077583635
101 | 10929 | 3.8274834071623784 | Avenida Duque de Caxias | 63.79139011937298
102 | 1731 | 1.4213261487655227 | Avenida Duque de Caxias | 23.688769146092046
103 | 1733 | 4.631815146870902 | Avenida Duque de Caxias | 77.19691911451504
104 | 10921 | 3.940277020140121 | Avenida Duque de Caxias | 65.67128366900202
105 | 12969 | 0.8433302614957165 | Avenida Duque de Caxias | 14.055504358261944
106 | 10392 | 5.077008239497512 | Avenida Duque de Caxias | 84.6168039916252
107 | 12971 | 3.541402184491856 | Avenida Duque de Caxias | 59.02336974153094
108 | 24118 | 1.163994608512139 | Avenida Duque de Caxias | 19.399910141868986
109 | 1737 | 5.407282465448289 | Avenida Duque de Caxias | 90.12137442413815
110 | 1741 | 5.6631558817312015 | Avenida Duque de Caxias | 94.3859313621867
111 | 24307 | 2.8288056582150825 | Avenida Duque de Caxias | 47.14676097025138
112 | 10919 | 1.5788656574755486 | Avenida Duque de Caxias | 26.31442762459248
113 | 99 | 1.6125042321092125 | Avenida Duque de Caxias | 26.875070535153544
114 | 97 | 1.4555596260266292 | Avenida Brigadeiro Protázio | 20.21610591703652
115 | 15816 | 0.7379112639781683 | Avenida Doutor Freitas | 10.248767555252337
116 | 510 | 1.0674675004507879 | Avenida Doutor Freitas | 14.825937506260944
117 | 26944 | 0.9357212700059486 | Avenida Doutor Freitas | 12.99612875008262
118 | -1 | 0 | |
(118 rows)
5.3. Geometry handling¶
From pgRouting point of view, the geometry is part of the additional
information, needed on the results for an application. Therefore JOIN the
results with other tables that contain the geometry and for further processing
with PostGIS functions.
5.3.1. Exercise 2: Route geometry (human readable)¶
Problem
Route from the Estação das Docas to Hangar Convention Center
Additionally to the previous exercise, get the
geometry
geomin human readable form named asroute_readable
Tip
WITH provides a way to write auxiliary statements in larger queries.
It can be thought of as defining temporary tables that exist just for one query.
Solution
The routing query named
resultsin a WITH clause. (lines 2 to 5)The results from the previous exercise. (lines 8 and 9)
Note
For result reading purposes, the result columns from the previous are in a comment. Uncomment to see the complete results for the problem.
The
geomprocessed withST_AsTextto get the human readable form. (line 12)Renaming the result to
route_readable
The
LEFT JOINwithvehicle_net. (line 14)
1WITH
2results AS ( -- line 2
3 SELECT seq, edge AS id, node, cost AS seconds
4 FROM pgr_dijkstra('SELECT * FROM vehicle_net', 5661895682, 10982869752)
5)
6SELECT
7 -- from previous excercise
8 seq, -- line 8
9 -- id, seconds, name, length,
10
11 -- additionally get
12 ST_AsText(geom) AS route_readable --line 12
13FROM results
14LEFT JOIN vehicle_net USING (id) -- line 14
15ORDER BY seq;
seq | route_readable
-----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | LINESTRING(-48.5002935 -1.4491382,-48.5002057 -1.4490799,-48.5001265 -1.4490202,-48.4998269 -1.4486681,-48.4997338 -1.4485607,-48.4996364 -1.4484672,-48.4995559 -1.4484022,-48.4994587 -1.4483512,-48.4993561 -1.4482882,-48.4993017 -1.4482467,-48.4991133 -1.4480664,-48.4990966 -1.4480348,-48.4990878 -1.4479913,-48.4991428 -1.44774)
2 | LINESTRING(-48.4991428 -1.44774,-48.4990724 -1.4477339,-48.4989705 -1.4477379)
3 | LINESTRING(-48.4989705 -1.4477379,-48.4989886 -1.4477975,-48.4989873 -1.4478592,-48.4989685 -1.4479142,-48.498935 -1.4479638)
4 | LINESTRING(-48.498935 -1.4479638,-48.4987405 -1.4479313)
5 | LINESTRING(-48.4987405 -1.4479313,-48.4985465 -1.4482172)
6 | LINESTRING(-48.4985465 -1.4482172,-48.4984527 -1.4483438)
7 | LINESTRING(-48.4984527 -1.4483438,-48.4983947 -1.4484296)
8 | LINESTRING(-48.4983947 -1.4484296,-48.4982262 -1.4486989)
9 | LINESTRING(-48.4982262 -1.4486989,-48.4981152 -1.4486197)
10 | LINESTRING(-48.4981152 -1.4486197,-48.4973973 -1.4481404)
11 | LINESTRING(-48.4973973 -1.4481404,-48.4962964 -1.4474314,-48.4962011 -1.4473699)
12 | LINESTRING(-48.4962011 -1.4473699,-48.4961697 -1.4473505)
13 | LINESTRING(-48.4961697 -1.4473505,-48.4956471 -1.4470076)
14 | LINESTRING(-48.4956471 -1.4470076,-48.4953584 -1.4474603)
15 | LINESTRING(-48.4953584 -1.4474603,-48.494943 -1.4481307)
16 | LINESTRING(-48.494943 -1.4481307,-48.4946591 -1.448529)
17 | LINESTRING(-48.4946591 -1.448529,-48.4942646 -1.4490737)
18 | LINESTRING(-48.4942646 -1.4490737,-48.4939959 -1.4494742)
19 | LINESTRING(-48.4939959 -1.4494742,-48.493915 -1.4495993)
20 | LINESTRING(-48.493915 -1.4495993,-48.4938034 -1.4497985)
21 | LINESTRING(-48.4938034 -1.4497985,-48.4933626 -1.4494717)
22 | LINESTRING(-48.4933626 -1.4494717,-48.4921294 -1.4485577)
23 | LINESTRING(-48.4921294 -1.4485577,-48.4912453 -1.4479154)
24 | LINESTRING(-48.4912453 -1.4479154,-48.4903663 -1.4472594)
25 | LINESTRING(-48.4903663 -1.4472594,-48.4887232 -1.4460579)
26 | LINESTRING(-48.4887232 -1.4460579,-48.4886407 -1.4459984)
27 | LINESTRING(-48.4886407 -1.4459984,-48.4885213 -1.4459093)
28 | LINESTRING(-48.4885213 -1.4459093,-48.4885012 -1.4458934)
29 | LINESTRING(-48.4885012 -1.4458934,-48.4884706 -1.4458686)
30 | LINESTRING(-48.4884706 -1.4458686,-48.4883886 -1.4458059)
31 | LINESTRING(-48.4883886 -1.4458059,-48.4883747 -1.4457972)
32 | LINESTRING(-48.4883747 -1.4457972,-48.488312 -1.4457486)
33 | LINESTRING(-48.488312 -1.4457486,-48.4882586 -1.445713)
34 | LINESTRING(-48.4882586 -1.445713,-48.4881629 -1.4456421)
35 | LINESTRING(-48.4881629 -1.4456421,-48.4874804 -1.4451359)
36 | LINESTRING(-48.4874804 -1.4451359,-48.4870783 -1.4448293)
37 | LINESTRING(-48.4870783 -1.4448293,-48.486549 -1.4444509)
38 | LINESTRING(-48.486549 -1.4444509,-48.4860067 -1.4441552)
39 | LINESTRING(-48.4860067 -1.4441552,-48.4856347 -1.4439806)
40 | LINESTRING(-48.4856347 -1.4439806,-48.4854193 -1.4438832)
41 | LINESTRING(-48.4854193 -1.4438832,-48.485021 -1.4437031,-48.4847526 -1.443592)
42 | LINESTRING(-48.4847526 -1.443592,-48.484519 -1.4435058)
43 | LINESTRING(-48.484519 -1.4435058,-48.4844675 -1.4434874)
44 | LINESTRING(-48.4844675 -1.4434874,-48.4841737 -1.4433782)
45 | LINESTRING(-48.4841737 -1.4433782,-48.4840192 -1.4433237,-48.4836722 -1.4432064)
46 | LINESTRING(-48.4836722 -1.4432064,-48.4829766 -1.4429586,-48.4828336 -1.4429203)
47 | LINESTRING(-48.4828336 -1.4429203,-48.4813048 -1.4424536)
48 | LINESTRING(-48.4813048 -1.4424536,-48.4805616 -1.4422223)
49 | LINESTRING(-48.4805616 -1.4422223,-48.4785543 -1.4415475)
50 | LINESTRING(-48.4785543 -1.4415475,-48.4772324 -1.4414051)
51 | LINESTRING(-48.4772324 -1.4414051,-48.4768596 -1.441359)
52 | LINESTRING(-48.4768596 -1.441359,-48.4767785 -1.441349)
53 | LINESTRING(-48.4767785 -1.441349,-48.4761448 -1.4413018)
54 | LINESTRING(-48.4761448 -1.4413018,-48.4753701 -1.4412082)
55 | LINESTRING(-48.4753701 -1.4412082,-48.475342 -1.4412022)
56 | LINESTRING(-48.475342 -1.4412022,-48.4751295 -1.4411624)
57 | LINESTRING(-48.4751295 -1.4411624,-48.4750651 -1.4411511)
58 | LINESTRING(-48.4750651 -1.4411511,-48.4746033 -1.4410539)
59 | LINESTRING(-48.4746033 -1.4410539,-48.4742412 -1.4409789)
60 | LINESTRING(-48.4745617 -1.4398022,-48.4742412 -1.4409789)
61 | LINESTRING(-48.4745617 -1.4398022,-48.47413 -1.4396471,-48.4739923 -1.43959)
62 | LINESTRING(-48.4739923 -1.43959,-48.4738232 -1.4394327)
63 | LINESTRING(-48.4738232 -1.4394327,-48.4734106 -1.4395805,-48.4729262 -1.439693)
64 | LINESTRING(-48.4729262 -1.439693,-48.4729169 -1.4396395,-48.4729068 -1.4395979,-48.4728891 -1.4395373)
65 | LINESTRING(-48.4728891 -1.4395373,-48.4721387 -1.4387004)
66 | LINESTRING(-48.4721387 -1.4387004,-48.4712263 -1.4375973)
67 | LINESTRING(-48.4712263 -1.4375973,-48.4706778 -1.4369384)
68 | LINESTRING(-48.4706778 -1.4369384,-48.4702411 -1.4364265)
69 | LINESTRING(-48.4702411 -1.4364265,-48.4702312 -1.4364149,-48.4697076 -1.4357476)
70 | LINESTRING(-48.4697076 -1.4357476,-48.4692971 -1.4352697)
71 | LINESTRING(-48.4692971 -1.4352697,-48.4681118 -1.4360672)
72 | LINESTRING(-48.4681118 -1.4360672,-48.4671349 -1.4367245)
73 | LINESTRING(-48.4671349 -1.4367245,-48.4668045 -1.4369468)
74 | LINESTRING(-48.4668045 -1.4369468,-48.4667203 -1.4370034)
75 | LINESTRING(-48.4667203 -1.4370034,-48.4666385 -1.4370593)
76 | LINESTRING(-48.4666385 -1.4370593,-48.4665354 -1.4371285)
77 | LINESTRING(-48.4665354 -1.4371285,-48.4664868 -1.4370544)
78 | LINESTRING(-48.4664868 -1.4370544,-48.465844 -1.4360723)
79 | LINESTRING(-48.465844 -1.4360723,-48.4657246 -1.4358894)
80 | LINESTRING(-48.4657246 -1.4358894,-48.4653756 -1.4353756)
81 | LINESTRING(-48.4653756 -1.4353756,-48.4649634 -1.4347336)
82 | LINESTRING(-48.4649634 -1.4347336,-48.4649079 -1.4346515)
83 | LINESTRING(-48.4649079 -1.4346515,-48.4648532 -1.4345682)
84 | LINESTRING(-48.4648532 -1.4345682,-48.4642006 -1.4336217)
85 | LINESTRING(-48.4642006 -1.4336217,-48.464072 -1.4334149)
86 | LINESTRING(-48.464072 -1.4334149,-48.4639398 -1.4332242)
87 | LINESTRING(-48.4639398 -1.4332242,-48.4632933 -1.4321911)
88 | LINESTRING(-48.4632933 -1.4321911,-48.4632299 -1.4320886)
89 | LINESTRING(-48.4632299 -1.4320886,-48.4632037 -1.4320479)
90 | LINESTRING(-48.4632037 -1.4320479,-48.4628241 -1.4315024,-48.4627215 -1.4313567)
91 | LINESTRING(-48.4627215 -1.4313567,-48.4624439 -1.4309243)
92 | LINESTRING(-48.4624439 -1.4309243,-48.462054 -1.430331)
93 | LINESTRING(-48.462054 -1.430331,-48.4618505 -1.4300156)
94 | LINESTRING(-48.4618505 -1.4300156,-48.4617568 -1.4298731)
95 | LINESTRING(-48.4617568 -1.4298731,-48.4616814 -1.4297624)
96 | LINESTRING(-48.4616814 -1.4297624,-48.4616238 -1.4296746)
97 | LINESTRING(-48.4616238 -1.4296746,-48.4615685 -1.4295928)
98 | LINESTRING(-48.4615685 -1.4295928,-48.4614857 -1.4294702)
99 | LINESTRING(-48.4614857 -1.4294702,-48.4612304 -1.4291091)
100 | LINESTRING(-48.4612304 -1.4291091,-48.4607873 -1.4284363)
101 | LINESTRING(-48.4607873 -1.4284363,-48.4604752 -1.4279524)
102 | LINESTRING(-48.4604752 -1.4279524,-48.4603566 -1.4277745)
103 | LINESTRING(-48.4603566 -1.4277745,-48.4599639 -1.427199)
104 | LINESTRING(-48.4599639 -1.427199,-48.4598934 -1.4270889,-48.4596442 -1.4266998)
105 | LINESTRING(-48.4596442 -1.4266998,-48.4595784 -1.4265913)
106 | LINESTRING(-48.4595784 -1.4265913,-48.4591475 -1.4259608)
107 | LINESTRING(-48.4591475 -1.4259608,-48.4588567 -1.4255144)
108 | LINESTRING(-48.4588567 -1.4255144,-48.4587574 -1.4253702)
109 | LINESTRING(-48.4587574 -1.4253702,-48.458296 -1.4247004)
110 | LINESTRING(-48.458296 -1.4247004,-48.4578312 -1.4239864)
111 | LINESTRING(-48.4578312 -1.4239864,-48.4576142 -1.4236202)
112 | LINESTRING(-48.4576142 -1.4236202,-48.4574931 -1.4234158)
113 | LINESTRING(-48.4574931 -1.4234158,-48.4574417 -1.4233478,-48.4573359 -1.4232316)
114 | LINESTRING(-48.4573359 -1.4232316,-48.4572025 -1.4231075)
115 | LINESTRING(-48.4572025 -1.4231075,-48.4572595 -1.4230347)
116 | LINESTRING(-48.4572595 -1.4230347,-48.4573478 -1.4229343)
117 | LINESTRING(-48.4573478 -1.4229343,-48.4574274 -1.4228483)
118 |
(118 rows)
5.3.2. Exercise 3: Route geometry (binary format)¶
Problem
Route from the Estação das Docas to Hangar Convention Center
Additionally to the previous exercise, get the
geomin binary format with the nameroute_geom
Solution
The query from the previous exercise is used
The
SELECTclause also contains:The
geomincluding the renaming (line 9)
1WITH results AS (
2 SELECT seq, edge AS id, node, cost AS seconds
3 FROM pgr_dijkstra('SELECT * FROM vehicle_net', 5661895682, 10982869752)
4 )
5SELECT
6 -- from previous excercise
7 seq,
8 -- id, seconds, name, length, ST_AsText(geom) AS route_readable,
9
10 -- additionally get
11 geom AS route_geom
12FROM results
13LEFT JOIN vehicle_net
14 USING (id)
15ORDER BY seq;
seq | route_geom
-----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | 0102000020E61000000E00000060730E9E094048C01F268689AB2FF7BFEB6289BD064048C0BCA7BC676E2FF7BF75AE2825044048C0E2E423CE2F2FF7BF9E94EE53FA3F48C0A422049ABE2DF7BF58DAF346F73F48C079550CFC4D2DF7BF12F2E615F43F48C0524255F1EB2CF7BF0D839E72F13F48C06F4507C9A72CF7BFF1193F43EE3F48C02BF5D14E722CF7BF704793E6EA3F48C05EEC623F302CF7BF292B3C1EE93F48C087A350BB042CF7BFD33FD2F1E23F48C0B8FB66AC472BF7BFBC45BB65E23F48C0B784D789262BF7BF926AE91BE23F48C0C847E6ECF82AF7BF560449E9E33F48C0D2C6116BF129F7BF
2 | 0102000020E610000003000000560449E9E33F48C0D2C6116BF129F7BF0A2BBA9AE13F48C0F3F79C05EB29F7BF9A95ED43DE3F48C020E05A37EF29F7BF
3 | 0102000020E6100000050000009A95ED43DE3F48C020E05A37EF29F7BFD509C3DBDE3F48C060B01BB62D2AF7BF474FDBD0DE3F48C0506793686E2AF7BFF99D2633DE3F48C0C29F8614A82AF7BF36EA211ADD3F48C08FABEC16DC2AF7BF
4 | 0102000020E61000000200000036EA211ADD3F48C08FABEC16DC2AF7BF68588CBAD63F48C01EADC502BA2AF7BF
5 | 0102000020E61000000200000068588CBAD63F48C01EADC502BA2AF7BF8484285FD03F48C0371378CCE52BF7BF
6 | 0102000020E6100000020000008484285FD03F48C0371378CCE52BF7BF2C8D4E4CCD3F48C070D4658C6A2CF7BF
7 | 0102000020E6100000020000002C8D4E4CCD3F48C070D4658C6A2CF7BFF77FC465CB3F48C00DEF2884C42CF7BF
8 | 0102000020E610000002000000F77FC465CB3F48C00DEF2884C42CF7BF538149E0C53F48C0CF04D4E5DE2DF7BF
9 | 0102000020E610000002000000538149E0C53F48C0CF04D4E5DE2DF7BFFCD1263DC23F48C01876BDD98B2DF7BF
10 | 0102000020E610000002000000FCD1263DC23F48C01876BDD98B2DF7BF324FF8B6AA3F48C00143A044952BF7BF
11 | 0102000020E610000003000000324FF8B6AA3F48C00143A044952BF7BFAF8FF3A3863F48C08341E3D3AD28F7BF9F5E8484833F48C0C86F1B576D28F7BF
12 | 0102000020E6100000020000009F5E8484833F48C0C86F1B576D28F7BF11621D7D823F48C0859675FF5828F7BF
13 | 0102000020E61000000200000011621D7D823F48C0859675FF5828F7BFBA6D3A5D713F48C0E403F170F126F7BF
14 | 0102000020E610000002000000BA6D3A5D713F48C0E403F170F126F7BF42E66FE7673F48C03222AC21CC28F7BF
15 | 0102000020E61000000200000042E66FE7673F48C03222AC21CC28F7BF2B31CF4A5A3F48C06056CD188B2BF7BF
16 | 0102000020E6100000020000002B31CF4A5A3F48C06056CD188B2BF7BF9B9548FD503F48C0DDEBA4BE2C2DF7BF
17 | 0102000020E6100000020000009B9548FD503F48C0DDEBA4BE2C2DF7BF0749FA0F443F48C042E66FE7672FF7BF
18 | 0102000020E6100000020000000749FA0F443F48C042E66FE7672FF7BFD66DF5413B3F48C00C55D6DB0B31F7BF
19 | 0102000020E610000002000000D66DF5413B3F48C00C55D6DB0B31F7BF7E00529B383F48C034DF1C098F31F7BF
20 | 0102000020E6100000020000007E00529B383F48C034DF1C098F31F7BFA9D326F3343F48C040A374E95F32F7BF
21 | 0102000020E610000002000000A9D326F3343F48C040A374E95F32F7BFE6087481263F48C0EFA3BF3C0931F7BF
22 | 0102000020E610000002000000E6087481263F48C0EFA3BF3C0931F7BF37339F18FE3E48C057E7BDD64A2DF7BF
23 | 0102000020E61000000200000037339F18FE3E48C057E7BDD64A2DF7BF28E84020E13E48C003FFA556A92AF7BF
24 | 0102000020E61000000200000028E84020E13E48C003FFA556A92AF7BFBFC7AA52C43E48C0E041FD78F927F7BF
25 | 0102000020E610000002000000BFC7AA52C43E48C0E041FD78F927F7BFF365587B8E3E48C082F5C99B0D23F7BF
26 | 0102000020E610000002000000F365587B8E3E48C082F5C99B0D23F7BF4DFF48C78B3E48C0DD17E137CF22F7BF
27 | 0102000020E6100000020000004DFF48C78B3E48C0DD17E137CF22F7BF2073AFDD873E48C04FB747CA7122F7BF
28 | 0102000020E6100000020000002073AFDD873E48C04FB747CA7122F7BF44071335873E48C03409281E6122F7BF
29 | 0102000020E61000000200000044071335873E48C03409281E6122F7BF5C076234863E48C04D03F51C4722F7BF
30 | 0102000020E6100000020000005C076234863E48C04D03F51C4722F7BF9F5E8484833E48C051D20D5E0522F7BF
31 | 0102000020E6100000020000009F5E8484833E48C051D20D5E0522F7BFCF58EA0F833E48C0BA5FAA3EFC21F7BF
32 | 0102000020E610000002000000CF58EA0F833E48C0BA5FAA3EFC21F7BF471FF301813E48C0F8CDB348C921F7BF
33 | 0102000020E610000002000000471FF301813E48C0F8CDB348C921F7BFD17EFF417F3E48C0CA6E66F4A321F7BF
34 | 0102000020E610000002000000D17EFF417F3E48C0CA6E66F4A321F7BF6D4F351F7C3E48C03D88539C5921F7BF
35 | 0102000020E6100000020000006D4F351F7C3E48C03D88539C5921F7BF73B8FBC1653E48C08E684CD2461FF7BF
36 | 0102000020E61000000200000073B8FBC1653E48C08E684CD2461FF7BFAF8BEC94583E48C057D7FC53051EF7BF
37 | 0102000020E610000002000000AF8BEC94583E48C057D7FC53051EF7BF6473D53C473E48C022D8028C781CF7BF
38 | 0102000020E6100000020000006473D53C473E48C022D8028C781CF7BF8511B177353E48C0CC92A57B421BF7BF
39 | 0102000020E6100000020000008511B177353E48C0CC92A57B421BF7BFC1262147293E48C071EFD0668B1AF7BF
40 | 0102000020E610000002000000C1262147293E48C071EFD0668B1AF7BF712C3938223E48C0B7E63345251AF7BF
41 | 0102000020E610000003000000712C3938223E48C0B7E63345251AF7BFC66F0A2B153E48C01E24FA6B6819F7BF53D3895F0C3E48C09546CCECF318F7BF
42 | 0102000020E61000000200000053D3895F0C3E48C09546CCECF318F7BF33A5F5B7043E48C08E61A9899918F7BF
43 | 0102000020E61000000200000033A5F5B7043E48C08E61A9899918F7BFC93CF207033E48C05602733E8618F7BF
44 | 0102000020E610000002000000C93CF207033E48C05602733E8618F7BFAA8A5F67F93D48C049264CBD1318F7BF
45 | 0102000020E610000003000000AA8A5F67F93D48C049264CBD1318F7BF6B515557F43D48C0DEAA9097DA17F7BF007E7CF8E83D48C0DB0B16985F17F7BF
46 | 0102000020E610000003000000007E7CF8E83D48C0DB0B16985F17F7BFDCDD5E2DD23D48C00DB6C7C15B16F7BF79B4CC7DCD3D48C08CC0B3983316F7BF
47 | 0102000020E61000000200000079B4CC7DCD3D48C08CC0B3983316F7BF33B44B659B3D48C09EF5DF394A14F7BF
48 | 0102000020E61000000200000033B44B659B3D48C09EF5DF394A14F7BF52DBE10A833D48C08CFDC0B05713F7BF
49 | 0102000020E61000000200000052DBE10A833D48C08CFDC0B05713F7BF1AEE6D44413D48C0C616821C9410F7BF
50 | 0102000020E6100000020000001AEE6D44413D48C0C616821C9410F7BFB94B87F3153D48C00D9A4CCBFE0FF7BF
51 | 0102000020E610000002000000B94B87F3153D48C00D9A4CCBFE0FF7BF4E6441BC093D48C067B96C74CE0FF7BF
52 | 0102000020E6100000020000004E6441BC093D48C067B96C74CE0FF7BFCD77F013073D48C0F5F411F8C30FF7BF
53 | 0102000020E610000002000000CD77F013073D48C0F5F411F8C30FF7BF8A141450F23C48C0A9A7EA79920FF7BF
54 | 0102000020E6100000020000008A141450F23C48C0A9A7EA79920FF7BF857F6CEDD83C48C0E7A15B54300FF7BF
55 | 0102000020E610000002000000857F6CEDD83C48C0E7A15B54300FF7BF2635B401D83C48C0A3C5BE092A0FF7BF
56 | 0102000020E6100000020000002635B401D83C48C0A3C5BE092A0FF7BFB3EE1F0BD13C48C01299034E000FF7BF
57 | 0102000020E610000002000000B3EE1F0BD13C48C01299034E000FF7BF49FCE5EECE3C48C0C482B174F40EF7BF
58 | 0102000020E61000000200000049FCE5EECE3C48C0C482B174F40EF7BF6E090ACDBF3C48C0405FC4888E0EF7BF
59 | 0102000020E6100000020000006E090ACDBF3C48C0405FC4888E0EF7BF393586EFB33C48C0EB9D1BE43F0EF7BF
60 | 0102000020E61000000200000093B71270BE3C48C074571B086E09F7BF393586EFB33C48C0EB9D1BE43F0EF7BF
61 | 0102000020E61000000300000093B71270BE3C48C074571B086E09F7BFB806B64AB03C48C0F77FC465CB08F7BF258799C7AB3C48C0D4601A868F08F7BF
62 | 0102000020E610000002000000258799C7AB3C48C0D4601A868F08F7BF040B163DA63C48C00BB03495EA07F7BF
63 | 0102000020E610000003000000040B163DA63C48C00BB03495EA07F7BF354AF2B7983C48C06859F78F8508F7BFF63581D8883C48C0677BF486FB08F7BF
64 | 0102000020E610000004000000F63581D8883C48C0677BF486FB08F7BFE49C7D8A883C48C0077AA86DC308F7BF2B07C435883C48C0953EBECE9708F7BF449149A1873C48C04AF48D435808F7BF
65 | 0102000020E610000002000000449149A1873C48C04AF48D435808F7BF86D6790A6F3C48C0B8DFEBB5EA04F7BF
66 | 0102000020E61000000200000086D6790A6F3C48C0B8DFEBB5EA04F7BFEEC1B524513C48C01F16C5066600F7BF
67 | 0102000020E610000002000000EEC1B524513C48C01F16C5066600F7BF03FA8E2B3F3C48C075DDA51EB3FDF6BF
68 | 0102000020E61000000200000003FA8E2B3F3C48C075DDA51EB3FDF6BF17DE40DC303C48C052B9895A9AFBF6BF
69 | 0102000020E61000000300000017DE40DC303C48C052B9895A9AFBF6BF88C73489303C48C034CBAF308EFBF6BF6157EE601F3C48C0C4F7B479D2F8F6BF
70 | 0102000020E6100000020000006157EE601F3C48C0C4F7B479D2F8F6BFC74D68ED113C48C02409675CDDF6F6BF
71 | 0102000020E610000002000000C74D68ED113C48C02409675CDDF6F6BF94EF6316EB3B48C090BBAD9921FAF6BF
72 | 0102000020E61000000200000094EF6316EB3B48C090BBAD9921FAF6BFFD288F13CB3B48C08ECA4DD4D2FCF6BF
73 | 0102000020E610000002000000FD288F13CB3B48C08ECA4DD4D2FCF6BF1590F63FC03B48C03B7881EDBBFDF6BF
74 | 0102000020E6100000020000001590F63FC03B48C03B7881EDBBFDF6BF8D70A47DBD3B48C058DAF346F7FDF6BF
75 | 0102000020E6100000020000008D70A47DBD3B48C058DAF346F7FDF6BFFA4674CFBA3B48C03A9A7EE431FEF6BF
76 | 0102000020E610000002000000FA4674CFBA3B48C03A9A7EE431FEF6BF90B6966EB73B48C080643A747AFEF6BF
77 | 0102000020E61000000200000090B6966EB73B48C080643A747AFEF6BF0202E7D6B53B48C09C2A29C12CFEF6BF
78 | 0102000020E6100000020000000202E7D6B53B48C09C2A29C12CFEF6BFD784B4C6A03B48C06310B3F226FAF6BF
79 | 0102000020E610000002000000D784B4C6A03B48C06310B3F226FAF6BFAAF81ADD9C3B48C0DDC4DA2967F9F6BF
80 | 0102000020E610000002000000AAF81ADD9C3B48C0DDC4DA2967F9F6BF9E2D7B6D913B48C03E9FB7674CF7F6BF
81 | 0102000020E6100000020000009E2D7B6D913B48C03E9FB7674CF7F6BF226BB2EB833B48C0BA8E2738ABF4F6BF
82 | 0102000020E610000002000000226BB2EB833B48C0BA8E2738ABF4F6BF7613211A823B48C07B849A2155F4F6BF
83 | 0102000020E6100000020000007613211A823B48C07B849A2155F4F6BF71B8454F803B48C0FB1AEEC8FDF3F6BF
84 | 0102000020E61000000200000071B8454F803B48C0FB1AEEC8FDF3F6BF4CE4DDEC6A3B48C0F15FC54E1DF0F6BF
85 | 0102000020E6100000020000004CE4DDEC6A3B48C0F15FC54E1DF0F6BFA27E17B6663B48C0F595517644EFF6BF
86 | 0102000020E610000002000000A27E17B6663B48C0F595517644EFF6BF09281E61623B48C0495FAD7F7CEEF6BF
87 | 0102000020E61000000200000009281E61623B48C0495FAD7F7CEEF6BF5BFAE1314D3B48C0CDF4013741EAF6BF
88 | 0102000020E6100000020000005BFAE1314D3B48C0CDF4013741EAF6BFC2830B1E4B3B48C03F975FBCD5E9F6BF
89 | 0102000020E610000002000000C2830B1E4B3B48C03F975FBCD5E9F6BF6F7143424A3B48C03DE30C0FABE9F6BF
90 | 0102000020E6100000030000006F7143424A3B48C03DE30C0FABE9F6BF7BA6F2D13D3B48C00354820F6FE7F6BFFAD346753A3B48C057917648D6E6F6BF
91 | 0102000020E610000002000000FAD346753A3B48C057917648D6E6F6BF0A5E995C313B48C0BCD3F8E010E5F6BF
92 | 0102000020E6100000020000000A5E995C313B48C0BCD3F8E010E5F6BF357EE195243B48C0954737C2A2E2F6BF
93 | 0102000020E610000002000000357EE195243B48C0954737C2A2E2F6BF1492CCEA1D3B48C02C51AC0958E1F6BF
94 | 0102000020E6100000020000001492CCEA1D3B48C02C51AC0958E1F6BF525AC9D81A3B48C0D8E19E9DC2E0F6BF
95 | 0102000020E610000002000000525AC9D81A3B48C0D8E19E9DC2E0F6BFF4154960183B48C0BACED0894EE0F6BF
96 | 0102000020E610000002000000F4154960183B48C0BACED0894EE0F6BF12071A7D163B48C007C02E79F2DFF6BF
97 | 0102000020E61000000200000012071A7D163B48C007C02E79F2DFF6BF902E36AD143B48C0988D29B39CDFF6BF
98 | 0102000020E610000002000000902E36AD143B48C0988D29B39CDFF6BF2C89A2F6113B48C08CB4F9241CDFF6BF
99 | 0102000020E6100000020000002C89A2F6113B48C08CB4F9241CDFF6BFE3F50599093B48C0E9A7EE80A1DDF6BF
100 | 0102000020E610000002000000E3F50599093B48C0E9A7EE80A1DDF6BFC1F40714FB3A48C03AB58E05E0DAF6BF
101 | 0102000020E610000002000000C1F40714FB3A48C03AB58E05E0DAF6BF3D4FF2D9F03A48C055EAA39DE4D8F6BF
102 | 0102000020E6100000020000003D4FF2D9F03A48C055EAA39DE4D8F6BFB6BF0EF7EC3A48C00801F9122AD8F6BF
103 | 0102000020E610000002000000B6BF0EF7EC3A48C00801F9122AD8F6BF99EBD918E03A48C079245E9ECED5F6BF
104 | 0102000020E61000000300000099EBD918E03A48C079245E9ECED5F6BFB85274C9DD3A48C0FBC09F2B5BD5F6BFE665039FD53A48C0195B632BC3D3F6BF
105 | 0102000020E610000002000000E665039FD53A48C0195B632BC3D3F6BF58F90A77D33A48C04721246651D3F6BF
106 | 0102000020E61000000200000058F90A77D33A48C04721246651D3F6BF24456458C53A48C0460C9645BCD0F6BF
107 | 0102000020E61000000200000024456458C53A48C0460C9645BCD0F6BF7606FCD0BB3A48C00CA2FF2FE8CEF6BF
108 | 0102000020E6100000020000007606FCD0BB3A48C00CA2FF2FE8CEF6BF25E6FE8FB83A48C071169BFB50CEF6BF
109 | 0102000020E61000000200000025E6FE8FB83A48C071169BFB50CEF6BF9DF17D71A93A48C0E59189A592CBF6BF
110 | 0102000020E6100000020000009DF17D71A93A48C0E59189A592CBF6BF528B77369A3A48C02E2E9FF6A5C8F6BF
111 | 0102000020E610000002000000528B77369A3A48C02E2E9FF6A5C8F6BFB597231A933A48C0B7CC8EF925C7F6BF
112 | 0102000020E610000002000000B597231A933A48C0B7CC8EF925C7F6BFA55247228F3A48C03CC159A54FC6F6BF
113 | 0102000020E610000003000000A55247228F3A48C03CC159A54FC6F6BFCFA91A738D3A48C03756BD5708C6F6BFB3E496FB893A48C0DB238A7F8EC5F6BF
114 | 0102000020E610000002000000B3E496FB893A48C0DB238A7F8EC5F6BF21938C9C853A48C0BE13B35E0CC5F6BF
115 | 0102000020E61000000200000021938C9C853A48C0BE13B35E0CC5F6BF8524B37A873A48C0B62B9908C0C4F6BF
116 | 0102000020E6100000020000008524B37A873A48C0B62B9908C0C4F6BFE3F2695F8A3A48C0DAB4ADC156C4F6BF
117 | 0102000020E610000002000000E3F2695F8A3A48C0DAB4ADC156C4F6BFACA525FB8C3A48C008B53A94FCC3F6BF
118 |
(118 rows)
5.3.3. Exercise 4: Route geometry directionality¶
Visually, with the route displayed with arrows, it can be found that there are arrows that do not match the directionality of the route.
To have correct directionality, the ending point of a geometry must match the starting point of the next geometry
Inspecting the detail of the results of Exercise 2: Route geometry (human readable)
Rows 59 to 61 do not match that criteria
WITH
results AS (
SELECT seq, edge AS id, node, cost AS seconds
FROM pgr_dijkstra('SELECT * FROM vehicle_net', 5661895682, 10982869752)
),
compare AS (
SELECT seq, id, lead(seq) over(ORDER BY seq) AS next_seq,
ST_AsText(ST_endPoint(geom)) AS id_end,
ST_AsText(ST_startPoint(lead(geom) over(ORDER BY seq))) AS next_id_start
FROM results LEFT JOIN vehicle_net USING (id)
ORDER BY seq)
SELECT * FROM compare WHERE id_end != next_id_start;
seq | id | next_seq | id_end | next_id_start
-----+-------+----------+-------------------------------+-------------------------------
59 | 10707 | 60 | POINT(-48.4742412 -1.4409789) | POINT(-48.4745617 -1.4398022)
60 | 10708 | 61 | POINT(-48.4742412 -1.4409789) | POINT(-48.4745617 -1.4398022)
(2 rows)
Problem
Route from the Estação das Docas to Hangar Convention Center
Fix the directionality of the geometries of the previous exercise
geomin human readable form named asroute_readablegeomin binary format with the nameroute_geomBoth columns must have the geometry fixed for directionality.
Solution
To get the correct direction some geometries need to be reversed:
Reversing a geometry will depend on the
nodecolumn of the query to Dijkstra (line 2)A conditional
CASEstatement that returns the geometry in human readable form:Of the geometry when
nodeis thesourcecolumn. (line 11)Of the reversed geometry when
nodeis not thesourcecolumn. (line 12)
A conditional
CASEstatement that returns:The geometry when
nodeis thesourcecolumn. (line 17)The reversed geometry when
nodeis not thesourcecolumn. (line 16)
1WITH results AS (
2 SELECT seq, edge AS id, node, cost AS seconds
3 FROM pgr_dijkstra('SELECT * FROM vehicle_net', 5661895682, 10982869752)
4 )
5SELECT
6 -- from previous excercise
7 seq,
8 -- id, seconds, name, length,
9
10 -- fixing the directionality
11 CASE
12 WHEN node = source THEN ST_AsText(geom)
13 ELSE ST_AsText(ST_Reverse(geom))
14 END AS route_readable,
15
16 CASE
17 WHEN node = source THEN geom
18 ELSE ST_Reverse(geom)
19 END AS route_geom
20
21FROM results
22LEFT JOIN vehicle_net USING (id)
23ORDER BY seq;
seq | route_readable | route_geom
-----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | LINESTRING(-48.5002935 -1.4491382,-48.5002057 -1.4490799,-48.5001265 -1.4490202,-48.4998269 -1.4486681,-48.4997338 -1.4485607,-48.4996364 -1.4484672,-48.4995559 -1.4484022,-48.4994587 -1.4483512,-48.4993561 -1.4482882,-48.4993017 -1.4482467,-48.4991133 -1.4480664,-48.4990966 -1.4480348,-48.4990878 -1.4479913,-48.4991428 -1.44774) | 0102000020E61000000E00000060730E9E094048C01F268689AB2FF7BFEB6289BD064048C0BCA7BC676E2FF7BF75AE2825044048C0E2E423CE2F2FF7BF9E94EE53FA3F48C0A422049ABE2DF7BF58DAF346F73F48C079550CFC4D2DF7BF12F2E615F43F48C0524255F1EB2CF7BF0D839E72F13F48C06F4507C9A72CF7BFF1193F43EE3F48C02BF5D14E722CF7BF704793E6EA3F48C05EEC623F302CF7BF292B3C1EE93F48C087A350BB042CF7BFD33FD2F1E23F48C0B8FB66AC472BF7BFBC45BB65E23F48C0B784D789262BF7BF926AE91BE23F48C0C847E6ECF82AF7BF560449E9E33F48C0D2C6116BF129F7BF
2 | LINESTRING(-48.4991428 -1.44774,-48.4990724 -1.4477339,-48.4989705 -1.4477379) | 0102000020E610000003000000560449E9E33F48C0D2C6116BF129F7BF0A2BBA9AE13F48C0F3F79C05EB29F7BF9A95ED43DE3F48C020E05A37EF29F7BF
3 | LINESTRING(-48.4989705 -1.4477379,-48.4989886 -1.4477975,-48.4989873 -1.4478592,-48.4989685 -1.4479142,-48.498935 -1.4479638) | 0102000020E6100000050000009A95ED43DE3F48C020E05A37EF29F7BFD509C3DBDE3F48C060B01BB62D2AF7BF474FDBD0DE3F48C0506793686E2AF7BFF99D2633DE3F48C0C29F8614A82AF7BF36EA211ADD3F48C08FABEC16DC2AF7BF
4 | LINESTRING(-48.498935 -1.4479638,-48.4987405 -1.4479313) | 0102000020E61000000200000036EA211ADD3F48C08FABEC16DC2AF7BF68588CBAD63F48C01EADC502BA2AF7BF
5 | LINESTRING(-48.4987405 -1.4479313,-48.4985465 -1.4482172) | 0102000020E61000000200000068588CBAD63F48C01EADC502BA2AF7BF8484285FD03F48C0371378CCE52BF7BF
6 | LINESTRING(-48.4985465 -1.4482172,-48.4984527 -1.4483438) | 0102000020E6100000020000008484285FD03F48C0371378CCE52BF7BF2C8D4E4CCD3F48C070D4658C6A2CF7BF
7 | LINESTRING(-48.4984527 -1.4483438,-48.4983947 -1.4484296) | 0102000020E6100000020000002C8D4E4CCD3F48C070D4658C6A2CF7BFF77FC465CB3F48C00DEF2884C42CF7BF
8 | LINESTRING(-48.4983947 -1.4484296,-48.4982262 -1.4486989) | 0102000020E610000002000000F77FC465CB3F48C00DEF2884C42CF7BF538149E0C53F48C0CF04D4E5DE2DF7BF
9 | LINESTRING(-48.4982262 -1.4486989,-48.4981152 -1.4486197) | 0102000020E610000002000000538149E0C53F48C0CF04D4E5DE2DF7BFFCD1263DC23F48C01876BDD98B2DF7BF
10 | LINESTRING(-48.4981152 -1.4486197,-48.4973973 -1.4481404) | 0102000020E610000002000000FCD1263DC23F48C01876BDD98B2DF7BF324FF8B6AA3F48C00143A044952BF7BF
11 | LINESTRING(-48.4973973 -1.4481404,-48.4962964 -1.4474314,-48.4962011 -1.4473699) | 0102000020E610000003000000324FF8B6AA3F48C00143A044952BF7BFAF8FF3A3863F48C08341E3D3AD28F7BF9F5E8484833F48C0C86F1B576D28F7BF
12 | LINESTRING(-48.4962011 -1.4473699,-48.4961697 -1.4473505) | 0102000020E6100000020000009F5E8484833F48C0C86F1B576D28F7BF11621D7D823F48C0859675FF5828F7BF
13 | LINESTRING(-48.4961697 -1.4473505,-48.4956471 -1.4470076) | 0102000020E61000000200000011621D7D823F48C0859675FF5828F7BFBA6D3A5D713F48C0E403F170F126F7BF
14 | LINESTRING(-48.4956471 -1.4470076,-48.4953584 -1.4474603) | 0102000020E610000002000000BA6D3A5D713F48C0E403F170F126F7BF42E66FE7673F48C03222AC21CC28F7BF
15 | LINESTRING(-48.4953584 -1.4474603,-48.494943 -1.4481307) | 0102000020E61000000200000042E66FE7673F48C03222AC21CC28F7BF2B31CF4A5A3F48C06056CD188B2BF7BF
16 | LINESTRING(-48.494943 -1.4481307,-48.4946591 -1.448529) | 0102000020E6100000020000002B31CF4A5A3F48C06056CD188B2BF7BF9B9548FD503F48C0DDEBA4BE2C2DF7BF
17 | LINESTRING(-48.4946591 -1.448529,-48.4942646 -1.4490737) | 0102000020E6100000020000009B9548FD503F48C0DDEBA4BE2C2DF7BF0749FA0F443F48C042E66FE7672FF7BF
18 | LINESTRING(-48.4942646 -1.4490737,-48.4939959 -1.4494742) | 0102000020E6100000020000000749FA0F443F48C042E66FE7672FF7BFD66DF5413B3F48C00C55D6DB0B31F7BF
19 | LINESTRING(-48.4939959 -1.4494742,-48.493915 -1.4495993) | 0102000020E610000002000000D66DF5413B3F48C00C55D6DB0B31F7BF7E00529B383F48C034DF1C098F31F7BF
20 | LINESTRING(-48.493915 -1.4495993,-48.4938034 -1.4497985) | 0102000020E6100000020000007E00529B383F48C034DF1C098F31F7BFA9D326F3343F48C040A374E95F32F7BF
21 | LINESTRING(-48.4938034 -1.4497985,-48.4933626 -1.4494717) | 0102000020E610000002000000A9D326F3343F48C040A374E95F32F7BFE6087481263F48C0EFA3BF3C0931F7BF
22 | LINESTRING(-48.4933626 -1.4494717,-48.4921294 -1.4485577) | 0102000020E610000002000000E6087481263F48C0EFA3BF3C0931F7BF37339F18FE3E48C057E7BDD64A2DF7BF
23 | LINESTRING(-48.4921294 -1.4485577,-48.4912453 -1.4479154) | 0102000020E61000000200000037339F18FE3E48C057E7BDD64A2DF7BF28E84020E13E48C003FFA556A92AF7BF
24 | LINESTRING(-48.4912453 -1.4479154,-48.4903663 -1.4472594) | 0102000020E61000000200000028E84020E13E48C003FFA556A92AF7BFBFC7AA52C43E48C0E041FD78F927F7BF
25 | LINESTRING(-48.4903663 -1.4472594,-48.4887232 -1.4460579) | 0102000020E610000002000000BFC7AA52C43E48C0E041FD78F927F7BFF365587B8E3E48C082F5C99B0D23F7BF
26 | LINESTRING(-48.4887232 -1.4460579,-48.4886407 -1.4459984) | 0102000020E610000002000000F365587B8E3E48C082F5C99B0D23F7BF4DFF48C78B3E48C0DD17E137CF22F7BF
27 | LINESTRING(-48.4886407 -1.4459984,-48.4885213 -1.4459093) | 0102000020E6100000020000004DFF48C78B3E48C0DD17E137CF22F7BF2073AFDD873E48C04FB747CA7122F7BF
28 | LINESTRING(-48.4885213 -1.4459093,-48.4885012 -1.4458934) | 0102000020E6100000020000002073AFDD873E48C04FB747CA7122F7BF44071335873E48C03409281E6122F7BF
29 | LINESTRING(-48.4885012 -1.4458934,-48.4884706 -1.4458686) | 0102000020E61000000200000044071335873E48C03409281E6122F7BF5C076234863E48C04D03F51C4722F7BF
30 | LINESTRING(-48.4884706 -1.4458686,-48.4883886 -1.4458059) | 0102000020E6100000020000005C076234863E48C04D03F51C4722F7BF9F5E8484833E48C051D20D5E0522F7BF
31 | LINESTRING(-48.4883886 -1.4458059,-48.4883747 -1.4457972) | 0102000020E6100000020000009F5E8484833E48C051D20D5E0522F7BFCF58EA0F833E48C0BA5FAA3EFC21F7BF
32 | LINESTRING(-48.4883747 -1.4457972,-48.488312 -1.4457486) | 0102000020E610000002000000CF58EA0F833E48C0BA5FAA3EFC21F7BF471FF301813E48C0F8CDB348C921F7BF
33 | LINESTRING(-48.488312 -1.4457486,-48.4882586 -1.445713) | 0102000020E610000002000000471FF301813E48C0F8CDB348C921F7BFD17EFF417F3E48C0CA6E66F4A321F7BF
34 | LINESTRING(-48.4882586 -1.445713,-48.4881629 -1.4456421) | 0102000020E610000002000000D17EFF417F3E48C0CA6E66F4A321F7BF6D4F351F7C3E48C03D88539C5921F7BF
35 | LINESTRING(-48.4881629 -1.4456421,-48.4874804 -1.4451359) | 0102000020E6100000020000006D4F351F7C3E48C03D88539C5921F7BF73B8FBC1653E48C08E684CD2461FF7BF
36 | LINESTRING(-48.4874804 -1.4451359,-48.4870783 -1.4448293) | 0102000020E61000000200000073B8FBC1653E48C08E684CD2461FF7BFAF8BEC94583E48C057D7FC53051EF7BF
37 | LINESTRING(-48.4870783 -1.4448293,-48.486549 -1.4444509) | 0102000020E610000002000000AF8BEC94583E48C057D7FC53051EF7BF6473D53C473E48C022D8028C781CF7BF
38 | LINESTRING(-48.486549 -1.4444509,-48.4860067 -1.4441552) | 0102000020E6100000020000006473D53C473E48C022D8028C781CF7BF8511B177353E48C0CC92A57B421BF7BF
39 | LINESTRING(-48.4860067 -1.4441552,-48.4856347 -1.4439806) | 0102000020E6100000020000008511B177353E48C0CC92A57B421BF7BFC1262147293E48C071EFD0668B1AF7BF
40 | LINESTRING(-48.4856347 -1.4439806,-48.4854193 -1.4438832) | 0102000020E610000002000000C1262147293E48C071EFD0668B1AF7BF712C3938223E48C0B7E63345251AF7BF
41 | LINESTRING(-48.4854193 -1.4438832,-48.485021 -1.4437031,-48.4847526 -1.443592) | 0102000020E610000003000000712C3938223E48C0B7E63345251AF7BFC66F0A2B153E48C01E24FA6B6819F7BF53D3895F0C3E48C09546CCECF318F7BF
42 | LINESTRING(-48.4847526 -1.443592,-48.484519 -1.4435058) | 0102000020E61000000200000053D3895F0C3E48C09546CCECF318F7BF33A5F5B7043E48C08E61A9899918F7BF
43 | LINESTRING(-48.484519 -1.4435058,-48.4844675 -1.4434874) | 0102000020E61000000200000033A5F5B7043E48C08E61A9899918F7BFC93CF207033E48C05602733E8618F7BF
44 | LINESTRING(-48.4844675 -1.4434874,-48.4841737 -1.4433782) | 0102000020E610000002000000C93CF207033E48C05602733E8618F7BFAA8A5F67F93D48C049264CBD1318F7BF
45 | LINESTRING(-48.4841737 -1.4433782,-48.4840192 -1.4433237,-48.4836722 -1.4432064) | 0102000020E610000003000000AA8A5F67F93D48C049264CBD1318F7BF6B515557F43D48C0DEAA9097DA17F7BF007E7CF8E83D48C0DB0B16985F17F7BF
46 | LINESTRING(-48.4836722 -1.4432064,-48.4829766 -1.4429586,-48.4828336 -1.4429203) | 0102000020E610000003000000007E7CF8E83D48C0DB0B16985F17F7BFDCDD5E2DD23D48C00DB6C7C15B16F7BF79B4CC7DCD3D48C08CC0B3983316F7BF
47 | LINESTRING(-48.4828336 -1.4429203,-48.4813048 -1.4424536) | 0102000020E61000000200000079B4CC7DCD3D48C08CC0B3983316F7BF33B44B659B3D48C09EF5DF394A14F7BF
48 | LINESTRING(-48.4813048 -1.4424536,-48.4805616 -1.4422223) | 0102000020E61000000200000033B44B659B3D48C09EF5DF394A14F7BF52DBE10A833D48C08CFDC0B05713F7BF
49 | LINESTRING(-48.4805616 -1.4422223,-48.4785543 -1.4415475) | 0102000020E61000000200000052DBE10A833D48C08CFDC0B05713F7BF1AEE6D44413D48C0C616821C9410F7BF
50 | LINESTRING(-48.4785543 -1.4415475,-48.4772324 -1.4414051) | 0102000020E6100000020000001AEE6D44413D48C0C616821C9410F7BFB94B87F3153D48C00D9A4CCBFE0FF7BF
51 | LINESTRING(-48.4772324 -1.4414051,-48.4768596 -1.441359) | 0102000020E610000002000000B94B87F3153D48C00D9A4CCBFE0FF7BF4E6441BC093D48C067B96C74CE0FF7BF
52 | LINESTRING(-48.4768596 -1.441359,-48.4767785 -1.441349) | 0102000020E6100000020000004E6441BC093D48C067B96C74CE0FF7BFCD77F013073D48C0F5F411F8C30FF7BF
53 | LINESTRING(-48.4767785 -1.441349,-48.4761448 -1.4413018) | 0102000020E610000002000000CD77F013073D48C0F5F411F8C30FF7BF8A141450F23C48C0A9A7EA79920FF7BF
54 | LINESTRING(-48.4761448 -1.4413018,-48.4753701 -1.4412082) | 0102000020E6100000020000008A141450F23C48C0A9A7EA79920FF7BF857F6CEDD83C48C0E7A15B54300FF7BF
55 | LINESTRING(-48.4753701 -1.4412082,-48.475342 -1.4412022) | 0102000020E610000002000000857F6CEDD83C48C0E7A15B54300FF7BF2635B401D83C48C0A3C5BE092A0FF7BF
56 | LINESTRING(-48.475342 -1.4412022,-48.4751295 -1.4411624) | 0102000020E6100000020000002635B401D83C48C0A3C5BE092A0FF7BFB3EE1F0BD13C48C01299034E000FF7BF
57 | LINESTRING(-48.4751295 -1.4411624,-48.4750651 -1.4411511) | 0102000020E610000002000000B3EE1F0BD13C48C01299034E000FF7BF49FCE5EECE3C48C0C482B174F40EF7BF
58 | LINESTRING(-48.4750651 -1.4411511,-48.4746033 -1.4410539) | 0102000020E61000000200000049FCE5EECE3C48C0C482B174F40EF7BF6E090ACDBF3C48C0405FC4888E0EF7BF
59 | LINESTRING(-48.4746033 -1.4410539,-48.4742412 -1.4409789) | 0102000020E6100000020000006E090ACDBF3C48C0405FC4888E0EF7BF393586EFB33C48C0EB9D1BE43F0EF7BF
60 | LINESTRING(-48.4742412 -1.4409789,-48.4745617 -1.4398022) | 0102000020E610000002000000393586EFB33C48C0EB9D1BE43F0EF7BF93B71270BE3C48C074571B086E09F7BF
61 | LINESTRING(-48.4745617 -1.4398022,-48.47413 -1.4396471,-48.4739923 -1.43959) | 0102000020E61000000300000093B71270BE3C48C074571B086E09F7BFB806B64AB03C48C0F77FC465CB08F7BF258799C7AB3C48C0D4601A868F08F7BF
62 | LINESTRING(-48.4739923 -1.43959,-48.4738232 -1.4394327) | 0102000020E610000002000000258799C7AB3C48C0D4601A868F08F7BF040B163DA63C48C00BB03495EA07F7BF
63 | LINESTRING(-48.4738232 -1.4394327,-48.4734106 -1.4395805,-48.4729262 -1.439693) | 0102000020E610000003000000040B163DA63C48C00BB03495EA07F7BF354AF2B7983C48C06859F78F8508F7BFF63581D8883C48C0677BF486FB08F7BF
64 | LINESTRING(-48.4729262 -1.439693,-48.4729169 -1.4396395,-48.4729068 -1.4395979,-48.4728891 -1.4395373) | 0102000020E610000004000000F63581D8883C48C0677BF486FB08F7BFE49C7D8A883C48C0077AA86DC308F7BF2B07C435883C48C0953EBECE9708F7BF449149A1873C48C04AF48D435808F7BF
65 | LINESTRING(-48.4728891 -1.4395373,-48.4721387 -1.4387004) | 0102000020E610000002000000449149A1873C48C04AF48D435808F7BF86D6790A6F3C48C0B8DFEBB5EA04F7BF
66 | LINESTRING(-48.4721387 -1.4387004,-48.4712263 -1.4375973) | 0102000020E61000000200000086D6790A6F3C48C0B8DFEBB5EA04F7BFEEC1B524513C48C01F16C5066600F7BF
67 | LINESTRING(-48.4712263 -1.4375973,-48.4706778 -1.4369384) | 0102000020E610000002000000EEC1B524513C48C01F16C5066600F7BF03FA8E2B3F3C48C075DDA51EB3FDF6BF
68 | LINESTRING(-48.4706778 -1.4369384,-48.4702411 -1.4364265) | 0102000020E61000000200000003FA8E2B3F3C48C075DDA51EB3FDF6BF17DE40DC303C48C052B9895A9AFBF6BF
69 | LINESTRING(-48.4702411 -1.4364265,-48.4702312 -1.4364149,-48.4697076 -1.4357476) | 0102000020E61000000300000017DE40DC303C48C052B9895A9AFBF6BF88C73489303C48C034CBAF308EFBF6BF6157EE601F3C48C0C4F7B479D2F8F6BF
70 | LINESTRING(-48.4697076 -1.4357476,-48.4692971 -1.4352697) | 0102000020E6100000020000006157EE601F3C48C0C4F7B479D2F8F6BFC74D68ED113C48C02409675CDDF6F6BF
71 | LINESTRING(-48.4692971 -1.4352697,-48.4681118 -1.4360672) | 0102000020E610000002000000C74D68ED113C48C02409675CDDF6F6BF94EF6316EB3B48C090BBAD9921FAF6BF
72 | LINESTRING(-48.4681118 -1.4360672,-48.4671349 -1.4367245) | 0102000020E61000000200000094EF6316EB3B48C090BBAD9921FAF6BFFD288F13CB3B48C08ECA4DD4D2FCF6BF
73 | LINESTRING(-48.4671349 -1.4367245,-48.4668045 -1.4369468) | 0102000020E610000002000000FD288F13CB3B48C08ECA4DD4D2FCF6BF1590F63FC03B48C03B7881EDBBFDF6BF
74 | LINESTRING(-48.4668045 -1.4369468,-48.4667203 -1.4370034) | 0102000020E6100000020000001590F63FC03B48C03B7881EDBBFDF6BF8D70A47DBD3B48C058DAF346F7FDF6BF
75 | LINESTRING(-48.4667203 -1.4370034,-48.4666385 -1.4370593) | 0102000020E6100000020000008D70A47DBD3B48C058DAF346F7FDF6BFFA4674CFBA3B48C03A9A7EE431FEF6BF
76 | LINESTRING(-48.4666385 -1.4370593,-48.4665354 -1.4371285) | 0102000020E610000002000000FA4674CFBA3B48C03A9A7EE431FEF6BF90B6966EB73B48C080643A747AFEF6BF
77 | LINESTRING(-48.4665354 -1.4371285,-48.4664868 -1.4370544) | 0102000020E61000000200000090B6966EB73B48C080643A747AFEF6BF0202E7D6B53B48C09C2A29C12CFEF6BF
78 | LINESTRING(-48.4664868 -1.4370544,-48.465844 -1.4360723) | 0102000020E6100000020000000202E7D6B53B48C09C2A29C12CFEF6BFD784B4C6A03B48C06310B3F226FAF6BF
79 | LINESTRING(-48.465844 -1.4360723,-48.4657246 -1.4358894) | 0102000020E610000002000000D784B4C6A03B48C06310B3F226FAF6BFAAF81ADD9C3B48C0DDC4DA2967F9F6BF
80 | LINESTRING(-48.4657246 -1.4358894,-48.4653756 -1.4353756) | 0102000020E610000002000000AAF81ADD9C3B48C0DDC4DA2967F9F6BF9E2D7B6D913B48C03E9FB7674CF7F6BF
81 | LINESTRING(-48.4653756 -1.4353756,-48.4649634 -1.4347336) | 0102000020E6100000020000009E2D7B6D913B48C03E9FB7674CF7F6BF226BB2EB833B48C0BA8E2738ABF4F6BF
82 | LINESTRING(-48.4649634 -1.4347336,-48.4649079 -1.4346515) | 0102000020E610000002000000226BB2EB833B48C0BA8E2738ABF4F6BF7613211A823B48C07B849A2155F4F6BF
83 | LINESTRING(-48.4649079 -1.4346515,-48.4648532 -1.4345682) | 0102000020E6100000020000007613211A823B48C07B849A2155F4F6BF71B8454F803B48C0FB1AEEC8FDF3F6BF
84 | LINESTRING(-48.4648532 -1.4345682,-48.4642006 -1.4336217) | 0102000020E61000000200000071B8454F803B48C0FB1AEEC8FDF3F6BF4CE4DDEC6A3B48C0F15FC54E1DF0F6BF
85 | LINESTRING(-48.4642006 -1.4336217,-48.464072 -1.4334149) | 0102000020E6100000020000004CE4DDEC6A3B48C0F15FC54E1DF0F6BFA27E17B6663B48C0F595517644EFF6BF
86 | LINESTRING(-48.464072 -1.4334149,-48.4639398 -1.4332242) | 0102000020E610000002000000A27E17B6663B48C0F595517644EFF6BF09281E61623B48C0495FAD7F7CEEF6BF
87 | LINESTRING(-48.4639398 -1.4332242,-48.4632933 -1.4321911) | 0102000020E61000000200000009281E61623B48C0495FAD7F7CEEF6BF5BFAE1314D3B48C0CDF4013741EAF6BF
88 | LINESTRING(-48.4632933 -1.4321911,-48.4632299 -1.4320886) | 0102000020E6100000020000005BFAE1314D3B48C0CDF4013741EAF6BFC2830B1E4B3B48C03F975FBCD5E9F6BF
89 | LINESTRING(-48.4632299 -1.4320886,-48.4632037 -1.4320479) | 0102000020E610000002000000C2830B1E4B3B48C03F975FBCD5E9F6BF6F7143424A3B48C03DE30C0FABE9F6BF
90 | LINESTRING(-48.4632037 -1.4320479,-48.4628241 -1.4315024,-48.4627215 -1.4313567) | 0102000020E6100000030000006F7143424A3B48C03DE30C0FABE9F6BF7BA6F2D13D3B48C00354820F6FE7F6BFFAD346753A3B48C057917648D6E6F6BF
91 | LINESTRING(-48.4627215 -1.4313567,-48.4624439 -1.4309243) | 0102000020E610000002000000FAD346753A3B48C057917648D6E6F6BF0A5E995C313B48C0BCD3F8E010E5F6BF
92 | LINESTRING(-48.4624439 -1.4309243,-48.462054 -1.430331) | 0102000020E6100000020000000A5E995C313B48C0BCD3F8E010E5F6BF357EE195243B48C0954737C2A2E2F6BF
93 | LINESTRING(-48.462054 -1.430331,-48.4618505 -1.4300156) | 0102000020E610000002000000357EE195243B48C0954737C2A2E2F6BF1492CCEA1D3B48C02C51AC0958E1F6BF
94 | LINESTRING(-48.4618505 -1.4300156,-48.4617568 -1.4298731) | 0102000020E6100000020000001492CCEA1D3B48C02C51AC0958E1F6BF525AC9D81A3B48C0D8E19E9DC2E0F6BF
95 | LINESTRING(-48.4617568 -1.4298731,-48.4616814 -1.4297624) | 0102000020E610000002000000525AC9D81A3B48C0D8E19E9DC2E0F6BFF4154960183B48C0BACED0894EE0F6BF
96 | LINESTRING(-48.4616814 -1.4297624,-48.4616238 -1.4296746) | 0102000020E610000002000000F4154960183B48C0BACED0894EE0F6BF12071A7D163B48C007C02E79F2DFF6BF
97 | LINESTRING(-48.4616238 -1.4296746,-48.4615685 -1.4295928) | 0102000020E61000000200000012071A7D163B48C007C02E79F2DFF6BF902E36AD143B48C0988D29B39CDFF6BF
98 | LINESTRING(-48.4615685 -1.4295928,-48.4614857 -1.4294702) | 0102000020E610000002000000902E36AD143B48C0988D29B39CDFF6BF2C89A2F6113B48C08CB4F9241CDFF6BF
99 | LINESTRING(-48.4614857 -1.4294702,-48.4612304 -1.4291091) | 0102000020E6100000020000002C89A2F6113B48C08CB4F9241CDFF6BFE3F50599093B48C0E9A7EE80A1DDF6BF
100 | LINESTRING(-48.4612304 -1.4291091,-48.4607873 -1.4284363) | 0102000020E610000002000000E3F50599093B48C0E9A7EE80A1DDF6BFC1F40714FB3A48C03AB58E05E0DAF6BF
101 | LINESTRING(-48.4607873 -1.4284363,-48.4604752 -1.4279524) | 0102000020E610000002000000C1F40714FB3A48C03AB58E05E0DAF6BF3D4FF2D9F03A48C055EAA39DE4D8F6BF
102 | LINESTRING(-48.4604752 -1.4279524,-48.4603566 -1.4277745) | 0102000020E6100000020000003D4FF2D9F03A48C055EAA39DE4D8F6BFB6BF0EF7EC3A48C00801F9122AD8F6BF
103 | LINESTRING(-48.4603566 -1.4277745,-48.4599639 -1.427199) | 0102000020E610000002000000B6BF0EF7EC3A48C00801F9122AD8F6BF99EBD918E03A48C079245E9ECED5F6BF
104 | LINESTRING(-48.4599639 -1.427199,-48.4598934 -1.4270889,-48.4596442 -1.4266998) | 0102000020E61000000300000099EBD918E03A48C079245E9ECED5F6BFB85274C9DD3A48C0FBC09F2B5BD5F6BFE665039FD53A48C0195B632BC3D3F6BF
105 | LINESTRING(-48.4596442 -1.4266998,-48.4595784 -1.4265913) | 0102000020E610000002000000E665039FD53A48C0195B632BC3D3F6BF58F90A77D33A48C04721246651D3F6BF
106 | LINESTRING(-48.4595784 -1.4265913,-48.4591475 -1.4259608) | 0102000020E61000000200000058F90A77D33A48C04721246651D3F6BF24456458C53A48C0460C9645BCD0F6BF
107 | LINESTRING(-48.4591475 -1.4259608,-48.4588567 -1.4255144) | 0102000020E61000000200000024456458C53A48C0460C9645BCD0F6BF7606FCD0BB3A48C00CA2FF2FE8CEF6BF
108 | LINESTRING(-48.4588567 -1.4255144,-48.4587574 -1.4253702) | 0102000020E6100000020000007606FCD0BB3A48C00CA2FF2FE8CEF6BF25E6FE8FB83A48C071169BFB50CEF6BF
109 | LINESTRING(-48.4587574 -1.4253702,-48.458296 -1.4247004) | 0102000020E61000000200000025E6FE8FB83A48C071169BFB50CEF6BF9DF17D71A93A48C0E59189A592CBF6BF
110 | LINESTRING(-48.458296 -1.4247004,-48.4578312 -1.4239864) | 0102000020E6100000020000009DF17D71A93A48C0E59189A592CBF6BF528B77369A3A48C02E2E9FF6A5C8F6BF
111 | LINESTRING(-48.4578312 -1.4239864,-48.4576142 -1.4236202) | 0102000020E610000002000000528B77369A3A48C02E2E9FF6A5C8F6BFB597231A933A48C0B7CC8EF925C7F6BF
112 | LINESTRING(-48.4576142 -1.4236202,-48.4574931 -1.4234158) | 0102000020E610000002000000B597231A933A48C0B7CC8EF925C7F6BFA55247228F3A48C03CC159A54FC6F6BF
113 | LINESTRING(-48.4574931 -1.4234158,-48.4574417 -1.4233478,-48.4573359 -1.4232316) | 0102000020E610000003000000A55247228F3A48C03CC159A54FC6F6BFCFA91A738D3A48C03756BD5708C6F6BFB3E496FB893A48C0DB238A7F8EC5F6BF
114 | LINESTRING(-48.4573359 -1.4232316,-48.4572025 -1.4231075) | 0102000020E610000002000000B3E496FB893A48C0DB238A7F8EC5F6BF21938C9C853A48C0BE13B35E0CC5F6BF
115 | LINESTRING(-48.4572025 -1.4231075,-48.4572595 -1.4230347) | 0102000020E61000000200000021938C9C853A48C0BE13B35E0CC5F6BF8524B37A873A48C0B62B9908C0C4F6BF
116 | LINESTRING(-48.4572595 -1.4230347,-48.4573478 -1.4229343) | 0102000020E6100000020000008524B37A873A48C0B62B9908C0C4F6BFE3F2695F8A3A48C0DAB4ADC156C4F6BF
117 | LINESTRING(-48.4573478 -1.4229343,-48.4574274 -1.4228483) | 0102000020E610000002000000E3F2695F8A3A48C0DAB4ADC156C4F6BFACA525FB8C3A48C008B53A94FCC3F6BF
118 | |
(118 rows)
Inspecting some the problematic rows, the directionality has been fixed.
WITH
results AS (
SELECT seq, edge AS id, node, cost AS seconds
FROM pgr_dijkstra('SELECT * FROM vehicle_net', 5661895682, 10982869752)
),
fix AS (
SELECT seq, id,
CASE
WHEN node = source THEN geom
ELSE ST_Reverse(geom)
END AS geom
FROM results LEFT JOIN vehicle_net USING (id)
),
compare AS (
SELECT seq, id, lead(geom) over(ORDER BY seq) AS next_id,
ST_AsText(ST_endPoint(geom)) AS id_end, ST_AsText(ST_startPoint(lead(geom) over(ORDER BY seq))) AS next_id_start
FROM fix
ORDER BY seq)
SELECT * FROM compare WHERE id_end != next_id_start;
seq | id | next_id | id_end | next_id_start
-----+----+---------+--------+---------------
(0 rows)
5.3.4. Exercise 5: Using the geometry¶
There are many geometry functions in PostGIS, the workshop already covered some of them like
ST_AsText, ST_Reverse, ST_EndPoint, etc.
This exercise will make use an additional function ST_Azimuth.
Problem
Modify the query from the previous exercise
Additionally obtain the azimuth of the correct geometry.
Because
vehicle_netand the other 2 views are sub graphs ofways, do theJOINwithways.
Solution
Move the query that gets the additional information into the
WITHstatement.Name it
additional. (line 6)
The
waystable geometry name isthe_geom.Final
SELECTstatements gets:Calculates the azimuth of
route_geom. (line 27)
WITH
results AS (
SELECT seq, edge AS id, node, cost AS seconds
FROM pgr_dijkstra('SELECT * FROM vehicle_net', 5661895682, 10982869752)
),
additional AS (
SELECT
seq, id, seconds, name, length,
CASE
WHEN node = source THEN ST_AsText(the_geom)
ELSE ST_AsText(ST_Reverse(the_geom))
END AS route_readable,
CASE
WHEN node = source THEN the_geom
ELSE ST_Reverse(the_geom)
END AS route_geom
FROM results
LEFT JOIN ways ON (gid = id)
)
SELECT
seq,
-- from previous excercise
-- id, seconds, name, length, route_readable, route_geom
degrees(ST_azimuth(ST_StartPoint(route_geom), ST_EndPoint(route_geom))) AS azimuth
FROM additional
ORDER BY seq;
seq | azimuth
-----+--------------------
1 | 219.45392598775587
2 | 269.3017110872027
3 | 351.0690562994052
4 | 260.5137946009003
5 | 325.8407955313577
6 | 323.46465121180717
7 | 325.94167329419747
8 | 327.9659392709573
9 | 234.49162950885935
10 | 236.27127260223202
11 | 237.21348134952265
12 | 238.29080140921084
13 | 236.72942283176377
14 | 327.47315373986453
15 | 328.21639983644445
16 | 324.51951447777776
17 | 324.0859941560983
18 | 326.14189081689125
19 | 327.10995187319173
20 | 330.740664532212
21 | 233.4475272478899
22 | 233.455592153592
23 | 234.0015375921268
24 | 233.26592360493802
25 | 233.8242453618781
26 | 234.2004004706289
27 | 233.26855342364627
28 | 231.65442504665162
29 | 230.97670238499185
30 | 232.59723699371077
31 | 237.957580091685
32 | 232.2200341386748
33 | 236.30993247347044
34 | 233.4668494905193
35 | 233.436227152522
36 | 232.67459214750514
37 | 234.43877782175528
38 | 241.39768367103756
39 | 244.85675985462152
40 | 245.66837400921696
41 | 246.40534534002722
42 | 249.7455937774301
43 | 250.33912848979384
44 | 249.6108352198055
45 | 251.08996134666592
46 | 251.1622144385592
47 | 253.02403139830176
48 | 252.71272241137245
49 | 251.4187469227288
50 | 263.85159456504294
51 | 262.9506590629574
52 | 262.9706504674567
53 | 265.74029298048976
54 | 263.11086111578715
55 | 257.9470239903407
56 | 259.39174071309924
57 | 260.0478590727108
58 | 258.11384314041345
59 | 258.29807387765754
60 | 344.76383539869016
61 | 249.56093432884606
62 | 227.0704530856361
63 | 286.1821748643207
64 | 193.40244724077004
65 | 221.88074868994642
66 | 219.59495216935
67 | 219.77562213697098
68 | 220.46737229597954
69 | 218.16132630439412
70 | 220.66146786728171
71 | 303.9336696170884
72 | 303.93434880760964
73 | 303.9334873286965
74 | 303.9093504654853
75 | 304.347688972676
76 | 303.869236231345
77 | 213.25968478853537
78 | 213.2053821652815
79 | 213.13713494574745
80 | 214.18641043814034
81 | 212.70273800101532
82 | 214.05888497089742
83 | 213.29141142432124
84 | 214.58578109918662
85 | 211.87569854442842
86 | 214.73107172955443
87 | 212.03777538086314
88 | 211.7383071883484
89 | 212.77078720382403
90 | 214.90068030823565
91 | 212.7003878701488
92 | 213.3117870222489
93 | 212.83061062010054
94 | 213.32667421819596
95 | 214.25956573401206
96 | 213.26633467624956
97 | 214.06023173762716
98 | 214.03379511332366
99 | 215.26055405442426
100 | 213.3685391217868
101 | 212.8207199895
102 | 213.6900675260788
103 | 214.30818742969623
104 | 212.6365016201201
105 | 211.2347846885786
106 | 214.34971084910975
107 | 213.08157629426898
108 | 214.55234384167915
109 | 214.56149332417715
110 | 213.06333832606416
111 | 210.64981019719437
112 | 210.64528908202325
113 | 220.47811121925758
114 | 227.06842425841222
115 | 141.9402269597685
116 | 138.66895109835988
117 | 137.21322817662033
118 |
(118 rows)
5.4. Creating the Function¶
The following function simplifies (and sets default values) when it calls the shortest path Dijkstra function.
Warning
pgRouting uses heavely function overloading:
Avoid creating functions with a name of a pgRouting routing function
Avoid the name of a function to start with pgr_, _pgr or ST_
5.4.1. Exercise 6: Function for an application¶
Problem
Putting all together in a SQL function
function name
wrk_dijkstraShould work for any given view.
Allow a view as a parameter
A table can be used if the columns have the correct names.
sourceandtargetare in terms ofosm_id.The result should meet the requirements indicated at the beginning of the chapter
Solution
The signature of the function:
The input parameters are from line 4 to 6.
The output columns are from line 7 to 14 (not highlighted).
The function returns a set. (line 16)
-- DROP FUNCTION wrk_dijkstra(regclass, bigint, bigint);
CREATE OR REPLACE FUNCTION wrk_dijkstra(
IN edges_subset REGCLASS,
IN source BIGINT, -- in terms of osm_id
IN target BIGINT, -- in terms of osm_id
OUT seq INTEGER,
OUT id BIGINT,
OUT seconds FLOAT,
OUT name TEXT,
OUT length_m FLOAT,
OUT route_readable TEXT,
OUT route_geom geometry,
OUT azimuth FLOAT
)
RETURNS SETOF record AS
The body of the function:
Appending the view name on line 7 in the
SELECTquery topgr_dijkstra.Using the data to get the route from
sourcetotarget. (line 8)The
JOINwithwaysis necessary, as the views are subset ofways(line 25)
$BODY$
WITH
results AS (
SELECT seq, edge AS id, cost AS seconds,
node
FROM pgr_dijkstra(
'SELECT * FROM ' || edges_subset,
source, target)
),
additional AS (
SELECT
seq, id, seconds,
name, length_m,
CASE
WHEN node = source THEN ST_AsText(the_geom)
ELSE ST_AsText(ST_Reverse(the_geom))
END AS route_readable,
CASE
WHEN node = source_osm THEN the_geom
ELSE ST_Reverse(the_geom)
END AS route_geom
FROM results
LEFT JOIN ways ON (gid = id)
)
SELECT *,
degrees(ST_azimuth(ST_StartPoint(route_geom), ST_EndPoint(route_geom))) AS azimuth
FROM additional
ORDER BY seq;
$BODY$
LANGUAGE 'sql';
CREATE FUNCTION
5.4.2. Exercise 7: Using the function¶
Problem
Test the function with the three views
From the Estação das Docas to the Hangar Convention Center using the OSM identifier
Solution
Use the function on the
SELECTstatementThe first parameter changes based on the view to be tested
Names of the streets in the route
SELECT DISTINCT name
FROM wrk_dijkstra('vehicle_net', 5661895682, 10982869752);
name
-----------------------------
Travessa Diogo Móia
Avenida Presidente Vargas
Rua Diogo Móia
Travessa Vileta
Avenida Brigadeiro Protázio
Passagem Ademar de Barros
Travessa Antônio Baena
Rua Oliveira Belo
Travessa Piedade
Avenida Duque de Caxias
Rua Gaspar Viana
Avenida Doutor Freitas
Avenida Visconde de Inhaúma
Rua Aristides Lobo
(15 rows)
Total seconds spent in each street
SELECT name, sum(seconds)
FROM wrk_dijkstra('taxi_net', 5661895682, 10982869752)
GROUP BY name;
name | sum
-----------------------------+--------------------
| 23.861249584123655
Travessa Diogo Móia | 2.3210555749047206
Avenida Presidente Vargas | 8.110769367340588
Rua Diogo Móia | 132.67284886367426
Travessa Vileta | 29.28263411971839
Avenida Brigadeiro Protázio | 1.6011155886292923
Passagem Ademar de Barros | 10.685150241662154
Travessa Antônio Baena | 8.241613174115601
Rua Oliveira Belo | 7.38222867704757
Travessa Piedade | 29.36759575170905
Avenida Duque de Caxias | 121.90223102345931
Rua Gaspar Viana | 27.132921302194045
Avenida Doutor Freitas | 3.0152100378783957
Avenida Visconde de Inhaúma | 48.99066219506939
Rua Aristides Lobo | 57.68452285282492
(15 rows)
Get all the information of the route
SELECT *
FROM wrk_dijkstra('walk_net', 5661895682, 10982869752);
seq | id | seconds | name | length_m | route_readable | route_geom | azimuth
-----+----+---------+------+----------+----------------+------------+---------
(0 rows)
Try the function with a combination of the interesting places:
10982869752Hangar Convention Center11818739588Instituto Federal do Pará, Campus Belém491465035Palacete Bolonha1069202329Forte do Castelo5661895682Estação das Docas