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:
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
ô
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
Thanks Cyrille!
~ fixed theô link
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
[ãÎ] 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 [ãÎ]
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
Hey Manuel,
UnforôÙtuôÙnately, I donãt have a method for comôÙpletôÙing this in QGIS. I am not familôÙiar with an equivôÙaôÙlent QGIS tool for ArcGIS ãxy to lineã. But I can howôÙever direct you to this disôÙcusôÙsion which talks about mapôÙping flight routes using a comôÙbiôÙnaôÙtion of QGIS and a PostôÙGIS dataôÙbase http://underdark.wordpress.com/2011/08/20/visualizing-global-connections/
Cheers,
Michael
Manuel,
A folôÙlowô upãÎ
A comôÙbiôÙnaôÙtion of QGIS and GRASS will allow you to conôÙvert xy coorôÙdiôÙnates to line feaôÙtures. Take a look at this wiki page. http://grass.osgeo.org/wiki/Convert_points_to_lines
Hi Michael,
Thanks for sharing.
Chris
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
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
[ãÎ] [ãÎ]
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
[ãÎ] 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 [ãÎ]
[ãÎ] 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 [ãÎ]
[ãÎ] 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 [ãÎ]
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)
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)
ãÎ
[ãÎ] Via SpaôÙtial Analysis. [ãÎ]
[ãÎ] 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 [ãÎ]
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.
Can you share what symôÙbolôÙogy setôÙtings you used in ArcGIS to get the final effect? Thanks
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
[ãÎ] Global ConôÙnecôÙtivôÙity RevisôÙited: mapôÙping out 58,288 flight routes by Michael MarkiôÙeta aka (@MichaelMarkieta) [ãÎ]
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
[ãÎ] 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 [ãÎ]
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
FanôÙtasôÙtic idea! Any idea how in ArcMap, orô QGIS?
Thanks so much for all of this, itãs great. Is there anyôÙway to do this in ArcMap 9.2?
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
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 ????? ????ô ?? ??????? ??????? ?????????
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
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.
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!
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
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.
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
[ãÎ] 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 ã [ãÎ]