Global Connectivity Revisited; mapping out 58,288 flight routes

Sep 042011
 

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!

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

  1. Cyrille says:
    9 Sep ’11 at 10:04 am

    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

    Reply
    • Michael says:
      9 Sep ’11 at 12:07 pm

      Thanks Cyrille!
      ~ fixed the link

    • Johna547 says:
      1 Jul ’14 at 7:47 pm

      Howdy! This arti­cle could not be writ­ten any bet­ter! Look­ing at this post reminds me of my pre­vi­ous room­mate! He con­stantly kept preach­ing about this. I’ll send this infor­ma­tion to him. Fairly cer­tain he’s going to have a good read. Thanks for shar­ing! kcbeeeaekeea

  2. Open Flights; Mapping Global Connectivity | Spatial Analysis says:
    22 Sep ’11 at 1:33 am

    […] 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. Manuel says:
    23 Jan ’12 at 8:25 pm

    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

    Reply
  4. Chris says:
    28 May ’13 at 4:59 am

    Hi Michael,

    Thanks for sharing.

    Chris

    Reply
  5. mike says:
    28 May ’13 at 12:41 pm

    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

    Reply
  6. Rachel says:
    28 May ’13 at 4:12 pm

    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

    Reply
  7. Anonymous says:
    29 May ’13 at 4:38 am

    […] […]

  8. Chris says:
    29 May ’13 at 12:51 pm

    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

    Reply
  9. World flight map reveals worst cities for a zombie outbreak | Ken Hegan says:
    29 May ’13 at 2:47 pm

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

  10. gis at ryerson » Geographic Analysis student makes a splash with global flight maps says:
    30 May ’13 at 1:41 am

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

  11. Global Flight Paths - Captivating Art from Everyday Airline Travel - The World by Road Collective - A Drive Around the World Expedition Turned New School Travel Portal says:
    30 May ’13 at 4:01 am

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

  12. U.O. says:
    1 Jun ’13 at 6:28 pm

    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)

    Reply
  13. U.O. says:
    1 Jun ’13 at 6:31 pm

    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)
    …

    Reply
  14. 58,288 Flight Routes on One Map | prettyawfulthings says:
    3 Jun ’13 at 12:01 pm

    […] Via Spa­tial Analysis. […]

  15. Fifty-eight thousand, two-hundred and eighty-eight flight routes | Stellar TravelStellar Travel says:
    5 Jun ’13 at 5:23 pm

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

  16. divesh says:
    8 Jun ’13 at 11:54 am

    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!

    Reply
    • Michael says:
      8 Jun ’13 at 12:00 pm

      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.

  17. Ken says:
    10 Jun ’13 at 2:49 pm

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

    Reply
  18. Paul says:
    15 Jun ’13 at 10:44 am

    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

    Reply
  19. Sharing some great tutorials | cartoblography says:
    17 Jun ’13 at 5:15 am

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

  20. Tom says:
    19 Jul ’13 at 8:44 am

    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

    Reply
  21. Maps that explain the world - Page 7 - City-Data Forum says:
    25 Nov ’13 at 12:55 am

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

  22. dan says:
    6 Dec ’13 at 11:08 am

    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

    Reply
  23. Ryan says:
    6 Dec ’13 at 11:46 pm

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

    Reply
  24. Jonathan Garlick says:
    18 Feb ’14 at 10:06 pm

    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

    Reply
  25. ????? ???? ?? ??????? ??????? ????????? says:
    16 Apr ’14 at 5:54 am

    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 ????? ???? ?? ??????? ??????? ?????????

    Reply
  26. fm 2013 hack says:
    22 Apr ’14 at 6:15 am

    The ‘Valentine’s Day’ dar­ling stepped out with gor­geous Veron­ica Lake wavges and
    matchong coral lip­stick. So it makes sense tto under­stand yoour col­leagues annd clients almost as welol as
    you under­stand the moods and atti­tudes of your friends and fam­ily.
    What does it take tto become the FHM sex­i­est woman in the world.

    Feel free to visit my page: fm 2013 hack

    Reply
  27. rea says:
    28 May ’14 at 8:55 am

    Thhank you for some other infor­ma­tive blog.
    Where elsee could I am get­ting that type of info writ­ten in such
    a per­fect approach? I’ve a under­tak­ing that I amm sim­ply now oper­at­ing on, and I have been
    at the glance oout for such info.

    Reply
  28. google adwords tool says:
    6 Jun ’14 at 4:54 am

    Thanks for finally writ­ing about > Global Con­nec­tiv­ity Revis­ited;
    map­ping out 58,288 flight routes | Spa­tial Analy­sis < Loved it!

    Reply
  29. Toronto ON basement leaks at floor says:
    17 Jun ’14 at 4:46 pm

    You could cer­tainly see your skills in the arti­cle you
    write. The sec­tor hopes for more pas­sion­ate writ­ers like you who are not afraid to say how they believe.

    Always go after your heart.

    my home­page — Toronto ON base­ment leaks at floor

    Reply
  30. Daria says:
    26 Jun ’14 at 3:18 pm

    I was see­ing a lot more than 500 #N/A val­ues when I processed the data in excel. As a result, after delet­ing all null and #N/A val­ues I only batched maybe.…8000 dif­fer­ent routes total. I’m not sure if it’s worth pur­su­ing fur­ther; if this is an indi­ca­tion that per­haps I’m doing some­thing wrong, or that the data has changed over the past few years, but either way, this may be more effort than it’s worth. If I am doing some­thing wrong, please let me know — I’m sus­pi­cious that it may be on my end of things.

    A much eas­ier method (per­haps due to the age of this tuto­r­ial) is to go into open­flights and down­load the routes.dat file directly and rela­bel as a .csv file, then upload into ArcMaps.

    Reply
  31. Dom says:
    10 Jul ’14 at 3:34 pm

    Fab­u­lous work! I would like to know if I can be pro­vided with a source JPEG file? I wanted to print this map on can­vas.
    Or would I have to reproduce?

    Thank you,
    DOM

    Reply
  32. In the world’s interconnected airspace, flights over conflict zones are common - The Washington Post says:
    17 Jul ’14 at 2:36 pm

    […] 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 — […]

 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>