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

ALTER TABLE
UPDATE 378

6.1.7. Exercise: 11 (Chapter: SDG 3)

Exercise 11: Finding the components which are to be removed

 component 
-----------
       214
       244
       296
       245
       252
       369
       318
(7 rows)

6.1.8. Exercise: 12 (Chapter: SDG 3)

Exercise 12: 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.1.9. Exercise: 13 (Chapter: SDG 3)

Exercise 13: Removing the unwanted edges and vertices

DELETE 29
DELETE 25

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.3290329100878828 | 0102000020E6100000020000004636A15A8E355240838D4699681B334064A82FF0813552409540EF32681B3340
  61 |    158 |     36 | 1.4493409579441923 | 0102000020E61000000200000051C719798F3552409540EF32681B33404636A15A8E355240838D4699681B3340
 472 |    158 |    312 |  2.235454572765966 | 0102000020E61000000200000051C719798F3552409540EF32681B33408D83A6808F355240FDAF29464C1B3340
 237 |     99 |    145 |  2.427530387055349 | 0102000020E61000000200000047FF70FC7535524030EB0FDE681B33406499D9426B3552407D40A033691B3340
 470 |    312 |    311 |  2.791877105220287 | 0102000020E6100000020000008D83A6808F355240FDAF29464C1B33400A01AF858F3552400D8D2782381B3340
  21 |     36 |     13 |  3.904719927544691 | 0102000020E6100000020000004636A15A8E355240838D4699681B3340E63B9DD0903552402B22799DC31B3340
 180 |    145 |    109 |  3.970347591636453 | 0102000020E6100000020000006499D9426B3552407D40A033691B3340A7EA1ED95C3552408E07B6A5691B3340
 259 |    157 |    158 |  4.022391725856522 | 0102000020E610000002000000F716201B923552402B6A300DC31B334051C719798F3552409540EF32681B3340
 236 |    293 |    145 |  4.742001982159778 | 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.904719927544691 | 0102000020E6100000040000005E21BDF26A3552405F3AF768BB1B3340F3CF679B76355240B3C8C04BBA1B33406AAD1EE681355240C2DF8A1FBE1B3340E63B9DD0903552402B22799DC31B3340
  20 |    293 |    145 |  4.742001982159778 | 0102000020E6100000040000005E21BDF26A3552405F3AF768BB1B3340F3CF679B76355240B3C8C04BBA1B33406AAD1EE681355240C2DF8A1FBE1B3340E63B9DD0903552402B22799DC31B3340
  21 |     36 |     98 | 1.3290329100878828 | 0102000020E6100000020000004636A15A8E355240838D4699681B3340E63B9DD0903552402B22799DC31B3340
  21 |     36 |     13 |  3.904719927544691 | 0102000020E6100000020000004636A15A8E355240838D4699681B3340E63B9DD0903552402B22799DC31B3340
  40 |     38 |     24 |   9.24489420855162 | 0102000020E610000002000000139A249694355240BB7B2535591C3340AE896077953552404D6C9967801C3340
  41 |     38 |     24 |   9.24489420855162 | 0102000020E610000002000000FD570C0D7A355240CE2C53DD6D1C3340AE896077953552404D6C9967801C3340
  59 |    159 |     35 |  6.144599533793317 | 0102000020E610000002000000B73066A68F3552409E4B27C8BE1A334040BE840A8E3552403E6BC889C01A3340
  59 |    155 |     35 |  8.764606484713976 | 0102000020E610000002000000B73066A68F3552409E4B27C8BE1A334040BE840A8E3552403E6BC889C01A3340
  59 |    159 |    153 |  8.969520574761862 | 0102000020E610000002000000B73066A68F3552409E4B27C8BE1A334040BE840A8E3552403E6BC889C01A3340
  60 |    159 |     35 |  6.144599533793317 | 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 264
 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 
-------
   529
(1 row)

 count 
-------
   353
(1 row)

6.2.2. Exercise: 6 (Chapter: SDG 7)

Exercise 6: Find the Component ID for Road vertices

UPDATE 353

6.2.3. Exercise: 7 (Chapter: SDG 7)

Exercise 7: Finding the components which are to be removed

 component 
-----------
(0 rows)

6.2.4. Exercise: 8 (Chapter: SDG 7)

Exercise 8: Finding the road vertices of these components

 id 
----
(0 rows)

6.2.5. Exercise: 9 (Chapter: SDG 7)

Exercise 9: Removing the unwanted edges and vertices

DELETE 0
DELETE 0

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.893749087931763
(1 row)

6.2.8. Exercise: 12 (Chapter: SDG 7)

Exercise 12: Compute total length of roads

     ?column?     
------------------
 55.6811422282419
(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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 0103000020E6100000010000007C0000005DC25946634C56408ABF87BDBD423640C1A237A6584C5640303DDDE7ED423640249BBB93504C56408102F66F25433640F39C4F5E4B4C56401601813362433640BDC63139494C56409AC7B3DCA1433640FB447C394A4C5640E98843F9E14336400968C5394A4C564015E594FDE14336405F1DD849404C56403A5577EED7433640ACA22AF92F4C564083775044CE433640D1D14BCE164C5640F1C9219DC94336400CF50F290A4C56405E7E1330CB433640F3418FC4F44B5640AC581E80D4433640E509D39DEE4B5640C765B120D8433640CA514196DF4B56404EC10A56E3433640066B34B9D04B5640610AF3DFEA4336402FC1DF4DC54B564085CAD719ED433640C9591B3ABF4B5640F1E5EA48EE43364011DAB3B9AA4B56401E9B1316E9433640BE268DB4A24B564099AAB1BFE4433640AC3A89729B4B564051CAA489DE433640C9676CB4874B5640BA0527F0C74336401D40D2D86E4B5640F5AA9FCDA743364014C5557C604B564048EB3A3E924336401B7C4CF8504B56404624C5AC814336407E94B7F1404B5640EA8B6E887D43364041FE3F06314B56408EACF6F9854336402BE983D2214B564021834CAE9A433640B4F811EC134B564006B3BFD9BA43364084EEA9DB074B5640F666D43FE54336405A56FB17FE4A5640FADC6C3F18443640ABE61501F74A564095F6D0E251443640EB74B8DCF24A5640761AF6F28F443640D4CEA2D3F14A5640EBB8490DD0443640FDD604F0F34A5640DF1128BB0F453640A24C1A1DF94A56403866188A4C4536409B18F727014B56401328E0238445364097457CC10B4B5640D4508065B445364018636281184B56407BB13A74DB4536407E693BEA264B56403670D0CFF7453640343BE10E374B56402A58070C104636405459A9A63A4B56400C80311015463640C281CAA3564B5640BDF1E43E3946364078E51B4B594B564073FD897A3C463640F5CA020C714B5640D1EB67AB57463640B85E8172764B564048DC8A105D463640A6DC2B7D834B56407ECAB339684636407F5FBFA6894B564018D4AA856C463640EFA1E0A8974B5640430C1D19744636402951FB739D4B5640218571657646364062D98771B94B56406B7A6A7E7D4636409400739CC24B5640490E28C37D463640358E8546CD4B5640AF9751AF7B463640752D47DEDB4B564073A90BD7784636405D4B742FE24B5640717C28A0764636403B00E98FF64B5640BE24644A6C463640E5FE8A45FB4B56405FEEBE57694636408B333996094C56408A85C9AA5E46364028093C94154C56408EFB5772594636402DD4F420204C5640FED3A9655B463640F01C7BFF234C564029EF54505F46364060D541A42D4C5640C6ACEE726B46364038A83343404C5640D1063783864636407C91432C444C564062D10EC18B463640F1E3EC4F554C5640B6CDEED2A046364056D33CEC574C564026C237D8A34636406B23E593674C56403BC78CD6B4463640CB6FC0B6784C5640ECFC01D2BF46364068083B0B8A4C5640D239E01DBC4636401329E60B8E4C5640D902888AB9463640EE58623E9D4C5640D0F651A9A9463640D6996367AB4C56403533C3788E463640A35FA501B84C56406FA6C0F868463640B115469BB94C5640CDE9CF2D63463640FEDD7DE9BA4C564001298A6D60463640D2EE2B65BF4C56406C47E19954463640060C5C59C34C56408751C0984B463640FECDFD33CD4C564065D752343146364061FA2B1DD14C56404EEE67002546364042FC43BFDC4C5640A8D0CEB7F8453640379138FFE54C56402FDB0C35C4453640CFED0982EC4C5640AC0CBB7C89453640D205AA07F04C5640366688D04A453640B3F8C816F04C564075054D4641453640ADC20E1FF44C564009C0211D39453640A1783163FA4C564095CBB6B83F453640EE5D946A0A4D5640BF8101AA43453640DEA158541A4D564016D8C7053B45364054CFF083294D5640D3890D21264536401EFEF663374D56408C085FC905453640C097EA6B434D56409B4FEB3CDB4436401B3E6F254D4D5640349F491EA84436407C38D930544D56407C6A63646E443640A5A7DA48584D5640FDB920473044364030552D45594D5640C7249529F0433640C5EA1E1C574D564007158482B0433640F0E231EB554D56409B1855719C4336406FB56626504D56408BED70085B433640B04D9011474D56409DC508F21F4336408DDF41EC434D56409FE8DCA30F4336403A7F09153B4D5640A3D8D3B8E84236404163826B394D564059E12B6DE3423640223BE9713A4D56404CCC6847CE42364076D4BABE3B4D56404370292C794236402F6D104D3B4D5640142EC2A35F4236409B072C9F384D5640DB07B14D20423640453BA8E7324D5640C2BF6A46E441364073FFC25E2A4D5640EB9A7DDCAD413640F33073581F4D564031543B277F413640FF882E41124D564052AC26F259413640D197BE99034D5640B5C64AAB3F4136402DC44DF2F34C5640910C2A55314136408E00DDE4E34C5640E7F9CE7C2F41364000B7580FD44C5640389060343A413640F924870DC54C564091C16F1251413640C4DB0B73B74C5640D7B0043673413640154ABBC5AB4C5640D6EA434F9F4136401D207678A24C5640A98A56ACD341364030D41C0D9C4C56406499C0F30C423640F5D774EB904C564041E0C04F29423640F15AF7E28B4C5640153E3F9C39423640E251EE19814C5640FFDB62A963423640323E840C7D4C5640CECF25AB764236405F7A990B704C56403494CECA964236405DC25946634C56408ABF87BDBD423640
(1 row)