But Sir, when will I ever use cosine?

The backstory...

I can't remember if anyone ever asked what the point of trig is in my maths lessons. This is possibly because it was so long ago, but mainly because I was a swot and was glad to be finally unleashing the power of my scientific calculator and was getting on with some maths.[1]

Other than thinking about trig when watching the Jeremy Brett adaptation of the Adventure of the Musgrave Ritual (spoiler alert) [2], I don't really think I've actually used it since school. So, when my passion for planning flared up and I started thinking in some depth about what was actually around me, I hadn't expected to fall into a rabbit warren of maths.

Last Saturday afternoon, having returned from a planning consultation on the News International site, and whilst my wife was asleep on the sofa, I did what any man would do. I submitted an FOI request to my local council LBTH, asking for details of how many residential properties were in the borough, which postcodes they were in, and which council tax band they fell into.

The quest... (or the boring bit)

I had been tinkering on google fusion with creating maps from data tables, and I had it in my head to use the fruits of my FOI request to plot this information. My aim was to create a map with circles (whose area related to the number of properties) centred on each postcode in the area. However, I discovered a number of issues:

1. LBTH likes to supply data in PDF format, requiring you to copy and paste from the pdf and then reformat the data into a table.
2. LBTH like to keep you on your toes by leaving out postcodes - fortunately I noticed that my previous two postcodes were not included and went back to them.[3]
3. Google Fusion does not readily accept postcodes without additional information such as the city and country associated with them, much preferring longitude and latitude to be supplied.
4.  KML, the XML type language that is widespread for geographic data (and supported by Google Fusion) does not have a function to draw circles.
5. I cannot code.

Points 1&2 were easy. 3, was ok - I initially downloaded a dataset from the Ordnance Survey that only had them based on OS National Grid references, a few google searches later found a link to the data with longitude and latitude. I could have applied a transformation onto the OS northings and eastings to convert them, but that sounded a bit too dull. 4. Was a bugger, and 5 is a fundamental failing of someone who wants to manipulate and present data on the web.

I'll go into the detail of the mechanics in a later post, but basically I needed to approximate a circle by drawing an icosagon, or 20 sided polygon, which required me to work out where the vertices would need to be relative to the position of the postcode and what this would be in longitude and latitude. Here comes the trig, what it took me a long time to realise was that a) trig functions on computers don't do degrees, they do radians b) once you've worked where the relative point is in radians, you need to convert it back to degrees and c) you need to plot vertices anti-clockwise in KML. All of which is simple, until you have to work it all out by trial and error, and frustration why nothing appears on your map, except a big close up of the Indian Ocean.

Eventually I did work it out and manipulated the data in Excel, as I'm not quite ready to start using google's API's, writing my own javascript or any such nonsense.

The end result (sort of)

So, the finished product. At the moment it includes only postcodes starting E1 in Tower Hamlets... , so includes E1, E1W and E14. This will be amended - this intial set of postcodes was done as a quick filter to makesure my formulae were correct in Excel before risking trying to geocode a failed attempt again (one failed attempt, when I forgot to adjust part of my formulae from radians back to degrees took two hours to geocode a relatively small number of points). Once it worked, there is no geocoding, it just does it.

At the moment, a property is assumed to be 64 square metres in area (I guessed that a modest two bed property with lounge-kitchen would fit in an 8x8 floorplan, though on subsequent checking the latest development in Wapping, 21 Wapping Lane states that their 'typical' two bed flat is 80 square metres, whereas a one bed is 48 square metres), such that the area of the circle is 64*number of properties. The next iteration will hopefully also map the relative proportion of properties by council tax band using concentric rings!

It surprised me that the circles weren't overlapping left, right and centre, particularly in residential areas, which suggests that there is more space (even if it is just a carpark or communal garden) than I might have expected. However, this may be an issue with my assumption on land usage. BTW the really big circle in the North East is the new Nido halls of residence in Spitalfields

Coming up next...

• The same map, updated for all LBTH postcodes and the composition of bands 
• A map showing overall density of different areas of Wapping and neighbouring boroughs
• An analysis of vacant properties in the area
   

Notes

[1] I had a misspent youth.

[2] Alas the last time I think I read the story was pretty much when I should have been studying for my GCSE maths - I had a very misspent youth.

[3] I should say that LBTH were very prompt and provided data on Tuesday, my wife exclamining 'they do know they've got another three and a half weeks to deal with that?' when I told her.