18 June 2017 | RustyB
So in this post the first in a series of little real world use cases of MapLesotho data, we’re going to be mapping building density in Lesotho.
For those not in the know that means we’re going to split lesotho into little parcels of a fixed size and then count the number of buildings within each parcel.
We will make the following assumptions if you’re following along, that you’ve got:
osm2pgsql
The first thing we’re going to do is create a hexgon grid to cover the land area of Lesotho. Although we can do this in QGIS i’m going to show you how to easily create one in PostGIS.
Thankfully CartoDB has already made a super handy function for use to create on of these grid. You should run the code below in your osm database to add the CDB_HexagonGrid()
function.
Now we shall make our hexagon grid table and make one that matches the are of Lesotho.
And the result:
So next we’re going to do a little prepartory work to make our queries work a little faster when querying the buildings of Lesotho. There are > 750,000 building in case you didn’t know.
First we will create a new table containing only buildings. You’ll notice we’re transforming our geomtetry, this is to allow us to take advantage of geography queries in later posts. We also get the centre point of the each polygon.
Now to help our queries run much faster, we will create some spatial indexs on the geometry and osm_id columns. This may take a few minutes to run through.
Here’s a quick image of the buildings and their center points.
Now onto the fancy part we’re going to use some spatial joins to count how many centroid points are within each hexagon. Running the query below will add a now column to the hexgrid table for the building count.
If we bring these layers into QGIS we can then visualise the results:
You can see that the highest densities are surrounding the major settlements and in the north from Maseru to Bera, to Leribe, and Buthe-Buthe.
Have an idea another idea of a #MapLesotho question we could answer with this technique, then let me know on twitter @Rusty1052