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

 

 

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.

Farthest McDonald’s-Ireland-Part II

I did a bit of quick work today to figure out what’s the farthest you can be from a McDonald’s in Ireland. I used the usual method that I’ve followed in a few previous posts, I calculated the euclidean distance from the existing McDonald’s, that was the OSM Overpass-turbo GeoJSON that I downloaded and used OGR2OGR to convert it to a shapefile. I had planned on using PostGIS for this and St_Distance to find this out but I went with the tried and tested method. The farthest you can be from a McDonald’s in Ireland is Co. Galway, more specifically in the townland of Ballinaleama. It is 75.3km from the nearest McDonald’s at the Westside Shopping Centre in Galway.

Farthest from McDonald's-Ireland

Farthest from McDonald’s-Ireland

 

McDonald’s

I was driving past a McDonald’s in Ireland last week and it got me thinking about the farthest distance you can be from a McDonald’s in Ireland. This has been attempted by others before me for the UK and the United States etc. but I can’t find anything on Ireland so I will give it a go. This post is just a quick post on how to download the relevant GeoJSON file. I will use overpass-turbo.eu to download the McDonald’s locations.

Overpass Turbo

I then used the wizard to look for McDonald’s in Ireland, created a bounding box for Ireland and ran the query, the below was the result.

McDonald's Locations in Ireland

McDonald’s Locations in Ireland

 

This was then exported as a GeoJSON file, in my next post I’ll load this as a table into a PostGIS database and find the farthest point from a McDonald’s in Ireland.

Distance to Nearest Road (Most Remote Point)-Rep. of Ireland

I recently read an article by Mark Jenkins in the Guardian celebrating 100 years since the foundation of the US National Parks Service. In this article he seeks the help of a GIS specialist to find the most remote place in the contiguous United States. It turns out that this point is just over 20 miles from the closest road. I strongly suspect that publicly accessible land must have been one of criteria as the centre of the Nevada Test Site (Wiki article)is certainly farthest than the point they found. I have emailed the GIS specialist and asked him the criteria he used. I’ll update this post if he responds.

OpenStreetMap view of the Nevada Test site.

OpenStreetMap view of the Nevada Test site.

This got me thinking, if for an area as large as the contiguous USA (8,080,464.3 km2) the distance is so small what would it be for Ireland given that Ireland is 95 times smaller than it (8,080,464.3/84,421=95.7). I did some research to see if any greater minds than my own (read: ALL) have undertaken any research on this. The first (and in my opinion the most reliable) source I found was the United States Geological Survey (USGS). They have published a brief paper (link) where they give the distance to nearest road every 30 metres for the lower 48. They have also published an academic paper (Roadless Space of the Conterminous United States) that can be viewed for free if you register.

The first thing to do was to acquire the administrative area of Ireland (for this I used the OSI’s landmask shapefile). The next step was to download all the rivers and lakes of Ireland (these were downloaded from the EPA’s geoportal). The final step was data acquisition was to download the OpenStreetMap road network from Geofabrik. Once all these were downloaded, I could begin.

The first task was to use remove all features from the roads shapefile that contained attributes not relevant for this exercise (any non-publicly accessible road). The features removed were:

  1. Bridleway
  2. Footway
  3. Steps
  4. Path
  5. Pedestrian
  6. Track
  7. Track_grade1
  8. Track_grade2
  9. Track_grade3
  10. Track_grade4
  11. Track_grade5
  12. Unknown

The above features were selected from the roads layer in ArcGIS as shown below.

 

Select by Attributes

Select by Attributes

 

The next step was to invert this selection and export it to a file geodatabase, 103,808 attributes were non suitable for this analysis. Once this selection was exported it was added to the map. A problem faced at this stage was that all of the roads for Northern Ireland were included. There were two maps to approach this problem, the first was to clip them to the extent of the Republic shapefile or the other option would leave them be and they would not be included by default when the zonal statistics was run as the zone would be the Irish shapefile. The second option was chosen for simply time saving reasons, from previous experience, a clip of the roads would take quite some time.

As all the data that was downloaded was in the geographic coordinate system WGS84 it had to be reprojected into TM65 Irish Grid (EPSG 29902). Everything was then exported to a file geodatabase to speed up future calculations.

Reproject in ArcMap

Reproject in ArcMap

 

The next step was to calculate the Euclidean distance for the roads layer. This was done using the Euclidean distance tool in ArcMap. The main variable here is what cell size to use, obviously the smaller the cell size the more accurate result but this comes at the expensive of processing time and file size. I chose a 30m cell size as I felt this would strike the right balance. This operation ran very quickly and the resulting raster with the roads overlaid is shown below.

Euclidean Distance Raster

Euclidean Distance Raster

 

With this raster each cell has a value based on its Euclidean distance from the road polyline. For the purposes of this exercise, the islands were discounted as I was only interested in the main landmass of Ireland. The islands were removed. The next step was to calculate the zonal statistics, the zone in this case was the Ireland polygon and the statistic that was calculated was ‘MAXIMUM’; this would tell us the cell that was the maximum Euclidean distance from a road.

The zonal statistics tool creates a new raster with just the maximum value present. This raster was then used in the following equation to calculate the locations where the distance equals the maximum distance.

Raster Calculator

Raster Calculator

 

This then generates a raster with only one value, this is then converted to a point using the ‘Raster to Point’ tool in the ArcToolbox. The result is shown in the image below. The farthest point from a road in Ireland is 6,760m from a road, it is in Nephin Beg Mountains in Ballycroy National Park in Co. Mayo, very close to Slieve Carr. To put this in perspective, the farthest you can be from a road in Ireland is the same distance as between O’Connell Bridge and Farmleigh Park to the west of the Phoenix Park, a very short distance indeed!

There are two drawbacks to my methodology, the data used from OSM is only as accurate as its contributors and I didn’t clip out the lakes and rivers of Ireland. I purposefully didn’t clip out the lakes and rivers because the end result would not have altered (as long as the point wasn’t in the middle of a lake at which point I would have clipped out the lakes).

I welcome any criticism of my methodology and I’ve purposefully went into a minutia of detail so as to encourage constructive criticism.

 

 

By Sarah777 - Own work, Public Domain, https://commons.wikimedia.org/w/index.php?curid=5902440

By Sarah777 – Own work, Public Domain, https://commons.wikimedia.org/w/index.php?curid=5902440

Most Remote Point in Ireland

Most Remote Point in Ireland

Mean Centre of Irish Population

In anticpation of results of the 2016 Irish census I thought that it would be interesting to calculate the mean centre of population from the 2011 census. The data was downloaded from the Central Statistics Office website. The next step was to download the census boundaries from here. Once downloaded they were opened in ArcMap 10.2.2 and the Mean Center (sic) tool was used from the Spatial Statistics toolbox to calculate the mean centre with the total population for each small area used for the weight. Below is the mean population centre for the Republic, it is located in the townland of Capard, Co. Laois.

Mean Population Centre, Ireland

Mean Population Centre, Ireland