6. Appendix: OSGeo UN Challenge Workshop Solutions¶
6.1. Solutions to Chapter 3: Good Health and Well Being¶
6.1.1. Exercise: 5 (Chapter: SDG 3)¶
Exercise 5: Counting the number of Roads and Buildings
count
-------
558
(1 row)
count
-------
378
(1 row)
count
-------
1066
(1 row)
6.1.2. Exercise: 6 (Chapter: SDG 3)¶
Exercise 6: Add a spatial column to the table
addgeometrycolumn
-------------------------------------------------------------------
buildings.buildings_ways.poly_geom SRID:4326 TYPE:POLYGON DIMS:2
(1 row)
6.1.3. Exercise: 7 (Chapter: SDG 3)¶
Exercise 7: Removing the polygons with less than 4 points
DELETE 17
6.1.4. Exercise: 8 (Chapter: SDG 3)¶
Exercise 8: Creating the polygons
UPDATE 1049
6.1.5. Exercise: 9 (Chapter: SDG 3)¶
Exercise 9: Calculating the area
ALTER TABLE
UPDATE 1049
6.1.6. Exercise: 10 (Chapter: SDG 3)¶
Exercise 10: Find the Component ID for Road vertices
UPDATE 353
6.1.7. Exercise: 11 (Chapter: SDG 3)¶
Exercise 11: Finding the components which are to be removed
component
-----------
(0 rows)
6.1.8. Exercise: 12 (Chapter: SDG 3)¶
Exercise 12: Finding the road vertices of these components
id
----
(0 rows)
6.1.9. Exercise: 13 (Chapter: SDG 3)¶
Exercise 13: Removing the unwanted edges and vertices
DELETE 0
DELETE 0
6.1.10. Exercise: 15 (Chapter: SDG 3)¶
Exercise 15: Finding the served roads using pgr_drivingDistance
gid | source | target | agg_cost | the_geom
-----+--------+--------+--------------------+--------------------------------------------------------------------------------------------
163 | 98 | 99 | 1.2794927512370855 | 0102000020E61000000200000064A82FF0813552409540EF32681B334047FF70FC7535524030EB0FDE681B3340
162 | 36 | 98 | 1.3290329100878826 | 0102000020E6100000020000004636A15A8E355240838D4699681B334064A82FF0813552409540EF32681B3340
61 | 158 | 36 | 1.4493409579441925 | 0102000020E61000000200000051C719798F3552409540EF32681B33404636A15A8E355240838D4699681B3340
472 | 158 | 312 | 2.2354545727659896 | 0102000020E61000000200000051C719798F3552409540EF32681B33408D83A6808F355240FDAF29464C1B3340
237 | 99 | 145 | 2.427530387055349 | 0102000020E61000000200000047FF70FC7535524030EB0FDE681B33406499D9426B3552407D40A033691B3340
470 | 312 | 311 | 2.791877105220322 | 0102000020E6100000020000008D83A6808F355240FDAF29464C1B33400A01AF858F3552400D8D2782381B3340
21 | 36 | 13 | 3.9047199275446904 | 0102000020E6100000020000004636A15A8E355240838D4699681B3340E63B9DD0903552402B22799DC31B3340
180 | 145 | 109 | 3.970347591636453 | 0102000020E6100000020000006499D9426B3552407D40A033691B3340A7EA1ED95C3552408E07B6A5691B3340
259 | 157 | 158 | 4.022391725856522 | 0102000020E610000002000000F716201B923552402B6A300DC31B334051C719798F3552409540EF32681B3340
236 | 293 | 145 | 4.742001982159765 | 0102000020E6100000020000005E21BDF26A3552405F3AF768BB1B33406499D9426B3552407D40A033691B3340
(10 rows)
6.1.11. Exercise: 16 (Chapter: SDG 3)¶
Exercise 16: Generalising the served roads
gid | source | target | agg_cost | the_geom
-----+--------+--------+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------
20 | 36 | 13 | 3.9047199275446904 | 0102000020E6100000040000005E21BDF26A3552405F3AF768BB1B3340F3CF679B76355240B3C8C04BBA1B33406AAD1EE681355240C2DF8A1FBE1B3340E63B9DD0903552402B22799DC31B3340
20 | 293 | 145 | 4.742001982159765 | 0102000020E6100000040000005E21BDF26A3552405F3AF768BB1B3340F3CF679B76355240B3C8C04BBA1B33406AAD1EE681355240C2DF8A1FBE1B3340E63B9DD0903552402B22799DC31B3340
21 | 36 | 98 | 1.3290329100878826 | 0102000020E6100000020000004636A15A8E355240838D4699681B3340E63B9DD0903552402B22799DC31B3340
21 | 36 | 13 | 3.9047199275446904 | 0102000020E6100000020000004636A15A8E355240838D4699681B3340E63B9DD0903552402B22799DC31B3340
40 | 38 | 24 | 9.244894208545695 | 0102000020E610000002000000139A249694355240BB7B2535591C3340AE896077953552404D6C9967801C3340
41 | 38 | 24 | 9.244894208545695 | 0102000020E610000002000000FD570C0D7A355240CE2C53DD6D1C3340AE896077953552404D6C9967801C3340
59 | 159 | 35 | 6.144599533802122 | 0102000020E610000002000000B73066A68F3552409E4B27C8BE1A334040BE840A8E3552403E6BC889C01A3340
59 | 155 | 35 | 8.764606484722782 | 0102000020E610000002000000B73066A68F3552409E4B27C8BE1A334040BE840A8E3552403E6BC889C01A3340
59 | 159 | 153 | 8.96952057477067 | 0102000020E610000002000000B73066A68F3552409E4B27C8BE1A334040BE840A8E3552403E6BC889C01A3340
60 | 159 | 35 | 6.144599533802122 | 0102000020E610000002000000E13F82878E355240E807BF1E5D1A334040BE840A8E3552403E6BC889C01A3340
(10 rows)
6.1.12. Exercise: 17 (Chapter: SDG 3)¶
Exercise 17: Estimating the population of buildings
CREATE FUNCTION
ALTER TABLE
UPDATE 1049
6.1.13. Exercise: 18 (Chapter: SDG 3)¶
Exercise 18: Finding the nearest roads to store the population
CREATE FUNCTION
ALTER TABLE
UPDATE 1049
6.1.14. Exercise: 19 (Chapter: SDG 3)¶
Exercise 19: Storing the population in the roads
ALTER TABLE
UPDATE 265
population
------------
606
(1 row)
6.1.15. Exercise: 20 (Chapter: SDG 3)¶
Exercise 20: Finding total population
sum
--------
124593
(1 row)
6.2. Solutions to Affordable and Clean Energy¶
6.2.1. Exercise: 5 (Chapter: SDG 7)¶
Exercise 5: Counting the number of Roads
count
-------
558
(1 row)
count
-------
378
(1 row)
6.2.2. Exercise: 6 (Chapter: SDG 7)¶
Exercise 6: Find the Component ID for Road vertices
ALTER TABLE
UPDATE 378
6.2.3. Exercise: 7 (Chapter: SDG 7)¶
Exercise 7: Finding the components which are to be removed
component
-----------
214
244
296
245
252
369
318
(7 rows)
6.2.4. Exercise: 8 (Chapter: SDG 7)¶
Exercise 8: Finding the road vertices of these components
id
-----
214
215
244
248
251
253
255
258
259
261
263
265
245
246
264
252
254
262
296
297
298
318
319
369
370
(25 rows)
6.2.5. Exercise: 9 (Chapter: SDG 7)¶
Exercise 9: Removing the unwanted edges and vertices
DELETE 29
DELETE 25
6.2.6. Exercise: 10 (Chapter: SDG 7)¶
Exercise 10: Find the minimum spanning tree
source | target | edge | the_geom
--------+--------+------+----------------------------------------------------------------------------------------------------------------------------
91 | 78 | 127 | 0102000020E610000002000000F48EAE2D97355240EDFFD258B11C3340CE44C7C49C3552405ACBAEC5B81C3340
90 | 91 | 149 | 0102000020E61000000200000048EF65EC95355240BC9E9E2DD61C3340F48EAE2D97355240EDFFD258B11C3340
39 | 90 | 148 | 0102000020E610000002000000DE86623C943552406A508FC8D21C334048EF65EC95355240BC9E9E2DD61C3340
77 | 91 | 150 | 0102000020E6100000020000007229F8B7953552404F907D35AC1C3340F48EAE2D97355240EDFFD258B11C3340
58 | 77 | 125 | 0102000020E610000002000000D2318C60883552404D672783A31C33407229F8B7953552404F907D35AC1C3340
51 | 58 | 96 | 0102000020E6100000020000004A9693507A35524033E25D899B1C3340D2318C60883552404D672783A31C3340
51 | 23 | 38 | 0102000020E6100000020000004A9693507A35524033E25D899B1C3340FD570C0D7A355240CE2C53DD6D1C3340
22 | 23 | 39 | 0102000020E6100000020000002DEDD45C6E35524013680B63661C3340FD570C0D7A355240CE2C53DD6D1C3340
314 | 22 | 35 | 0102000020E6100000020000001DA8F8646A3552403B504C83471C33402DEDD45C6E35524013680B63661C3340
314 | 21 | 32 | 0102000020E6100000030000001DA8F8646A3552403B504C83471C3340B97592086835524021D4FB7C4A1C334045E4AA686235524028863F1E551C3340
(10 rows)
source | target | edge | agg_cost
--------+--------+------+------------------------
77 | 91 | 150 | 0.00011868180146658374
91 | 78 | 127 | 0.00035951958222797713
90 | 91 | 149 | 0.0005671962270664609
39 | 90 | 148 | 0.0006824882042342863
24 | 77 | 126 | 0.0007877531202786062
24 | 92 | 152 | 0.0008877883640677352
58 | 77 | 125 | 0.0009437234852640964
92 | 316 | 479 | 0.001435218624463729
92 | 82 | 132 | 0.0014838157208228499
82 | 38 | 66 | 0.0015808774537568746
(10 rows)
6.2.7. Exercise: 11 (Chapter: SDG 7)¶
Exercise 11: Compute total length of material required in km
?column?
-------------------
29.89374908792656
(1 row)
6.2.8. Exercise: 12 (Chapter: SDG 7)¶
Exercise 12: Compute total length of roads
?column?
-------------------
55.68114222823703
(1 row)
6.3. Solutions to Sustainable Cities and Communities¶
6.3.1. Exercise: 1 (Chapter: SDG 11)¶
Exercise 1: Create a point for the city
CREATE TABLE
INSERT 0 1
6.3.2. Exercise: 6 (Chapter: SDG 11)¶
Exercise 6: Counting the number of Waterways
count
-------
815
(1 row)
6.3.3. Exercise: 7 (Chapter: SDG 11)¶
Exercise 7: Removing the Rivers which are in swamps
DELETE 31
6.3.4. Exercise: 8 (Chapter: SDG 11)¶
Exercise 8: Get the Connected Components of Waterways
ALTER TABLE
ALTER TABLE
UPDATE 779
UPDATE 784
6.3.5. Exercise: 9 (Chapter: SDG 11)¶
Exercise 9: Creating buffer around the city
ALTER TABLE
UPDATE 1
city_buffer
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0103000020E610000001000000210000009BE61DA7E84C564014AE47E17A44364015A92714E74C564076EAEAF33A443640B640C16AE24C5640DD8B777BFD4336407945C7D8DA4C5640D2C5ABD4C4433640E167B3A8D04C56401C2EDD2C93433640F3C1BF3EC44C5640BCB78D6C6A43364071D00C15B64C5640C7CAA5244C433640CAF8EFB6A64C56404E290C7F39433640E3C798BB964C56403333333333433640FC9641C0864C56404E290C7F3943364055BF2462774C5640C7CAA5244C433640D3CD7138694C5640BCB78D6C6A433640E5277ECE5C4C56401C2EDD2C934336404D4A6A9E524C5640D2C5ABD4C4433640104F700C4B4C5640DD8B777BFD433640B1E60963464C564076EAEAF33A4436402BA913D0444C564014AE47E17A443640B1E60963464C5640B271A4CEBA443640104F700C4B4C56404BD01747F84436404D4A6A9E524C56405696E3ED30453640E5277ECE5C4C56400C2EB29562453640D3CD7138694C56406CA401568B45364055BF2462774C56406191E99DA9453640FC9641C0864C5640DA328343BC453640E3C798BB964C5640F5285C8FC2453640CAF8EFB6A64C5640DA328343BC45364071D00C15B64C56406191E99DA9453640F3C1BF3EC44C56406CA401568B453640E167B3A8D04C56400C2EB295624536407945C7D8DA4C56405696E3ED30453640B640C16AE24C56404BD01747F844364015A92714E74C5640B271A4CEBA4436409BE61DA7E84C564014AE47E17A443640
(1 row)
6.3.6. Exercise: 11 (Chapter: SDG 11)¶
Exercise 11: Finding the components intersecting the buffer
component
-----------
8
57
62
(3 rows)
6.3.7. Exercise: 12 (Chapter: SDG 11)¶
Exercise 12: Get the rain zones
ALTER TABLE
UPDATE 5
6.3.8. Exercise: 13 (Chapter: SDG 11)¶
Exercise 13: Create a union of rain zones
combined_rain_zone
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0103000020E6100000010000007C000000323E840C7D4C5640CECF25AB764236405F7A990B704C56403494CECA964236405DC25946634C56408ABF87BDBD423640C1A237A6584C5640303DDDE7ED423640249BBB93504C56408102F66F25433640F39C4F5E4B4C56401601813362433640BDC63139494C56409AC7B3DCA1433640FB447C394A4C5640E98843F9E14336400968C5394A4C564015E594FDE14336405F1DD849404C56403A5577EED7433640ACA22AF92F4C564083775044CE433640D1D14BCE164C5640F1C9219DC94336400CF50F290A4C56405E7E1330CB433640F3418FC4F44B5640AC581E80D4433640E509D39DEE4B5640C765B120D8433640CA514196DF4B56404EC10A56E3433640066B34B9D04B5640610AF3DFEA4336402FC1DF4DC54B564085CAD719ED433640C9591B3ABF4B5640F1E5EA48EE43364011DAB3B9AA4B56401E9B1316E9433640BE268DB4A24B564099AAB1BFE4433640AC3A89729B4B564051CAA489DE433640C9676CB4874B5640BA0527F0C74336401D40D2D86E4B5640F5AA9FCDA743364014C5557C604B564048EB3A3E924336401B7C4CF8504B56404624C5AC814336407E94B7F1404B5640EA8B6E887D43364041FE3F06314B56408EACF6F9854336402BE983D2214B564021834CAE9A433640B4F811EC134B564006B3BFD9BA43364084EEA9DB074B5640F666D43FE54336405A56FB17FE4A5640FADC6C3F18443640ABE61501F74A564095F6D0E251443640EB74B8DCF24A5640761AF6F28F443640D4CEA2D3F14A5640EBB8490DD0443640FDD604F0F34A5640DF1128BB0F453640A24C1A1DF94A56403866188A4C4536409B18F727014B56401328E0238445364097457CC10B4B5640D4508065B445364018636281184B56407BB13A74DB4536407E693BEA264B56403670D0CFF7453640343BE10E374B56402A58070C104636405459A9A63A4B56400C80311015463640C281CAA3564B5640BDF1E43E3946364078E51B4B594B564073FD897A3C463640F5CA020C714B5640D1EB67AB57463640B85E8172764B564048DC8A105D463640A6DC2B7D834B56407ECAB339684636407F5FBFA6894B564018D4AA856C463640EFA1E0A8974B5640430C1D19744636402951FB739D4B5640218571657646364062D98771B94B56406B7A6A7E7D4636409400739CC24B5640490E28C37D463640358E8546CD4B5640AF9751AF7B463640752D47DEDB4B564073A90BD7784636405D4B742FE24B5640717C28A0764636403B00E98FF64B5640BE24644A6C463640E5FE8A45FB4B56405FEEBE57694636408B333996094C56408A85C9AA5E46364028093C94154C56408EFB5772594636402DD4F420204C5640FED3A9655B463640F01C7BFF234C564029EF54505F46364060D541A42D4C5640C6ACEE726B46364038A83343404C5640D1063783864636407C91432C444C564062D10EC18B463640F1E3EC4F554C5640B6CDEED2A046364056D33CEC574C564026C237D8A34636406B23E593674C56403BC78CD6B4463640CB6FC0B6784C5640ECFC01D2BF46364068083B0B8A4C5640D239E01DBC4636401329E60B8E4C5640D902888AB9463640EE58623E9D4C5640D0F651A9A9463640D6996367AB4C56403533C3788E463640A35FA501B84C56406FA6C0F868463640B115469BB94C5640CDE9CF2D63463640FEDD7DE9BA4C564001298A6D60463640D2EE2B65BF4C56406C47E19954463640060C5C59C34C56408751C0984B463640FECDFD33CD4C564065D752343146364061FA2B1DD14C56404EEE67002546364042FC43BFDC4C5640A8D0CEB7F8453640379138FFE54C56402FDB0C35C4453640CFED0982EC4C5640AC0CBB7C89453640D205AA07F04C5640366688D04A453640B3F8C816F04C564075054D4641453640ADC20E1FF44C564009C0211D39453640A1783163FA4C564095CBB6B83F453640EE5D946A0A4D5640BF8101AA43453640DEA158541A4D564016D8C7053B45364054CFF083294D5640D3890D21264536401EFEF663374D56408C085FC905453640C097EA6B434D56409B4FEB3CDB4436401B3E6F254D4D5640349F491EA84436407C38D930544D56407C6A63646E443640A5A7DA48584D5640FDB920473044364030552D45594D5640C7249529F0433640C5EA1E1C574D564007158482B0433640F0E231EB554D56409B1855719C4336406FB56626504D56408BED70085B433640B04D9011474D56409DC508F21F4336408DDF41EC434D56409FE8DCA30F4336403A7F09153B4D5640A3D8D3B8E84236404163826B394D564059E12B6DE3423640223BE9713A4D56404CCC6847CE42364076D4BABE3B4D56404370292C794236402F6D104D3B4D5640142EC2A35F4236409B072C9F384D5640DB07B14D20423640453BA8E7324D5640C2BF6A46E441364073FFC25E2A4D5640EB9A7DDCAD413640F33073581F4D564031543B277F413640FF882E41124D564052AC26F259413640D197BE99034D5640B5C64AAB3F4136402DC44DF2F34C5640910C2A55314136408E00DDE4E34C5640E7F9CE7C2F41364000B7580FD44C5640389060343A413640F924870DC54C564091C16F1251413640C4DB0B73B74C5640D7B0043673413640154ABBC5AB4C5640D6EA434F9F4136401D207678A24C5640A98A56ACD341364030D41C0D9C4C56406499C0F30C423640F5D774EB904C564041E0C04F29423640F15AF7E28B4C5640153E3F9C39423640E251EE19814C5640FFDB62A963423640323E840C7D4C5640CECF25AB76423640
(1 row)