Skip to main content

How to geocode and visualise flight paths with Google Fusion Tables

My dad worked as a travel agent and lived through the early days of commercial tour operating. Since 1958 he logged all his flights with a flight log book. A couple of years ago he retired and he recently found the time to transfer his handwritten records into an nearly 2000-row large excel table. My aim was to visualize that data but given my limited programming skills, I was looking for ways to convert destinations into geo-location data (longitude + latitude) and connect the two departure and arrival positions on the map with a line. KML seemed to be the easiest data format that could do the job.

The following describes steps on how to turn flight log entries from an Excel or Google spreadsheet into a geocoded kml file that you can load into Google Earth or display on a custom Google Map.

1. Import to Google Spreadsheets

My dad recorded his flights with a lot of detail (date, departure and arrival airports, distance flown, airborne time, aircraft make and model, type (prop or jet), airline but of course all you need to map your flights is departure and arrival locations (and a date if you intend to order them chronologically). If you’re working with Excel or Open Office, save as .xls, upload to Google Drive and make sure it’s converted to a Google Spreadsheet.

2. Clean Up

Assuming that you collected your data by hand as my dad did, you got to do some clean up in order to process your data without problems later on. That means removing eventual empty spaces before after destination names with the TRIM() formula and making sure that all cells such as dates, distances are properly formatted. Use the filter to check for typos in your location names. It works best if you consistently write the English name of locations.

3. Geocode!

Geocoding is the process of finding associated geographic coordinates for every location in your spreadsheet. Of course you can look these up manually (e.g. with mygeoposition.com) if you have just a few entries but with nearly 4,000 destinations I needed an automated solution, which I found on github. Mapbox has developed a neat geocoding script for Google Spreadsheets (worth checking out Mapbox.js as well, if you’re a pro and designing your map for online use).

Run the Geo For Google Docs script inside your Google Spreadsheet, it inserts additional columns next to your location with the equivalent longitude and latitude as well as type of location it found (e.g. city, municipality, state etc) Follow the instructions carefully and the script will do the magic for you and geocode all locations in your table (it actually stopped after about 1,000 rows and I had to re-run the script to complete all 2,000 rows)

Double check the geocoded entries. If you’ve flown to some remote reservoirs somewhere in Africa as my dad did, then you might have to double check that it geocoded the right location. Also, there are some places and cities with the same names in different countries s.a. Santa Cruz, make sure it picked up the right one.

Looking up longitude and latitude would already be enough if you only want to display points (i.e. Placemarks) on your map, but since we want to draw a line between departure and arrival locations, we need to pack our geocoded location data into a short kml string into a column at the end of our spreadsheet.

4. KML preparations

in KML, lines are defined within the LineString argument, so all we need to do is merge and wrap the long/lat coordinates into it so that we get to this format:

<LineString>
<coordinates>
8.5619,47.4504 -0.127659,51.507276
</coordinates>
</LineString>

For example, if your departure longitude is in D2, departure latitude in E2, arrival longitude in F2 and arrival latitude in G2, the formula for your column that creates your kml string is:

<LineString><coordinates>"&D2;&","&E2;&" "&F2;&","&G2;&"</coordinates></LineString>

Copy this formula down your Line column. Now all that’s left to do is convert our spreadsheet into a kml file.

5. Convert to Google Fusion Table

Create a new Fusion Table here. Load your spreadsheet and make sure your line column with the pre-arranged KML code only displays as “KML…”. Navigate to the Map tab. It will automatically start to geocode the first location column. Cancel that and navigate to the “Configure Map” section left of Map. Select your line column (the one with the “KML…” entries) in the pull down menu as “Location”. Bam! Zooming in a few times will unveil your lines.

Note: Google Fusion Tables is a great tool to geocode single locations, but since we need to look up two locations per row and draw a line between departure and arrival, we only use it as spreadsheet-to-kml converter.

6. Style your map

Now format and customize your lines and info popup accordingly. Since I was planning to display the lines on a dark background, I chose a bright gradient for the colours of the lines; the older the trip, the more yellow. Also I pulled in all the additional data from the table to the info window so that it shows greater detail of each flight s.a. airline, aircraft, distance and duration.

7. Adding additional KML parameters

If you intend to import your kml to Google Earth, you need to add a few more kml parameters to ensure the straight lines align on the Earth’s surface and don’t disappear in the oceans as GEarth will consider the shortest distance between the two points, which – for long flights – would go through the planet. Simply open up the kml file in a text editor and “find/replace” all <LineString> with:

<LineString>
<extrude>0</extrude>
<tessellate>1</tessellate>
<altitudeMode>clampToGround</altitudeMode>

8. Import to GEarth / GMaps

Right click on your kml and and select “Open in Google Earth”. Flight paths look pretty stunning in NASA’s city lights layer. Navigate to the Layers panel (Layers -> Gallery -> NASA) and activate the “Earth City Lights” layer.

Screenshot from Google Earth:

pathsonglobe

Screenshot from Google Earth with the NASA City Lights layer enabled:

earthlights

Join the discussion 2 Comments

Leave a Reply

© 2016 Raphael Faeh. All rights reserved.