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.