We're already at the end of the first week of the 30 Day Map Challenge. The topic for day 7 is "Accessibility".
Map how people (or things) get around. Visualize travel time, barriers, inclusive design, public transport reach, or create a map that is itself highly accessible to all users.
For today's challenge, I'm going to investigate funded bus transport for secondary school children. In England the rules about which children are provided with free school travel differ from area to area depending on which council area you live in. For example in West Sussex, to receive free transport to school, the child must attend their closest secondary school as the crow flies, regardless of whether it's their catchment school (which may reduce the chance of receiving a place when applying), and live more than a 3 mile distance from the school. This 3 mile distance is calculated as the travel distance, and includes not only road, but all public highways including footpaths and bridleways.
Therefore my Power BI report and map today are going to take a detailed look at West Sussex.
First of all I've downloaded from a Freedom of Information request, a list of all the schools in England. I've then filtered this to all the schools in West Sussex and the surrounding areas (as the closest school may not be in West Sussex).
I've added this as a reference layer on the map represented by square blocks.
For each of these schools I've then generated a 3 mile travel time isochrone based on walking routes using a local instance of the Open Source Routing Machine (OSRM). This therefore takes into account footpaths.
Again I've added this as a reference layer onto the map.
I then added another reference layer containing the boundaries of the surrounding council areas that I downloaded from the ONS geoportal. I've set a thick white border and a white fill to show which areas are outside of the West Sussex.
Next I downloaded the Open UPRN dataset from Ordnance Survey containing a precise location of every property in England. Councils have access to premium datasets that contain much more detail, including the addresses, but for this demo, the Open UPRN file is sufficient. I used a python script to filter the records to just those within West Sussex.
I then calculated the nearest school as the crow files for each property - again using a python script generated by ChatGPT.
I then calculated the walking distance from each property. With this I can then classify them as being eligible for funded school transport if they are beyond 3 miles. I also generated the actual walking route.
This property data and walking routes were added to the map as a circle layer and a GeoJSON (from data) layer, allowing them to be filtered by secondary school.
I also formatted each circle according to eligibility status, and length of walk.
I then added a layer to show all the public bus routes, to show whether there were any existing public bus services available for those children not eligible, but live a long walk from school.
Finally I set the background layer to use Ordnance Survey Open Zoomstack outdoor styling, and set the terrain to be 3D, so you can get an idea of any steep gradients involved in the journey.
Putting it all together, you can see from this screenshot that it's a complicated situation. In Rake the nearest school is Bohunt in Liphook Hampshire. Which side of the road you live on determines which council provides the school transport. A little further up the road neat Milland, and children have more than an hour's walk through potentially muddy farm fields up a steep gradient. Whilst there is a bus service, it doesn't go to the town with the school.
Here's the Power BI report:
You can download the Power BI .pbix file to see see how it was created. (Caution - this is a 100mb file).