I thought, mostly because I wanted to play, I would put together my first Tableau Public work book showing the distances each of the Primary Schools in my area took. We went through this process last year with our eldest and as distances have contracted so much wanted to see what it looked like. As can be seen from the map below, the distances crashed this year:

I filtered out the school who took all applications offered, only looking at those who were fully subscribed. One thing I intend to expand later into the data is Ofsted rating as a colouring to see how the distance varies with it. I would also like to add labels onto the chart for the schools. The visualisation itself is at https://public.tableausoftware.com/profile/james.dunkerley#!/vizhome/BromleyPrimarySchools/Dashboard1.

Putting the Visualisation Together

As always when working with data the first and most tedious part was getting the data into a friendly form. We had the PDF files from the last 2 years as well as the one available on Bromley’s website. Each years file contains the last three years distances and the previous years number of applications. So a little shredding and tidying up within Excel,  to get a table which looks like:

While this was tedious, nothing particularly complicated in doing this. A little tidying up of data to produce the grid, and then I thought would be straight forward to bring into Tableau. Copying the data into Tableau as normal, produced a reasonable set of dimensions and measures. It recognized that there was a postcode field and generated Latitude and Longitude fields. Unfortunately when I looked at the values on a small sample set, I found they didn’t have geographic locations:

At bit of a Google around turned up a blog post from the guys at The Information Lab – Hopefully the last word on UK postcode mapping in tableau. I download the TDE file the have of all postcode date and then loaded the postcodes of the schools into the same Tableau and joined the two together (I need to remove the space in <4> <3> postcodes) using Tableau data blending to generate a set of Longitudes and Latitudes for each postcode. Back into Excel and the old faithful VLookUp to join the postcodes back to the school. I then reshaped the data so that it stored by year, removing any entry in the distance column which from a church school (as they don’t publish distances) and setting any school which had All Applications Offered to be a 10 mile radius (the maximum of any school which was not All Applications Offered was 5.26 miles).

The one additional difficulty in creating the visualisation is that I want the circles on the map to be a fixed geographic size and overlapping. Each circle would represent the area where applications were accepted for a school in a specific year. While it trivial to get Tableau to plot the data on a map and to show points of varying size, I couldn’t see how to get the points to be a fixed geographic size. I could imagine this might be added in a later version of Tableau but isn’t there at the moment.

More searching in the Tableau forum turned up a post on Concentric Circles. In simple terms instead of plotting individual point you plot a polygon on the map. This means repeating the data for each point and then using a formula to produce a latitude and longitude for the point. We have the centre and the radius (I converted it to kilometres as the formulae in the example are in kilometres). I chose to repeat each point 360 times and a number from to 1 to 360 which I could use to create the points. The formulae I used are:

Circle Latitude:
    DEGREES(ASIN(
        SIN(RADIANS([Latitude])) * COS(([Distance KM])/6371) +
        COS(RADIANS([Latitude])) * SIN(([Distance KM])/6371) *
            COS(RADIANS([Point]))
    ))
Circle Longitude:
    DEGREES(
        RADIANS( [Longitude] ) +
        ATAN2(
            COS(([Distance KM])/6371)-SIN(RADIANS( [Latitude] ))*
                SIN(RADIANS( [Circle Latitude] )),
            SIN(RADIANS([Point]))*SIN(([Distance KM])/6371)*
                COS(RADIANS( [Latitude] ))
    ))-90

Finally a little tweaking in Tabelau. Tell it to plot a Polygon with Point as the Path and the Circle Longitude as Longitude and Circle Latitude as Latitude. Applying the Year as the page and the Name of the school as the colour. A little formatting tweak in the Color settings – transpanency down to 40% and adding a border to make it look nicer.