How to use the KML file

You can download the 2012 KML file here:
http://www.mediafire.com/?j1b3i4pcv37d7cj

The Errors file is here:
http://www.mediafire.com/view/?dr3wf27j6safe7o
(though the format is subject to platform)

Once you have the KML file opened in Google Earth you can select the month or months you want - by default it will have the month selected that it was created in.
Then you can adjust the time slider on the main map page to suit your tastes, I would suggest having the left slider all the way to the left, and the right slider all the way to the right - this means that the whole time period for the selected month(s) is being shown.

The next bit is dependant on what you want to use the utility for - but basically you can see which aircraft of the BBMF will be roughly where - obviously their schedule is subject to change at short notice and the flight paths I have deduced have a wide range of error due to a whole range of factors.

Clicking on the name of a particular aircraft brings up it's picture (well it did before the BBMF changed some of them)
Clicking on the title of the KML entry in the places section brings up some info and the warning 'Please bear in mind that flights can be changed or cancelled at short notice' as well as including a link to the BBMF website.

If you select a certain aircraft and then click on the tour button:

You get a virtual tour of what the aircraft will see - pretty neat! :-)

Possible uses include;
Confirming which aircraft it was you have already seen on a particular day,
If you are in a certain location what aircraft you may see,
It will give you an idea of just how busy the BBMF really are,
If you are planning a holiday then you can get an idea of where you might want to visit on certain days.
If you are planning on sitting out in the garden you can check to see if anything is likely to be in the area.

And I'm sure there are other uses that people can find for this interpretation of the published BBMF data.

Spreadsheet processing

The actual spreadsheet processing is rather tedious.

I create a temporary sheet and populate it with all the entries from each sheet, each line of the temporary sheet is amended to make sure there is a date, either already there or inherited from the last known date.

For missing take-off and landing times I have had to make assumptions, so I tend to start at 9am, and land at 5pm since that seems to be their normal day. I suspect that isn't right in all cases, but hopefully good enough.
The really frustrating bit is the fact that Google Earth needs to have co-ordinates to work with, and the entries in the spreadsheet can be pretty varied in their spelling and/ or existence - there have been many places that I just haven't been able to find, which is something I can't really do anything about.

The way the spreadsheet lists all the planes means that I can keep track of which spitfire (for instance) is doing what. The Lanc, Hurricane and Dakota are less of an issue since they are singular.

Anyway for each location I try and find the most likely location in Google Earth and then copy the eastings and northings to a worksheet that contains the name and location of each place in the spreadsheets.
This is a time-consuming task, especially since spelling mistakes are rife - there are 9 different spellings and abbreviations for 'Coningsby' and they are based there!
There are so far 849 locations - though of course some are duplicates.

For the actual 'programming' for ease of 'portability' I decided to do all this in an Excel macro since I wasn't too sure how things were going to develop and didn't want whoever took this thing over having to install ASP/Perl/Python or whatever else might be other valid ways of doing things,

Essentially the Excel macro creates a file and fills it with the standard information (declaration, styles etc.) and the for each plane/month it loops through a filtered copy of each sheet and populates two strings that hold the date/times and co-ordinates respectively.
Once each plane/month is outputted to the file it moves onto the next plane in the month.

The use of the temporary sheet means that I have one source to deal with, which makes the eventual output a lot easier, though of course at the end of the process I delete the sheet.

All of this worked well enough and produced a KML file that I felt was rather useful when opened in Google Earth.

I also took the opportunity to list out all the errors that I could find - missing dates, times, un-found places etc. and produce an errors file - the original intention was to feed this back to the BBMF so that they could improve their data, but it also came in handy in error checking as I went along.
The error file has loads of the following data:

Take off but no time - Sheet: Apr, Line: 39. 
Venue but no slot time - Sheet: Apr, Line: 39. 
Landing but no time - Sheet: Apr, Line: 39. 
Venue but no slot time - Sheet: Apr, Line: 42. 
A/C mentioned but not specified - Sheet: Apr, Line: 42. 
Take off but no time - Sheet: May, Line: 5. 
Venue but no slot time - Sheet: May, Line: 5. 
Landing but no time - Sheet: May, Line: 5. 

Most of the output is expected - mainly due to blank lies or red text - which I take to be unconfirmed events.

Spreadsheet and KML format

Ok, so I made a start on sorting out the raw data into something useful. The spreadsheet can be seen in the following screenshot:

Each month has it's own sheet and the columns are, from the left:
Day, Date, Take-Off Airfield, TO Time, Transit time, Venue, <aircraft selection>,<aircraft summary>, pilot, display/Flypast/Taxi(?)/Park(?), Slot time, duration, transit time, landing airfield, landing time .. etc.

Obviously some fields are more useful than others, but using some thought I was able to find a use for most fields.

For my purposes I decided to split activities into 3 distinct types:
Take-offs - identified by having a take-off airfield entry, date and time
Transits  - identified by not necessarily having a take-off or landing airfield entry, but a venue,
Landing - identified by having a landing airfield entry and time

I needed to adjust these somewhat due to the number of missing bits of data - some it deliberate (for instance transits and landing tended to have no date, so had to inherit from the entry above) and some of it accidental, such as the entry on line 44 that only has a Take off airfield and venue.

That's the spreadsheet side of things - the KML file is something else. Looking at the API (https://developers.google.com/kml/documentation/kmlreference) the basic structure of a KML file is as follows:
<declaration>
<styles>
<folder>
  <Placemark>
    <gx:Track>
      <when></when>
      ......
      <gx:coord></gx:coord>
      .....

    <gx:Track>

  </Placemark>

  <Placemark>
  .......

<folder>

Each <folder> holds a month of data, and each <placemark> holds a particular aircrafts' movements. When Google Earth is displaying these each can be (de)selected by the user:

The black bar in the Google Earth window is the time-frame selector which can be adjusted to show whatever start and end date/times the user wants.

The important bit in the KML file is the gx:track section that maps a date/time to a co-ordinate - an example is below:

<gx:Track>

<tessellate>1</tessellate>

<altitude>1000</altitude>

<altitudeMode>clampToGround</altitudeMode>

<when>2012-04-19T10:05:00Z</when><!-- TO CGY -->

<when>2012-04-19T12:10:00Z</when><!-- Land CGY -->

<when>2012-04-20T09:00:00Z</when><!-- TO CGY -->

<when>2012-04-20T17:00:00Z</when><!-- Land CGY -->

<when>2012-04-24T09:00:00Z</when><!-- TO CGY -->

<when>2012-04-24T17:00:00Z</when><!-- Land CGY -->

<when>2012-04-26T09:00:00Z</when><!-- TO CGY -->

<when>2012-04-26T17:00:00Z</when><!-- Land CGY -->

<gx:coord>-0.172764 53.091834 1000</gx:coord><!-- CGY -->

<gx:coord>-0.172764 53.091834 1000</gx:coord><!-- CGY -->

<gx:coord>-0.172764 53.091834 1000</gx:coord><!-- CGY -->

<gx:coord>-0.172764 53.091834 1000</gx:coord><!-- CGY -->

<gx:coord>-0.172764 53.091834 1000</gx:coord><!-- CGY -->

<gx:coord>-0.172764 53.091834 1000</gx:coord><!-- CGY -->

<gx:coord>-0.172764 53.091834 1000</gx:coord><!-- CGY -->

<gx:coord>-0.172764 53.091834 1000</gx:coord><!-- CGY -->

</gx:Track>

This shows an aircraft which is shown to be taking off and landing at Coningsby - since I haven't bothered with any sort of holding pattern for these instances it will just be a dot on a map - not interesting. :-) If I pursue this I will probably have to fudge the start and end co-ordinates to each end of the airfield to get something visible.

Needless to say the number of date/time points needs to match the number of co-ordinate points, which gets to be a problem when times are missed from the spreadsheet, or need to be inferred from a take off times and various transit times.

Brief intro.

Too late tonight to do anything else than get started.

Over a year ago I saw a Hurricane fly over north Watford - needless to say everyone else thought it was a Spitfire, but the wings were too thick and 'stubby' so we had a discussion about Vulcans, SR71 and other things instead.

When I got home I decided to look on the Battle of Britain Memorial flights website (http://www.raf.mod.uk/bbmf/) to see if I could find out the answer - after emailling off a question, downloading the years flying schedule spreadsheet and poring over the entries I got a reply to confirm that it was a Hurricane.

However the spreadsheet got me thinking and I started to think of some way of making use of the published information to answer this sort of question in the future.

I found myself looking at KML files which allow Google Earth (http://www.google.com/earth/index.html) to display a range of information. After a lot of reading of the KML API and messing aroud with excel I managed to produce a working KML file (http://www.mediafire.com/?pwrk5c9sxeod688) which was enough to show that it could be done.

After a very small amount of polishing I decided to contact the BBMF and ask if they were interested in what I was doing.

After a fair bit of too-ing and fro-ing I thought I had got the idea across.

Ok - that's the start, hopefully tomorrow I can pad out the rest.

ttfn