Covid-19 Cases Ireland – 26 March 2020.
Updated map for the 25th of March 2020.
I’ve been creating daily maps of Covid-19 cases in Ireland using the official Government source. I think proportional symbols and proper labelling lead to the best outcome.
I was thinking the other week about the amount of bogs around Europe after the collapse of a trial against turf cutters in Galway. I put the below map and tweet together in a few minutes that shows the peatlands of Europe. The outlines of Iceland, Ireland, Northern England, Scandinavia and Scotland are visible from their peat cover alone. The data comes from the European Commission’s CORINE land cover dataset.
After completing the above I thought I’d put together a map showing the peat cover in Ireland. Besides from the CORINE land cover dataset I used a DEM from the European Space Agency to create the hillshade and the administrative data comes from Ordnance Survey Ireland and OpenDataNI. The dataset doesn’t differentiate between raised and blanket bogs but for my purposes that’s okay.
Below is the finished map. I tried something new and used abbreviated county name labels à la various maps for the USA where the state names are abbreviated. I think it works well.
I was thinking since I posted this that there’s a bit of work involved to figure out the order of peat per county so I created a graph. For any GIS folks out there, I quickly unioned the county layer and the peat layer and then calculated the area in square kilometres for the peat and exported this as a CSV. I then used Matplotlib to create the below graph. If you’d like to know more about simple workflows like these just reach out to me on Twitter (@pearoid).
I was reading this Guardian article the other day where they produced maps showing the number of Airbnb listings per 100 dwellings. I thought it was really interesting and I hadn’t seen Airbnb data mapped like that before. I had a few hours to spare yesterday so I set about replicating their method for Ireland. I used the 2016 census electoral divisions (to get the household numbers) and data for Ireland from Inside Airbnb. I think at best this data is questionable because from the reading I’ve undertaken it seems to still list properties that were briefly on Airbnb a number of years ago and have long since been removed however this is the only data available so I went with it.
Below is the map, it was made with a combination of Bash, GDAL, QGIS, LibreOffice Calc and Illustrator.
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.
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.
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).
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:
- Download CSV, inspect and clean the data (remove any extraneous records).
- Download the ungeneralised shapefile of the EDs (available here).
- Use QGIS to create the polygon centroids of each ED.
- Use the VLOOKUP and concatenate functions in Excel to create well-known text linestrings for the commutes between each EDs.
- 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.
- Load the CSV into QGIS and save as a shapefile.
- Use FME to load the shapefile file into a PostGIS database.
- Connect database to QGIS and create the map.
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:
1001,1002,1,-6.92771,52.83721,-6.93919,52.83783,"LINESTRING (-6.92771 52.83721, -6.93919 52.83783)"
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.
In my last post I downloaded all the McDonald’s in Ireland using overpass-turbo. The problem I had when I exported these was that because GeoJSON is such a flexible format, I was getting both nodes and ways. These would have had to be imported into ArcMap separately and the centroids of the ways found and merged with the points, this would have taken some time. Instead I got a great suggestion “Mmd Osm” on Youtube, that was to use ‘out centre;’ instead of:
out body; >; out skel qt;
This worked as promised and the centroid of both the nodes and the ways was exported. I then used OGR2OGR to convert this to a shapefile:
ogr2ogr -nlt POINT -skipfailures McDonalds_Ireland.shp McDonalds.json
I had to rename the GeoJSON file to .json for the purposes of this conversion, the last step I undertook was to transform the shapefile from WGS84 to the Irish Transverse Mercator, EPSG 2157. This was completed using the following code in OGR:
ogr2ogr -t_srs EPSG:2157 McDonalds_1shp McDonalds_Ireland.shp
OSGeo4w is in my opinion the best place to start with all things related to open source geospatial software. Now that
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.