Mapping the population density of New Zealand with QGIS, SQLite and TileMill

There are over 4.4 million people living in New Zealand, but they’re not evenly distributed across the country. On my travels, I’ve been to some very remote areas like Doubtful Sound in Fjordland with a permanent population of one! Where do the New Zealanders live? Let’s create a population density map of New Zealand.

Doubtful Sound

According to Lonely Planet, 63% of New Zealanders live on the North Island, 20% on the South Island, 10% in Australia, 5% in the rest of the world, and 2% are travelling! The area of New Zealand is 268,021 km2 (Norway has 385,252 km2), which means there are about 16.5 New Zealanders per km2. Norwegians have a bit more space, we're only 15.5 persons per km2.

Auckland

I want to map the population density as a choropleth map, using darker colors for higher densities. But which units or geographical areas should I use? The regions (territorial authorities) I mapped in my last blog post are too big to show the population density of the country. Instead, I decided to use area units, which are non-administrative areas that are in between meshblocks (the smallest geographic unit for which statistical data is collected by Statistics New Zealand) and territorial authorities. In urban areas, area units normally contain a population of 3,000–5,000.

The census is the official count of how many people and dwellings there are in New Zealand. It takes a snapshot of the people in New Zealand and the places where we live. The last census was in 2006. There was supposed to be a new census in 2011, but it could not be completed due the national state of emergency after the Christchurch earthquake. A new census is scheduled for 2013.

I therefore decided to download the area units used for 2006 census. These can be found on Koordinates.com. I used QGIS to removed ocean, lakes, inlets and tidal bay areas where no or very few people live – which gives me the shape of New Zealand the people recognize.

The yellow area shows the extent of the original dataset, while the red color shows the area units after removing osean, lakes, inlets and tidal bay areas.

I’ve also used the field calculator in QGIS to calculate the size (in square kilometers) of each area unit polygon.

The dataset is in the New Zealand Transverse Mercator 2000 (NZTM2000) projection, which is using a metric coordinate system. The formula to calculate the area in km2 is $area / 1,000,000. There are one million square meters in a square kilometer.

The largest area unit is Fjordland with 8287 km2. The smallest area unit is
Motuopae Island in Tauranga, with less than 0.03 km2.

We can save the dataset as a SQLite database by right-clicking on the layer name in QGIS and select “Save as…”.

Using SQLite will allow us to add the population data to the same file.

Now that we have the area units, we need some population data covering the same geographical areas. Luckily, getting statistical data in New Zealand is as easy as getting geospatial data. I used the beta release of NZ.Stat to download estimated population data for 2006-2011.

It was not possible to download the dataset as a CSV file when I tried, so I downloaded the Excel version. I then created a CSV file with this format (first line is the column names):

no;name;pop2006;pop2007;pop2008;pop2009;pop2010;pop2011
500100;Awanui;370;360;360;360;360;360
500202;Karikari Peninsula-Maungataniwha;4350;4320;4300;4320;4370;4350
500203;Taipa Bay-Mangonui;1610;1590;1570;1560;1550;1540
500204;Herekino;2020;2000;1980;1960;1960;1950
500205;Ahipara;1160;1160;1180;1170;1170;1180
500206;North Cape;520;500;490;480;460;450

I used SQLite Database Browser to import the population dataset to the file containing the area units (File -> Import -> Table from CSV file).

Finally, we can use TileMill to create population density map. You can create a layer from a SQLite database like this:

I'm using this SQL query to join the area units and the population data:


SELECT OGC_FID, au_no, au_name, geometry, area, pop2011, (pop2011/area) AS popdens FROM nz_area_units_2006_census AS area JOIN population ON au_no = no

The query is also calculating the population per km2 (pop2011/area), which will be our mapping variable. Also note that you need to specify the SRS projection string for the dataset (NZTM2000):

+proj=tmerc +lat_0=0 +lon_0=173 +k=0.9996 +x_0=1600000 +y_0=10000000 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs

I've used a 9-class yellow-orange-brown color scheme from ColorBrewer to represent increasing population density.

This is my CartoCSS:


The map looks like this:

Recommended reading: Working with TileMill and SQLite (MapBox)

I'm going to create an interactive version with a map legend in my next blog post. Stay tuned!