#maplesotho analysis, QN vs Quthing the 2 complete districts

*Query*

SELECT admin_level,name, way

FROM planet_osm_polygon

WHERE admin_level is not null

 

*results*

admin_level name
5 “Mokhotlong District”
2 “South Africa”
2 “Lesotho”
5 “Mafeteng District”
5 “Mohale’s Hoek”
5 “Maseru District”
5 “Berea District”
5 “Quthing District”
5 “Leribe District”
10 “Setsoto Ward 14”
10 “setsoto Ward 12”
5 “Thaba_Tseka District”
5 “Butha-Buthe District”
5 “Qacha’s Nek District”

 

 

Next will be how to extract a district from the entire planet_osm

*Query*

Qacha’s  Nek                                                                         Quthing

CREATE TABLE qacha_district AS                 CREATE TABLE quthing_district AS

SELECT admin_level, name                              SELECT admin_level, name

FROM planet_osm_polygon                              FROM planet_osm_polygon

WHERE admin_level = ‘5’                                 WHERE admin_level = ‘5’

AND name = ‘Qacha’’s Nek District                 AND name = ‘Quthing District

 

Then in the next queries, we want to get the geometries that are only within two districts of choice (Quthing and Qacha’s Nek), so I had to use the ST_Intersects function. this will intersect my two specific districts with the rest of the planet geometries, namely; points, lines and polygons.

 

 

I was awe struck as I waited while the queries ran, for example, for qacha_lines table, it resulted in 32965 rows and took 3409067ms overall to load the results. This is an example of a map with content.

 

NB: there are numerous different ways around querying this.

 

*Queries*

Qacha’s Nek                                                                    Quthing

points

CREATE TABLE qacha_points AS (                CREATE TABLE quthing_points AS (

SELECT *                                                             SELECT *

FROM planet_osm_point as qp                             FROM planet_osm_point as qp

WHERE ST_Intersects(qp.way, (                         WHERE ST_Intersects(qp.way, (

SELECT way                                                       SELECT way

FROM planet_osm_polygon                                FROM planet_osm_polygon

WHERE admin_level = ‘5’ AND                         WHERE admin_level = ‘5’ AND

name = ‘Qacha’’s Nek District’)))                          name = ‘Quthing District’)))

 

 

lines

CREATE TABLE qacha_lines AS (                     CREATE TABLE quthing_lines AS

SELECT *                                                                SELECT *

FROM planet_osm_line as ln                                   FROM planet_osm_line as qn

WHERE ST_Intersects (ln.way, (                            WHERE ST­_Intersects(qn.way, (

SELECT way                                                              SELECT way

FROM planet_osm_polygon                                     FROM planet_osm_polygon

WHERE admin_level = ‘5’                                       WHERE admin_level = ‘5’

AND name = ‘Qacha’’s Nek District’)))                   AND name = ‘Quthing District’

 

You can do the same thing for polygons, you will just have to substitute the words line or point with polygon.

 

Now the next step is to create tables that count many things that are mapped in your districts as they are tagged, could be buildings, roads, rivers e.t.c as follows:

*Query*

SELECT highway, count(*)                               SELECT highway, Count(*)

FROM qacha_lines                                             FROM quthing_lines

WHERE highway is not null                              WHERE highway is not null

GROUP BY highway                                         GROUP BY highway

 

*Results*

QACHA                                                                     QUTHING

Highway Count highway count
Primary 34 primary 23
unclassified 167 secondary 13
secondary 36 unclassified 348
track 646 track 780
footway 458 footway 13
service 13 service 12
path 1520 path 2694
tertiary 47 tertiary 56
residential 703 residential 1139
road 6
bridleway 1

 

 

 

*Query*

SELECT waterway, count(*)                                     SELECT waterway, count(*)

FROM qacha_lines                                                    FROM quthing_lines

WHERE waterway is not null                                    WHERE waterway is not null

GROUP BY waterway                                              GROUP BY waterway

 

*Results*

QACHA                                                                     QUTHING

Waterway Count waterway count
Drain 20 drain 110
Weir 3 weir 3
River 141 river 149
Stream 14367 dam 1

 

*Query*

SELECT landuse, count(*)                                         SELECT landuse, count(*)

FROM qacha_polygon                                               FROM quthing_polygon

WHERE landuse is not null                                        WHERE landuse is not null

GROUP BY landuse                                                  GROUP BY landuse

 

*Results*

QACHA                                                                     QUTHING

Landuse Count landuse count
Cemetery 12 cemetery 82
Meadow 1 recreation_ground 1
Farmland 4184 farm 1
Farmyard 24 farmland 7760
Military 1 farmyard 258
residential 700 military 1
Orchard 3 residential 1935
Forest 24 orchard 1
Industrial 3 forest 154
Quarry 9 Quarry 42
plant_nursery 2 Industrial 1
commercial 9 Commercial 13
Landfill 1 Grass 6
Retail 1 Garages 1
health centre 1

 

 

*Query*

SELECT building, count(*)                                        SELECT building, count (*)

FROM qacha_polygon                                               FROM quthing_polygon

WHERE building is not null                                       WHERE building is not null

GROUP BY building                                                 GROUP BY building

 

*Results*

QACHA                                                                     QUTHING

Building count building count
House 38 house| 64
Church 4 Church 13
Ruins 104 farm 4
Hangar 1 ruins 290
Hospital 4 Kraal 2
Apartments 2 Industrial 3
Industrial 7 Commercial 130
Commercial 14 Clinic 1
Ruin 7 Office 40
Office 4 Yez 1
Civic 1 Wall 5
School 15 School 25
Shed 84 Terrace 1
Farm houses 2 Shed 11
Garage 2 Service 2
Wall 5 Hu 1
Residential 10 Garage 1
Wal 333 Residential 8
Cabin 3 Building 1
Yes 20431 Yes 34269
Construction 1349 Construction 3460
Retail 1 Retail 38
Hotel 11 Hotel 3
Hut 17910 Hut 24813
Garages 1 Barn 2
Ruind 1 Woolshed 1

 

After running and checking them analysis, especially for Qacha’s Nek because I definitely would know anything about Quthing. Our data is still not accurately tagged. For instance some of the buildings are tagged as yez instead of yes, wal instead of wall or ruind instead of ruins, more over some of the numbers do not match what is observeable of the area or on ground. For example, Qacha’s Nek does not have any industrial buildings but in the tabulated results our Industrial buildings = 7.

this is open for everyone to comment though!

the data used was that of the 28th december 2016, downloaded from download.geofabrik.de. this was loaded into pgadmin using the following command line:

cd C:\Program Files\osm

-c -d lesotho -K -H localhost -U postgres -P 5432 –slim -S default.style 2812-lesotho-latest.osm.pbf

Advertisements

One thought on “#maplesotho analysis, QN vs Quthing the 2 complete districts

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s