Mapping Drone near misses in Google Earth*
My colleague Andrew Heaton from the Civic Drone Centre set me off on a little adventure with mapping tools when he showed me a spreadsheet of airprox reports involving drones.
In my head an airprox report describes what is often called a ‘near miss’ but more accurately, the UK Airprox board describe it as this…
An Airprox is a situation in which, in the opinion of a pilot or air traffic services personnel, the distance between aircraft as well as their relative positions and speed have been such that the safety of the aircraft involved may have been compromised.
The board produce very detailed reports (all in PDF!) on all events reported to them, not just drones, and they pack that all up in a very detailed spreadsheet each year. You can also get a sheet that has all reports from 200–2016! (h/t Owen Boswarva). If you look at those sheets and you just want drone reports look for ‘UAV’. There is also a very detailed interactive map of UK Airprox locations you can look through.
But given I’m on a bit of a spreadsheet/maps thing at the moment, I thought it would be fun to see if I could get the data from the spreadsheet into Google Earth . Why? Well, why not. But I did think it would be cool to be able to fly through the flight data!
Getting started.
The Airprox spreadsheet
At first glance the data from the Airprox board looks good. The first thing to do is tidy it up a bit. The bottom twenty or so rows are reports that have yet to go to the ‘board’. So the details on location are missing. I’ve just deleted them. Each log also got latitude and longitude data which means mapping should be easy with things like Google Maps. But a look over it shows the default lat and long units are not in the format I’d expected.
This sheet uses a kind of shorthand for Northings and Eastings. These are co-ordinates based on distance from the equator — the N you can see in the Latitude — and distance to the west and east of the Greenwich Meridian line, the W and the E you can see in the Longitude. To get it to work with stuff like Google maps and other off the shelf tools it would be more useful to have it in decimal co-ordinates eg. 51.323 and -2.134.
Converting the lat and long
This turned out to be not that straight forward. Although there are plenty of resources around to convert coordinate systems, the particular notation used here tripped me up a little. A bit of digging around including a very helpful spreadsheet and guide from the Ordnance Survey and some trial and error, sorted me out with a formula I could use in a spreadsheet.
Decimal coordinates = (((secs/60)+mins)+degrees)). If the Longitude is W then -1 eg.(((secs/60)+mins)+degrees))-1So to convert 5113N 00200W to decimal Latitude =((((00/60)+13)/60)+51) = 51.21666667 Longitude =((((00/60)+00)/60)+2)*-1 = -2
Running that formula through the spreadsheet gave me a set of co-ordinates in decimal form. To test it I ran them through Google Maps.
Getting off the ground.
Google maps is great but its a bit flat. Literally. The Airprox data also contain altitude information and that seems like an important part of the data to reflect in any visualization around things that fly!. That’s why Google Earth sprang to mind.
To get data to display in Google Earth you need to create KML files. At their most basic these are pretty simple. You can add a point to a map with a simple text editor and a basic few lines like the one below. Just save it with a KML extension e.g. map.kml
Any KML files usually open in Google Earth by default and when it opens it should settle on something a bit like the shot below.
Google Earth jumps to the point defined in the KML file.
Adding some altitude to the point is pretty straight forward. The height, measured in meters is added as a third co-ordinate. You also need to set the altitudeMode of the point “which specifies a distance above the ground level, sea level, or sea floor” for the point
<?xml version="1.0" encoding="UTF-8"?> <kml xmlns="[http://earth.google.com/kml/2.0](http://earth.google.com/kml/2.0)"> <Document> <Placemark> <name>Here is the treasure</name> <Point> <coordinates> -0.1246, 51.5007, **96 ** </coordinates> <altitudeMode>relativeToGround</altitudeMode> </Point> </Placemark> </Document> </kml>
The result looks something like this.
Setting the altitudeMode and setting an altitude co-ordinate gives your point a lift.
But hold your horses! There’s a problem.
The Altitude column in the Airprox sheet is not in Meters. Its in Feet.
When it comes to distances aviation guidance mixes its unit. Take this advice from the Civil Aviation Authority’s DroneCode as an example:
Make sure you can see your drone at all times and don’t fly higher than 400 feet
Always keep your drone away from aircraft, helicopters, airports and airfields
Use your common sense and fly safely; you could be prosecuted if you don’t.
Drones fitted with cameras must not be flown within 50 metres of people, vehicles, buildings or structures, over congested areas or large gatherings such as concerts and sports events
On the ground its meters but height is in Feet! So the altitude data in our sheet will need converting. Luckily Google sheets comes to the rescue with a simple formula:
=CONVERT(A1,"ft","m") A1 = altitude in feet
Once we’ve sorted that out, we can look at creating a more complete XML file from a spreadsheet with more rows.
Creating a KML file from the spreadsheet
The process of creating a KML file from the Airprox data was threatening to become a mammoth session of cut-and-paste, typing in co-ordinates into a text editor. So anything that can automate the process would be great.
As a quick fix I got the spreadsheet to write the important bits of code using the =concatenate formula.
=CONCATENATE("<Placemark> <name>",A1,"</name><Point> <coordinates>", B1,",",C1,",",D1,"</coordinates <altitudeMode>absolute</altitudeMode> </Point> </Placemark>") Where A1 = the text you want to appear as the marker B1 = the longitude C1 = the latitude D1 = the altitude
The spreadsheet can do most of the coding for you using the =concatenate formula to build up the string (click the image to see the spreadsheet)
To finish the KML file, you select all the cells with the KML code in and then paste that into a text file with a standard text that makes up a KML header and footer.
<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="[http://earth.google.com/kml/2.0](http://earth.google.com/kml/2.0)"> <Document> paste the code from the cells here. </Document>
</kml>
Your file will look something like the code below. There’ll be a lot more of it and don’t worry about the formatting.
<?xml version="1.0" encoding="UTF-8"?> <kml xmlns="[http://earth.google.com/kml/2.0](http://earth.google.com/kml/2.0)"> <Document> <Placemark> <name>Drone</name><Point> <coordinates>-2,51.2166667,91.44</coordinates> <altitudeMode>relativeToGround</altitudeMode> </Point> </Placemark><Placemark> <name>Drone</name><Point> <coordinates>-2.0166667,51.2333333,91.44</coordinates> <altitudeMode>relativeToGround</altitudeMode> </Point> </Placemark><Placemark> <name>Unknown</name><Point> <coordinates>-2.6833333,51.55,2133.6</coordinates> <altitudeMode>relativeToGround</altitudeMode> </Point> </Placemark><Placemark> <name>Model Aircraft</name><Point> <coordinates>0.25,52.2,259.08</coordinates> <altitudeMode>relativeToGround</altitudeMode> </Point> </Placemark> </Document> </kml>
The result of the file above looks something like this.
With a simple file you can add lots of points with quite a bit of detail.
Is it floating?
When we zoom in to a point it can be hard to tell if the marker is off the ground or not especially if we have no reference point like Big Ben! Luckily you can set the KML file to draw a line between the ground and the point to make it clearer. You need to set the
<Placemark> <name>Unknown</name><Point> <coordinates>-2.6833333,51.55,2133.6</coordinates> <altitudeMode>relativeToGround</altitudeMode> **<extrude>1</extrude>**</Point> </Placemark>
The result looks a little like this:
Wrapping up, some conclusions (and an admission)
There is more that we can do here to get our KML file really working for us; getting more data onto the map; maybe a different icon. But for now we have pretty solid mapping of the points and good framework from which to explore how we can tweak the file (and maybe the spreadsheet formula) to get more complex mapping.
Working it out raised some immediate points to ponder:
- **It was an interesting exercise but it started to push the limits of a spreadsheet. **Ideally the conversion to KML (and some of the data work) would be better done with a script. But I’m trying to be a bit strict and keep any examples I try as simple as possible for people to have a go.
- **The data from the Airprox board is, erm, problematic. **The data is good but it needs a clean and some standard units wouldn’t go a miss. It could also do with some clear licensing or terms of use on the site. I could be breaking all kinds of rules just writing this up.
- The data doesn’t tell a story yet. There needs to be more data added and it needs to be seen in the context i.e the relationship to flight paths and other information.
And now the admission. I found a pretty immediate solution to this exercise in the shape of a website called Earth Point. It has a load of tools that make this whole process easier including an option to batch convert the odd lat/long notation. It also has a tool that will convert a spreadsheet into a KML file (with loads of options). The snag is that it does cost for a subscription to do batches of stuff. However Bill Clark at EarthPoint does offer free accounts for education and humanitarian use which is very nice of him.
So I used the Earthpoint tools to do a little more tweaking, with some pleasing(to me) results.
You can download the KML file and have a look yourself
Let me know what you think and if you have a go.
Thanks to Andrew Heaton for advice and helpful navigation round the quirks of all things drones and aviation.** If you have any interest in that area I can really recommend him and the work the CDC do.**
*Yes, I’m pretty sure ‘near misses’ isn’t the right word but forgive me a little link bait.