#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

#Maplesotho. Qacha’s Nek’s yet another success

IMG_20160905_191456

presenting the completion of the task #1942 of the Qacha’s Nek district detailed mapping, the first district to 100% mapped. Qacha’s Nek Assistant Physical Planners (APPs), would like to pledge their gratitude to the following people;  Andrewbuck, Autre for Nepal, DebigC, Greenmtdave, RustyB and Tshedy  for their most valued support making up the 13.5% of the contribution towards the completion this task.

img-20160906-wa0001

Mapping this task was a real deal and a great adventure for QN Mappers (APPs). It was not an easy road but it came with positive factors like the much appreciated outside help mentioned above and the  never ending courage they received from the DebigC . all this helped them to overcome any obstacle. QN Mappers(APPs) would also like to recognize her Majesty the the Queen of Maplesotho A.K.A Tshedy,  for being their inspiration. “we want to be a bunch of you.”

img-20160906-wa0000

QN Mappers namely; (MontshyQn, FifiQn, Topsqn, Mabatho and Stanley Makhanya)img-20160907-wa0005  showed great team work and extended much dedication which lead towards the successful completion of this task. Not leaving behind MontshyQn who has claimed a leadership position by throwing in the  28% contribution.—> way to girl, keep up the good work.<—

 

#Maplesotho QN’ s success

img-20160907-wa0006

Qacha’s Nek celebrates victory when QN Analysis kombat, the analysis team from Qacha’s Nek was presented a winner in the analysis month competition. QN Analysis Kombat members, Fifiqn, Stanley Makhanya and MontshiQn, and Topsqn (a behind the scene member) dedicated their all when they were confronted with this competition. This competition was an uphill climb for them, it hurt their heads a lot but in the end, it was all worth it when they won themselves a prize of a limitless month’s internet. QN Analysis Kombat wishes to thank the following people

  • Fifiqn the team manager who worked tirelessly and drove her team towards this victory.
  • RustyB, who was always available to lend a helping hand here and there at any given time of the day.

QN Analysis Kombat, again thanks RustyB since this competition introduced them to other interesting and new things like how to commit repositories using a markdown format in github, how to create, open and use gists also in github and how to interact using slack, a very interesting application which is like whatsapp but even better since one can even add “code snippets” so that they appear as they should, something whatsapp cannot do I think.  QN Analysis Kombat’s team members, worked till late ours, they googled, they read a lot and they consulted in order to achieve all this greatness. In the end, out of this competition, a lot of knowledge and skill is acquired. QN Analysis Kombat wishes to thank all those who congratulated them upon their victory.

#Maplesotho, this is one project we are proud of, that brings out our inner hard worker.

GO QN ANALYSIS KOMBAT and  RISE #Maplesotho.

#Maplesotho’s Analysts

img_20160826_202146

I know I am becoming one and so can you be. I feel lucky to have been chosen this year to come to Advanced OSM Training. I did not know exactly what to expect, but I knew what I wanted. I wanted to learn and gain knowledge on everything that has to do with #maplesotho. As a land use and settlement planner, I only knew one way to approach planning, and this was using Arc GIS. But then since I am a very ambitious and curious person, when I learned about Open Street Mapping (OSM) I wanted to develop more skills towards the approach to planning. At first, I thought, it was just another project which added more to what I already know. But later, it occurred to me that, not only does it add more to my knowledge, but it teaches me a whole different and adequate way to approach planning.

If you try it you will find that it is as easy as this, first step is to get signed up into open street map, so that you can have a username and password that will log you in whenever you want to map. The next step is you map (edit) and assign tags in open street map. This is a skill I developed even before I was picked for the training this year. I gained this skill from taking interest and mapping as an individual, interaction and dialogue with other mappers (APPs), the mapathons which have been held and the competitions which were basically the motivation for us to map and finish the base map. At first I used ID Editor, which is basic but from the mapathons and interaction with other mappers, I learned about Java Open Street Map (JOSM) Editor. But as we speak now, I am still a little bit of a rough diamond, almost polished since I am learning more on JOSM. For Example, how to shortcut it by using keyboard keys and also how to apply the correct osm tags through oms wiki and communication.

So, now Analysis comes in as a third step. It is the third step because, without osm data you cannot be able to perform analysis. This is where the most action is found. #Maplesotho gets a little bit complicated here. But that is just what it is, a little bit. Dear mappers, from what I gathered, you think this is too difficult, but let me let you in on a secret, it is not that difficult, it is just exciting. You also have it encrypted in your minds that it is so tough to crack but it is not. It only needs you to love it and it will love you back. Whenever you are confronted with analysis, give it attention, dedicate yourself to it, invest all your efforts and time on it. Even when you come across new things about it because this is going to happen often, try hard to crack them and if you feel like you are failing to do so do not give up, be resourceful  (read something, google something) or require assistance. I know this one beardy who is ready and willing to help anyone out. This is exactly what I do myself and if you do this and probably more, you will fall in love with analysis. You will feel like a totally brand new person, –>>A SPATIAL ANALYSIST. Atleast this is how I feel now and I want to grow as one. Right now, I feel like I am headed towards applying all this knowledge I have acquired to landuse and settlement planning.

I feel like my lazy mind is awake, I have learned how to command the computer into loading osm data into postgis, and that as it does this, it is already creating a database. Now using postgis, I have learned how to manipulate this data and perform wanted analysis using a Structured Querybased Language (SQL). And then how to display, my query results in Quantum GIS (QGIS), layout it and export it to PDF for printing and or other formats also save it as a project for later reuse.

IMPORTANT: if you think about it, the beardies are not going to be with us forever, they are here to equip us with skills and tools that will help us plan Lesotho better. The MOU which lets us be with them will not be renewed forever even though we wish otherwise. If we do not learn how to apply spatial planning ourselves, the big question is who will do this for you except for yourselves when they are not here? Hard fact: Our country needs more analysts.

QN Mapaholics at #MapLesotho

Mapping, ….food for the soul. The 27th and the 28th days of June 2016, were the two dates set for mapping task #1942 #MapLesotho – Qacha’s Nek Detailed District Mapping. So we Qacha’s Nek mappers hit open street map and showed much dedication towards mapping our task. There are 600+ tiles and we have 180 of them done now. We need to meet regularly if we are to finish by the end of this year.

 

8d535163-246f-4bcf-8c04-7a0532e15732

Stanley Makhanya in silouette mapping in the red room

This was seen when we applied all of our learned techniques, bearing in mind the balance needed between speed and quality since they were able to achieve at least 16% of the district in those two days with the smile across their faces and heads nodding in rhythm to the beats of hip-hop music playing from Stanley Makhanya’s laptop. Moving upwards from the 12% that was already mapped, now 28% of the district is mapped.

55629696-4230-48f1-9c0b-5a7bc235fd44

QN mappers doing what we do best

As QN Mappers we would like to give a special thanks to the following awesome individuals DeBigC and “Her Majesty”, Queen Tshedy for lending a hand. Thanks to DeBigC again who tirelessly encouraged our team during throughout journey. Also it was nice to get #HotOSM help from Andrew Buck who did 9 tiles while we slept!

We again promise to continue at this rate till the whole district is completely covered because mapping is like the craving and or the addiction you will not be able to overcome. On a serious note we know the mapping we do now is data for us as planners to use later. I have learned how to use SQL in PostGIS and I know good tidy tracing and accurate tagging let’s me extract data with confidence. That’s why quality is key.