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!

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

  1. Hey,

    Nice post. Thanks for link­ing to the orig­i­nal data. I’m look­ing for­ward to explor­ing it.
    While you link to the data works for the part 1 post, the link above is broken.

    Regards,
    Cyrille

  2. […] 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 […]

  3. Hi Michael,

    Thank you to post this kind of tuto­ri­als.
    I’m just start­ing with the whole GIS thing, so as a new­bie I have some ques­tions:
    Can this be done using QGis?
    if yes, Could you please take the time to make a ver­sion of this tuto­r­ial using QGis?

    It is valid to use just some of the flights? for exam­ple, if I’m only inter­ested in the flights from Venezuela to Cuba, how do I can use only that data to make the map?

    Thanks in advance and sorry for bother you with this new­bie questions.

    Many

  4. Hi Michael,

    Thanks for sharing.

    Chris

  5. Hi Michael,

    I really love this visu­al­i­sa­tion. It is pos­si­ble you could post a much higher res­o­lu­tion ver­sion of it as I would love to print it out on a really big piece of paper (to stick on my wall for per­sonal use).

    Thanks
    –Mike

  6. Michael,

    This is beau­ti­ful! Thanks so much for shar­ing your method­ol­ogy and giv­ing folks the oppor­tu­nity to explore the tools.

    I agree with “mike” above. Can you please post a link to down­load a higher res­o­lu­tion photo — I would LOVE to hang this on a wall, what a fan­tas­tic con­ver­sa­tion piece!

    Best,
    Rachel

  7. Hi Michael,

    Absolutely stun­ning images… Like many of the peo­ple who have posted before me, I would love to print this as a can­vas in my house, would you be able to upload a high res­o­lu­tion version?

    Thanks

  8. […] 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 […]

  9. […] 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 […]

  10. […] 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 […]

  11. No ESRI s/w required.
    http://en.wikipedia.org/wiki/Well-known_text
    Add an index col­umn for the rows.
    exam­ple w/ lon,lat point pairs
    row 1: oid;line
    rows 2 through n: =CONCATENATE($J2,”;”,“LINESTRING(“,$L2,” “,$K2,”, “,$N2,” “,$M2,”)”)
    import into qgis “Cre­ate a Layer from a Delim­ited Text File” (WKT pull-down, line as WKT field)

  12. oid;line
    1;LINESTRING(39.956589 43.449928, 37.906111 55.408611)
    2;LINESTRING(48.006278 46.283333, 30.262503 59.800292)
    3;LINESTRING(61.503333 55.305836, 37.906111 55.408611)

  13. […] 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 […]

  14. michael,

    thanks for the tuto­r­ial! when you’re merg­ing the xy files, I don’t seem to have Join on the list of Merge Rules for Shape Length. Should I change the Field Type to Text?

    Thanks!

    • Hey divesh,

      What ver­sion of ArcMap are you using? Also, can you try using Merge with­out any join rules? I have a feel­ing it will still work.

  15. Can you share what sym­bol­ogy set­tings you used in ArcGIS to get the final effect? Thanks

  16. Hi Michael,

    Any chance you could send me a high res­o­lu­tion ver­sion of this map? Would love to put it on my wall.

    Thanks,

    Paul

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

  18. You can actu­ally play around with these routes and explore direct flights between coun­tries, air­ports, and the world on this inter­ac­tive map

  19. […] 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 […]

  20. It would be totally cool to dis­play this same data in a Fuller pro­jec­tion (Dymax­ion Map):
    http://basementgeographer.com/wp-content/uploads/2013/05/uapoK.jpg

  21. Thanks so much for all of this, it’s great. Is there any­way to do this in ArcMap 9.2?

  22. Heya,

    Wow! This is exactly what i am look­ing for. Is there a way to export the data into vec­tor infor­ma­tion for design soft­ware such as Adobe Illus­tra­tor? Say EPS, vec­tor PDF or .ai for­mats? I want to use this in a design but want to play with the colours and need to know if it is worth tak­ing the plunge and fig­ur­ing out ArcGIS etc.

    If you can export this data, instead of me recre­at­ing it as I am more inter­ested in the vec­tor rather then learn­ing GIS, maybe you could send me these files? I would be very thankful!

    Thanks!

    Joe

  23. You need to be able to touch the ground with
    the balls of your feet. A bike’s engine and motor­cy­cle exhaust pipes caan get seri­ously hot
    and that can seri­ously wound rid­ers. Danny Eslick takes
    the llead in thhe final lap at Roadd Atlanta.

    Feel free to surf to my web­sie ????? ???? ?? ??????? ??????? ?????????

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>