Cycle to School Scheme Report

Length[EntityList[EntityClass["IrishSchools", "Secondary School"]]]

723

723 secondary schools in Ireland. We can see how they are distributed in the following visualisations.

First, here's all the schools on a map.

And here's the number of schools per county:

This is one of those things that ends up practically just being a population map. Naturally, there are more schools where there are more people.

Next, let's take a look at the distribution of the number of students in schools by county.

Looking at the above graph, it's evident that Cork and Dublin have more schools than the rest of the country, but in general most schools appear to have somewhere between 200 and 600 students. Curiously, it seems that there are more schools that take smaller numbers of students in Dublin and Cork, proportonal to other counties. I also certainly didn't expect to see quite so many schools with more than a thousand students.

Now, let's think about how many bicycles would be irreparably damaged, lost, or stolen over the course of a year. I would expect somewhere in the region of 10%.

As we found above, the number of students in secondary schools in Ireland is 362,704. If we assume 370,000 bicycles in our initial bicycle investment and 37,000 bicycles per year that need replacing, over ten years, we would need (37,000*9) + 370,000 bicycles - that works out nicely to 700,000 bicycles. That seems like a lot of bicycles to me - probably something like 50% of all bicycles currently in use in Ireland.

This is a fairly straightforward query to Open Street Maps. We can use Overpass, a query language for Open Street Maps - however, we might change this to use SPARQL via Sophox instead, since the Linked Data nature of SPARQL is appealing for this project, and SPARQL is a slightly more readable and expressive language.

Here is a simple Overpass query to get all bike shops:

[out:json][bbox:{{bbox}}][timeout:25];
(
  node["shop"="bicycle"];
  area["shop"="bicycle"];
);
out body;
>;
out skel qt;

This turns out to only be 213 bike shops listed in Open Street Maps. That sounds low to me, but it doesn't seem like it's a million miles away from what I would expect. This is partially to do with using community-driven data sources like Open Street Maps - the data is unlikely to be completely comprehensive.

Anyway, we want something slightly more useful - bike shops within a certain distance to school buildings.

[out:json][bbox:{{bbox}}][timeout:30];

(
  node[building=school];
  way[building=school];
  rel[building=school];
)->.schools;

(
  node[shop=bicycle];
  way[shop=bicycle];
  rel[shop=bicycle];
)->.bikeshops;

(
  node.bikeshops(around.schools:10000);
  way.bikeshops(around.schools:10000);
  rel.bikeshops(around.schools:10000);
)->.bikeShopsNearSchools;

.bikeShopsNearSchools;

out geom meta;

The SPARQL version of this query is the following:

SELECT ?bikeshops ?schools ?distance WHERE {
  ?schools osmt:building "school" ;
           osmm:loc ?schoolsLoc.
  ?bikeshops osmt:shop "bicycle" ;
             osmm:loc ?bikeshopsLoc.

  BIND(geof:distance(?bikeshopsLoc, ?schoolsLoc) as ?distance)
  FILTER(?distance < 10)
}

This returns to us the list of bike shops within ten kilometres of a school. In fact, this is 213 bike shops, meaning none of the bike shops on Open Street Maps in Ireland is more than 10 kilometres from a school.

The bike shop closest to a school is The Cycle Shop in Dundalk, 29 metres away from Coláiste Rís, right next door.

Let's flip it and find the total number of schools with bike shops no less than 5km away. If we change the SELECT statement above to only ask for DISTINCT ?schools, and change the FILTER to 5 rather than 10, we get 1,556 schools (of a total of 4,118 schools in the Open Street Maps data for the island of Ireland).

We can start out doing a Fermi calculation, which we've described elsewhere in how many bikes. Basically, we're just going to sit and think for a minute and come up with a number that sounds kind of plausible. We're doing this because it can be helpful to have a kind of "sanity check" on the numbers we come out with at the end. It's always helpful to come up with, and then write down, some expectations and hypotheses. That way you can be happy at how smart you are (if you got a reasonable estimate) or intrigued at how surprising the real data actually is (if your estimate was shockingly far off).

We know that just under a million students are in primary and secondary schools in Ireland. We can assume that practically all of those actually need to attend the school every day (remote learning notwithstanding).

If we assume that there are a million students, let's estimate that 40% of those get to school via methods that aren't cars - walking, cycling, teleportation, and so on. So then, 600,000 students travel to school via the roads. Let's say half of these go via busses in groups of about 30. That's 300,000/30: 3,000 journeys for those students taking the bus. Let's imagine the rest all carpool in groups of three: 300,000/3: 10,000 journeys for those. That's 13,000 vehicles on the roads to bring students to school every morning, and 13,000 vehicles on the roads to bring them home in the evening. That's our baseline estimate. Of course, there are plenty of holes in this strategy - for example, people two drop their kids off on their way to work, so their car would still be on the road - but it's a rough idea.

Now that we have a baseline idea, let's take a look at some data. Luckily for us (and for students), we happen to close most of our schools in the summer. We can simply compare traffic levels during the summer holidays against traffic in the surrounding months. There are some pretty clear confounding factors here - for instance, families going abroad and being more mobile generally during the summer months. Still, it'll give us some interesting numbers.

Since we have a baseline guess, what we'd like to see is a drop in traffic volumes somewhere in the same order of magnitude.

Transport Information Ireland publish a lot of really high quality open data, including the raw data for every traffic counter they run since 2013 - that's over 300 traffic counters, every five minutes, for years. It's a really incredible amount of detail. In theory, we could look at traffic during the "school run" times specifically, which might lead to a better estimate. However, the datasets involved are absolutely massive - in the region of one gigabyte per day of data. Sadly, I simply don't have the bandwidth to download over a terabyte of data!

So, let's look at the daily aggregates instead. These files are a lot smaller, and they'll still have what we're really looking for. I'm going to be looking at the data for 2016, 2017, and 2018 - in 2019, their counters appear to have had a month-long outage, and for obvious reasons, 2020 and 2021 aren't suitable for a representative sample.

The data we're looking at contains the daily totals for each traffic counter across the country, and it includes the vehicle types. We're going to look only at cars.

Here's the data per year. In order to make it a little easier to see trends, I've used a 14-day moving average, meaning that every "day" is an average of the 14 surrounding days.

It's interesting to see how much less traffic there was in 2016 - in some cases about a million fewer counts.

Let's split the data per month and remove the 14-day moving average. We'll be able to see differences and similarities per month more easily this way.

Well, Storm Ophelia and the Christmas-New Year's break is certainly quite clear in these two plots, but it doesn't really make it clear whether or not car traffic counts go down during the Summer holidays. Let's look at the average per month, against the average over the three years, and see if there is a marked decrease in the Summer months.

Well, I think that's a resounding "there is not a marked decrease in the Summer months." This means we're not able to verify our estimate using this data. That's life! Still, it's incredible data and I look forward to looking at it again - it'll be really interesting to see the impact of COVID-19 on these counters, for instance.

The easiest way to get an estimate of the number of bike shops is to use Open Street Maps data, as we have done in several other research sections.

We can use a Sophox instance to query Open Street Maps data using SPARQL. A SPARQL query to find simply a list of all bike shops is the following:

SELECT (COUNT(?place) as ?count) WHERE {
  ?place osmt:shop "bicycle".
}

Our Sophox instance, described in the appendix, only covers the Island of Ireland, and so we don't need to add a bounding box or similar check.

This returns 213 rows, which indicates that there are 213 bike shops listed in Open Street Maps on the island of Ireland. That seems like quite a low number! Open Street Maps is unfortunately not an exhaustive database, as, like Wikipedia, it depends on member contributions.

A useful method when presented with a result like this is to ask: is that really a low number? If we think about it, I suppose I would expect there to be a bike shop in more or less every "big" town in Ireland, and an extra few dozen for Dublin, Galway, Limerick, and Cork. If we say 50 bike shops in those larger cities, then we're left with 163 bike shops for largish towns.

If we take a look at the CSO's population data for towns, taken from the 2016 Census (table E2052), we can see how many towns might be described as "big" towns. There are 873 towns in the dataset. Let's say that we think any town of 5,000 people or more will have a bike shop in it.

ds = SemanticImport["./Downloads/E2052.csv"][[;; -2]] (* the last row is the total, so we skip it*)

ds[Select[#"VALUE" > 5000 &] /* Length] (*86*)
ds[Select[#"VALUE" > 2500 &] /* Length] (*139*)

Apparently, there are 86 towns with more than 5,000 people in them. If we lower our threshold to 2,500 people, we get 139 towns. I live in a town of 2,700 people, and it's got a bike shop, so I'm going to assume that's the average (this method is called #datascience).

Here's a histogram of town populations in Ireland. Here I've restricted the range to populations of less than ten thousand people, because there are only 46 places with populations greater than that (as opposed to 827 with smaller populations), and they make it hard to see detail in the chart.

(If I wanted to include Dublin in that chart, it would be so far over to the right that you couldn't see any of the rest of the data. The difference in order of magnitude between Dublin's population and practically anywhere else in Ireland is hard to comprehend.)

Having looked at that town population data, I suppose I no longer think that 213 bike shops is a really low number. It's possible that it's still way off, but I'm fairly confident that the maximum number of bike shops in Ireland is somewhere less than 500.

One of the most interesting and most wide-ranging datasets on data.gov.ie is the planning permission dataset. Nearly all planning permission applications for the last several years are included in this dataset, including short descriptions of the work, when the application was put in, where the work was set to be done, and so on. It's a really valuable dataset. We can even use it here!

We can start by simply looking for "bike" or "bicycle" in the dataset and see how often these words are mentioned, and when. There are nearly 3,000 mentions of those words in the dataset!

We can see that the mentions of our keywords increases a fair bit from 2010, particularly increasing in 2013 and 2014. It looks like it may have been impacted a little by the late 2000s financial crisis. It's also useful to remember that the Cycle to Work scheme was started in 2009, so some more focus will have been put into bike parking at workplaces and so on from then.

We can also look at this data geographically, showing where the most mentions of these keywords are:

Since that's a bit crowded, here it is in a geo-histogram. Each hexagon is lighter-coloured if it has fewer mentions, and darker coloured if it has more.

Now onto the really cool stuff - mining the descriptions of work to be carried out for more detailed knowledge. Since there are almost 3,000 references to "bike" or "bicycle", it isn't really feasible to read through every single one of those to determine whether a bike shop is being built.

One useful method here is to use n-grams (sometimes written engrams). An n-gram is a sequence of words in a corpus. For example, the most common 2-gram (or bigram) in the Wikipedia page summary for "N-grams" is {an, ngram}. The most common 3-gram, or trigram, in the whole Wikipedia page for "recursion" is {in, terms, of} with 7 instances.

Now, let's look at the most common n-grams in the planning permission dataset, where one of the grams is "bicycle" or "bike". I've changed all mentions to "bicycle" to "bike" in the dataset in order to count duplicates together ({no, bike, parking} and {no, bicycle, parking} mean the same thing, after all).

Well, that's fairly clear, but just for the sake of being able to use a word cloud: here's a word cloud that really shows the common theme in these n-grams.

Parking. It seems like the vast, vast majority of bicycle related infrastructure being built is bike parking. Sadly, it's not clear that we found any references to bike shops at all - mostly, we saw reference to bike parking. It seems that a large number of new developments include bike parking - a good sign, although it's not what we're looking for.