# 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
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------