Perth, Australia

Australia passed the 25 million people mark shortly after 11pm on the 7th of August 2018. This got me thinking, what would a map of Perth look like showing each nationality? Over 28% of Australians was born abroad, what would this translate to in Perth terms?

I took a quick look online to see if anything already existed, the only thing I could find is the below from Perth’s Wikipedia page

One Dot per 100 persons, Perth, Wikipedia

It’s from 2008 and although a gallant effort, there are a few major problems, most notably the lack of a legend. So I decided to see if I could make something, if not better, than as good as the above.

My first job was to source the data, I knew from previously working with ABS data that their pre-built geopackages or datapacks wouldn’t contain the data I needed (question 12 from census ’16) but the geopackages were useful to download the geometry that I needed.

Question 12

I needed to use the Tablebuilder in order to collate the data that I needed for the geometry that I was going to use. This was the main learning area for me, I didn’t know enough about which unit of statisitical geography I wanted to use for this exercise. Luckily, the ABS  have a website where you can compare and contract each unit.

The ABS already had the hard working done in that one of their staticial units is ‘Greater Perth‘, I used this as my boundary and then chose the SA2 as the statistical unit. I went back to Tablebuilder and tried in vain to make sense of it; I found it very cumbersome and non-intuititve to use at the start and their introductory videos weren’t of any help. Fortuntately,  I found an amazing video on YouTube that explained Tablebuilder in great detail and once I’d watched that everything made sense, and I’m a Tablebuilder convert now!

I then used Tablebuilder to build the exact statistics that I needed (Country of Birth by SA2). I saved the table in Tablebuilder and downloaded it as a CSV file. In QGIS I then joined this with the SA2 geopackage file for WA and clipped it using the Greater Perth boundary that I had also downloaded. I then exported this layer as a new geopackage. I had previously found the top 8 nationalities by country of birth (using Tablebuilder) and then created new fields for each one where each number represented 200 persons born in that country. I then used the Random Points Inside Polygons tool to create random points for each nationality.

Generate Ramdom Points Inside Polygons using QGIS

I then used Adobe Color [sic] to pick a decent colour scheme for the various dots. I used Quick OSM in QGIS to download a layer with the towns in Greater Perth to be used for reference, this took about 10 seconds to do, Quick OSM is really useful.

Quick OSM in QGIS

Lastly, I used Google Fonts to download some nice fonts. I also used some styling effects in QGIS before I exported everything to Inkscape in order to add the text. Below is the finished product, the biggest flaw in what I have done is that there are overlapping points but I still think it gives a good overall understading of where people of different nationalities live in Greater Perth.


Excel and Removing Columns

I had a situation today where I had a spreadsheet that contained hundreds of columns. I only needed five or so of these and I didn’t fancy going through them one-by-one to delete the unnecessary ones. I found the below snipped of VBA on stackoverflow. The code ran almost instantaneously and deleted all columns that I didn’t need. The country names are the columns that I needed to keep. I didn’t change/need the part of the code that deletes cells if they don’t contain the string ‘homer’.

 

IRELAND-TOWNLANDS

In Ireland, the townland is the smallest unit of land division. They pre-date the Anglo-Norman conquest (source). What I find amazing about them is how prevalent their use is to this day. Where I grew up in Kerry, they are still used, day-in, day-out to give everything from directions to advertise property and house sales. I find this fascinating; what also amazes me is the number of discussions that occur among friends and in the community regarding townlands and their exact boundaries. Until the OSI released the below dataset, any disputes on the boundaries would have to be resolved using someone’s copy of maps from the 19th Century. It is great to be able to solve these using accurate data.

There has been an OSM project ongoing with a few years to map all the townlands of Ireland. The Ordnance Survey of Ireland released the townland boundaries as open data under a creative commons licence. There are no townlands for the cities of Dublin and Cork but they cover the rest of the country. There are 50,380 townlands in this dataset.

Townlands of Ireland

Townlands of Ireland

Because the ArcGIS Online viewer isn’t fantastic, I uploaded the townlands to Carto to view online. I have only uploaded the 50m generalised dataset as the ungeneralised dataset is ~240MB. Below is a Carto web map of the townlands of Ireland. I hope to do some work in the future on these townlands, such as general statistics and such.

ArcPY Data Driven Pages Script

I had a situation at work a few weeks back where an individual needed 180 maps within a few hours. The maps themselves weren’t overly complex, they required satellite imagery as the basemap, some Ordnance Survey mapping overlaid with each map showcasing a particular site (in the Greater London area). I knew I wouldn’t be able to turn these around if I had to export them manually so enter ArcPy and the power of data driven pages in ArcMap.

I used the basic ‘Grid Index Features‘ tool to create the index for the mapbook and I then created the mapbook as normal. I needed each page to only show one site, to achieve this there is a little workaround in ArcMap to white-out irrelevant features.

The next step was to insert dynamic text for each page using a value from the attribute table (this was the layer name). I carried out a spatial join between the polygons (which contained the field with the polygon/page name) and the grid index features so that each grid index polygon would have the page/polygon name as an attribute. I then used this guidance to ensure each page had its own page number.

Although, it would have been possible to export the mapbook from the ‘File’ menu, it would just export one (quite large) pdf with a single filename as opposed to 180 individual PDFs with each having the correct label and title. I then wrote the following python function in order to export each page. If the same file name is exported more than once it appends an underscore and number to the end. It then took about 20 minutes to export the 180 plans, automation for the win!

 

Highest Number of Persons Born in the UK, Living in Ireland-Census 2016

I was reading an article online the other day about Brexit and I got thinking about all the Irish people (myself included, at least for the next two weeks) that live in the UK. I’ve never heard much said about the people from the UK that live in the Republic.

With no surprise, the border counties contain the highest percentage of persons living in them who were born in the UK. So, the question now is, if we exclude the border counties (Donegal, Leitrim, Cavan, Monaghan and Louth) where in Ireland has the highest percentage of persons living there who were born in the UK?

I used the small area spatial unit for this analysis. The answer is, Templenoe, County Kerry. Templenoe is 6km to the west of Kenmare. Obviously, I can’t say for sure but I would be tempted to guess that part of the reason for this is the presence of the Ring of Kerry Golf Course. Twenty of the sixty-nine people who live there were born in the UK.

UK Born - Census 2016

Percentage Persons Born in the UK

Phoenix, Arizona

I was thinking the other day about the true size of Phoenix, Arizona and its sprawling suburbs. I wondered what this would look like overlaid on Dublin. The coordinate systems are different but the below overlay gives a good indication of its size in comparison to Dublin.

Phoenix Overlaid on Dublin

Phoenix Overlaid on Dublin

Commute to Work-Ireland

I was reading ‘Project Ireland 2040-National Planning Framework‘ and it got me thinking about what percentage of people in each ED commute for an hour or more to work. This is exactly the type of unsustainable living that needs to be avoided by promoting as much infill development as possible in existing urban centres. Below is a map I created that shows the commuting times that people face, obviously, it is important to bear in mind that the stark red colour still only equates to a maximum of 34% of people commuting for an hour or more. This is still just over one third, which is significant. Although not designed with the purpose in mind it gives a good indication of the functional urban area of the major cities (especially Dublin). Commuting Time Ireland-Census 2016

 

 

Python-Quick Graph

I’ve never really had cause to use Matplotlib before so over the weekend I took a quick look at it and how useful it actually is for creating quick graphs. I took the electricity connections dataset (used as a measure of new homes built, however rough) and created a simple line graph. Below is the code I used and the result.

 

And here is the resulting graph:

Python Plot, Electricity Connections

ESB Connections-Ireland

The main strength I’ve seen from it is that it is very easily customisable as well as being a nice addition at various stages of a complex python script in order to error check a methodology in train.

Isochrones-Ireland and Australia

I was reading Topi Tjukanov’s fantastic post yesterday on how far you can drive in one hour from European capitals. This got me thinking, wouldn’t it be interesting to compare driving distance for somewhere in Ireland and somewhere in Australia. For Ireland I chose the geographic centre and for Australia I chose my fiancée’s home city of Perth. I used Digital Geography’s post on utilising the HERE API to generate isochrones for 2, 4 and 5.5 hours driving distances. Unless I’m mistaken, the API seems to max out at 5.5 hours (as measured in seconds) so that’s why 5.5 hours was my upper limit.

Interesting but not surprising to see that you can cover almost the entire of the island of Ireland in that time but it Australia it doesn’t look like much progress at all!

Ireland-Isochrone Map

Ireland-Isochrone Map

Perth-Isochrone Map

Perth-Isochrone Map

Ireland, A Country in Motion: Methodology

I promised late last year that I’d do a blog post explaining how I created the ‘Ireland in Motion’ commuting map. Well, this is that post!

The first thing to say is, that until the ’16 census results came out it wasn’t possible (as a member of the public) to create this type of map as the Central Statistics Office (CSO) just didn’t release the data. Before now (and is still the case) in order to access the full Place of Work, School or College data (POWSCAR) you must attend a training program and sign up to be an ‘Officer of Statistics’. The deciding factor for myself was that you have to be resident in Ireland, which I am currently not. You also have to be a ‘bona fide’ researcher.

So, imagine my delight when I found out that they were releasing an aggregated anonymised dataset for the entire country! The data is aggregated by electoral divisions (ED) and county level. The POWSCAR website where the data can be downloaded is located here. There are two important caveats when talking about this data, EDs where fewer than 10 persons commuted have been excluded and records where no work, school or college were able to be geocoded have been removed. Below is an extract from the CSO’s website showing the categories available.

RESIDENCE_ED_GUID Geographic Unique Identification (GUID) Code for origin Electoral Division (ED)
RESIDENCE_CSOED CSO ED code for origin ED
RESIDENCE_CSOED_LABEL Name of origin CSO ED
RESIDENCE_COUNTY County code for origin county
RESIDENCE_COUNTY_LABEL Name of origin county
POWSC_ED_GUID GUID for destination ED
POWSC_CSOED CSO ED code for destination ED
POWSC_CSOED_LABEL Name of destination ED
POWSC_COUNTY County code for destination county
POWSC_COUNTY_LABEL Name of destination county
COUNT Number of persons commuting

The downloaded zip file when extracted was a 42MB CSV file. CSVs are an ideal format because they are supported by a huge number of programs. I knew that for the type of map I was going to create that I wanted to use create straight lines between the centroids of each ED. The basic methodology I followed was as follows:

  1.  Download CSV, inspect and clean the data (remove any extraneous records).
  2. Download the ungeneralised shapefile of the EDs (available here).
  3. Use QGIS to create the polygon centroids of each ED.
  4. Use the VLOOKUP and concatenate functions in Excel to create well-known text linestrings for the commutes between each EDs.
  5. Use python to parse the CSV file and multiply each row by the number (count) of commutes between each ED. Each row represents one commute between two EDs.
  6. Load the CSV into QGIS and save as a shapefile.
  7. Use FME to load the shapefile file into a PostGIS database.
  8. Connect database to QGIS and create the map.

Detailed Methodology:

1 CSV:

The original number of commutes in the CSV was 2, 750, 239. The following records were removed:

A. The destination was within the same ED (478,884)

B. There was no fixed place of work (174,628)

C. Work/school from home (114,189)

D. Commute to Northern Ireland (9,336)

E. Commute overseas(!) (3,531) were removed.

 

This left the grand total of 1,969, 671 Commutes to be mapped.

2  Download Ungeneralised shapefile:

The ungeneralised shapefile was downloaded from here.

3 Use QGIS to Create the Polygon Centroids:

The centroids of each polygon was quickly calculated in QGIS.

4 Vlookup and Concatenate in Excel:

The attribute table of centroids was exported to Excel and the Vlookup and Concatenate functions were used to create the linestrings for individual commutes as shown below:

5 Python:

A simple python script was used to multiply each line string by the count, so that each individual commute would be represented by a separate line on the map.

6 QGIS-Load CSV:

The CSV file was quickly and easily loaded into QGIS and exported as a shapefile. A better method to do this would probably have been to use FME to load the CSV directly into PostGIS and that’s something I will bear in mind for the future.

7 FME Shapefile:

FME 2017 was used to load the shapefile to PostGIS, and a simple reproject was used to get the data into Irish Transverse Mercator (EPSG 2157).

8 Connect PostGIS to QGIS:

A PostGIS layer can be added in a few clicks from within QGIS. The advantage of using PostGIS is that it will load the 1.96 million lines a lot faster than a shapefile for example, shapefiles have their uses (widely supported for example) but they are an archaic format that will hopefully go the way of the Dodo (this is already happening with the support for Geopackage in QGIS 3 for example).

 

The above is a quick overview of how I carried out the data processing for the map. It’s remarkable that almost all the software used to create the map was open-source. I’d be curious to try and do it totally open-source (replace FME with OGR and Excel with LibreOffice Calc) but as I have a home use licence for FME and Office ’16 I decided to use those.