Global Connectivity Revisited; mapping out 58,288 flight routes

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:

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 3^rd 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 3^rd 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!