You can now pur­chase a copy: here!

I guess we can call this part two of the global con­nec­tiv­ity by flight exper­i­ment, which I have been work­ing on the past cou­ple of days. Once again, the data comes from openflights.org/data.html and is avail­able for free to the pub­lic. It is a very detailed set of data that requires just a lit­tle bit of mas­sag­ing to be usable in a desk­top work envi­ron­ment. In this blog post I would like to take you through some quick help­ful tips that should get you from down­load­ing the data and clean­ing it up to map­ping it in ArcMap. In all hon­esty, I would have loved to com­pleted this process in entirely open source appli­ca­tions but I had to resort to ArcMap’s “XY to Line” tool. I wish I could have repli­cated the result I’ve achieved using QGIS but alas, I am more famil­iar with ArcMap’s geo­pro­cess­ing capa­bil­i­ties and so I fall back to my stu­dent licensed Arc prod­uct when I need to.

Fifty-eight thousand, two-hundred and eighty-eight flight routes

Yup! 58,288 flight routes… 1 Earth. If that’s not an impres­sive num­ber I’m not sure what is! The num­ber is almost as impres­sive as the map cre­ated by con­nect­ing all of the flight ori­gins and destinations:

mapping out 58288 flight routes

With­out a basemap of the con­ti­nents, we can clearly make out the shape of many major regions of the world. Also note that we can iden­tify many unique spa­tial pat­terns of set­tle­ment and urban­iza­tion. Flights that leave or arrive in South Amer­ica, Africa or Aus­tralia gen­er­ally tend to fly to/from the coast. This is very reflec­tive of the set­tle­ment pat­terns in these regions. The United States has flights arriv­ing and depart­ing all over the coun­try, where as Canada has most flights located along the south­ern Canada-USA bor­der (with a few con­ti­nen­tal flights trav­el­ling into the North). We can see that Aus­tralia has major urban cen­tres spread out over its large land mass and that con­ti­nen­tal flights are very pop­u­lar in Aus­tralia, con­nect­ing almost every coastal region to each other with very few flights land­ing in cen­tral Aus­tralia (what would be the point, right?).

We could talk about how flight routes reflect the set­tle­ment pat­terns all day but then I would never get to show­ing you how to make a map sim­i­lar to this!

I will assume you have the fol­low­ing two appli­ca­tions installed on your computer:

  • ArcMap 9.3x+ or 10.x+
  • OpenOffice.org (OOo) or your favourite spread­sheet editor

Let's get started!

  • down­load both airports.dat and routes.dat from openflights.org/data.html
  • change the exten­sion from ‘.dat’ to ‘.csv’ on both files
  • cre­ate a new spread­sheet in OOo with two sheets/tabs
  • open airports.csv, copy and paste the data into sheet 1
  • re-name sheet 1 to airports
  • open routes.csv and paste the data into sheet 2
  • re-name sheet 2 to routes

Now to clean up the data:

  • in sheet.airports: delete all columns BUT those con­tain­ing the 3-letter IATA/FAA codes or 4-letter ICAO codes & lat­i­tude and lon­gi­tude (columns 5–8)
  • in sheet.routes: delete all columns BUT those con­tain­ing ori­gin and des­ti­na­tion air­port codes (columns 3 and 5)
  • insert a new row above all exist­ing data and input appro­pri­ate field names. ie sheet.airports{IATA_FAA, ICAO, Lat, Long}, sheet.routes{Orig, Dest}

Querying Route Origin and Destination XY Coordinates

What we are going to do is query sheet.airports from sheet.routes to obtain lat­i­tude and lon­gi­tude val­ues for each ori­gin and des­ti­na­tion of every route (wow, mouthful).

Working in sheet.routes:

  • cre­ate 4 col­umn field names {Orig_Lat, Orig_Long, Dest_Lat, Dest_Long} *should be cells C1:F:1*
  • in cell C2, insert func­tion: =VLOOKUP($A2;airports.$A$2:$D$6630;3;0)
  • fill’ the equa­tion down the table by double-clicking in the bot­tom right hand of the cell
  • in cell D2, insert func­tion: =VLOOKUP($A2;airports.$A$2:$D$6630;4;0)
  • fill’ the equa­tion down
  • in cell E2, insert func­tion: =VLOOKUP($B2;airports.$A$2:$D$6630;3;0)
  • fill’ the equa­tion down
  • in cell F2, insert func­tion: =VLOOKUP($B2;airports.$A$2:$D$6630;4;0)
  • fill’ the equa­tion down

The func­tion VLOOKUP, allows us to ‘lookup’ cell val­ues in arrays located inside your spread­sheet. In words =VLOOKUP($A2;aiports.$A$2:$D$6630;3;0) means, iden­tify the value in cell A2 and locate it in sheet.aiports within the array A2:D6630. If found, the expres­sion returns the value located in the 3rd col­umn (col­umn C; where col­umn 1 = A; the first col­umn in your array). What we are attempt­ing to do is locate the route ori­gin code in A2 and find it in sheet.aiports array A2:D6630. If it finds a match, it will take the value located in the same row albeit the 3rd col­umn over as the expres­sions value. A slight mod­i­fi­ca­tion to the func­tion is needed to cal­cu­late the lat­i­tude of route ori­gins and lat/long of route destinations.

More info on VLOOKUP here.

Note:

There a few things we must do to com­bat a few issues that may arise later on when we begin geo­pro­cess­ing. There are approx 500 entries which returned no lat/long val­ues for route ori­gin, des­ti­na­tion or both. These need to be deleted before geo­pro­cess­ing so go ahead and sort your columns numer­i­cally, find those #N/A entries and delete the entire row. Also, we will have some dif­fi­culty geo­pro­cess­ing 58K entries at once, so its best we split up our table into 6 sep­a­rate tables. I split my table up into 10,000 entry chunks. Make sure when you do this that you don’t leave the field head­ings (row 1) out by acci­dent on each sub­se­quent table! Sav­ing the tables as .csv allows us to open them up in ArcMap and per­form geo­pro­cess­ing oper­a­tions on them.

In ArcMap:

  • cre­ate a new file­ge­o­data­base and name it appro­pri­ately (I’ll call it openflights)
  • add all 6 tables to a new project
  • search for the tool “XY to Line”, located in the data man­age­ment tool­box, open it up and fill it out like so:
    • input table > table 1
    • out­put file > ‘openflights.gdb/routes_1’
    • Start X > Orig_Long
    • Start Y > Orig_Lat
    • End X > Dest_Long
    • End Y > Dest_Lat
    • Method > Great Circle
  • Rinse and repeat for all 6 tables
  • Using the ‘Merge’ tool located in the analy­sis tool­box, merge all 6 route poly­line shapefiles
    • input files > routes_1 to routes_6
    • field map > right-click on ‘shape length’ > merge rule > join
    • out­put file > ‘openflights.gdb/routes_merged’

With all that said and done, you should have a flight route net­work of 58 thou­sand poly­lines! Fid­dle around with the sym­bol­ogy and have some fun pro­duc­ing an eye-catching map!

Cheers!
You can now pur­chase a copy: here!

 
 
If this post helped you and you enjoy my site I would hap­pily accept Lite­coin dona­tions:
 
LKPfT772e9HxvXYcA8LVDctTmENoqQxQF3
 

Thank you!

  10 Responses to “Global Connectivity Revisited; mapping out 58,288 flight routes”

  1. […] out the update to this post here! Related Posts!4 Sep ’11 — Global Con­nec­tiv­ity Revis­ited; map­ping out 58,288 flight […]

  2. […] Either that or a glow­ing Lite-Brite box. But instead of a toy the whole fam­ily can enjoy, this map shows which inter­na­tional cities will be destroyed in the com­ing zombie […]

  3. […] from the Open­Flights air­port and air­line route data­bases in Sep­tem­ber 2011 for his per­sonal blog at http://www.spatialanalysis.ca/2011/global-connectivity-mapping-out-flight-routes/. On his part­time posi­tion with global trans­porta­tion plan­ning and engi­neer­ing firm Arup’s […]

  4. […] of this infor­ma­tion and turned it into the these fas­ci­nat­ing images, you can check out his blog: Spa­tial Analy­sis. Michael has also crunched the num­bers in a vari­ety of other areas and spit them out into something […]

  5. […] the land masses.  More Images can be found at BBC News or visit Michael’s site at spatialanalysis.ca instruc­tions on cre­at­ing your own […]

  6. […] Global Con­nec­tiv­ity Revis­ited: map­ping out 58,288 flight routes by Michael Marki­eta aka (@MichaelMarkieta) […]

  7. […] Air Travel Routes: Fre­quency in Pop­u­lar Air Travel Routes: 58,288 Flight Routes: Spa­tial Analy­sis Global Con­nec­tiv­ity Revis­ited; map­ping out 58,288 flight routes | Spa­tial Analy­sis A Spa­tial Analy­sis Map Show­ing All of the Flight Ori­gins and Des­ti­na­tions. This phe­nom­e­nal map […]

  8. […] that seem remote in the daily news are much less so on a map of global flight paths. Parts of the world that feel dis­con­nected from us — by pol­i­tics, by war, by sheer distance — […]

Sorry, the comment form is closed at this time.