Air travel makes it easy for any one of us to hop from country to country, for most of the time that’s fine. When the H1N1 flu virus started to spread though it wasn’t much of a surprise how the far and wide reach of reports was caused by, travel. With data being readily available it was easy to see how it was moving, a good example of this was the “Just Landed In” post which used Twitter data, Metacarta and Processing to visualise the movements (http://blog.blprnt.com/blog/blprnt/just-landed-processing-twitter-metacarta-hidden-data)

The Ebola virus has the potential to cause an awful lot of problems. So the question is where are the first connection points?

Gathering Route Data

The actual query to find out what flights goes where it easy enough and I’ll show you that in a moment. First though we need data, mainly airports, airlines and routes.

OpenFlights has all this data to hand in CSV format.

Airports: airports.dat

Airlines: airlines.dat

Routes: routes.dat

As they are CSV files they can be imported into a database such as MySQL easily. You can download the sql files here.

Crafting The Query

As it stands there are only four countries in West Africa that the Ebola outbreak is concentrated on: Guinea, Liberia, Sierra Leone and Nigeria.  The United States and Senegal have travel related cases but I’m not looking at those just yet, one step at a time.

The SQL query is basic but it gives us good info.

SELECT 
 r.airlinecode, 
 al.airlinename, 
 a.longname as depairport, 
 a.country as depcountry, 
 a.iatacode as depcode, 
 b.longname as arrairport, 
 b.iatacode as arrcode, 
 b.country as arrcountry 
 
 FROM airports a, airports b, routes r, airlines al 
 WHERE r.depaircode 
 IN (SELECT iatacode FROM airports WHERE country="NIGERIA") 
 AND a.iatacode=r.depaircode
 AND b.iatacode=r.arraircode
 AND al.iatacode=r.airlinecode

This query will give us the departure and destination airports available for Nigeria, the country can be changed to anything.

Inspecting The Results

The output we get is the airline information, departure airport and IATA code and the destination airport.

"airlinecode","airlinename","depairport","depcountry","depcode","arrairport","arrcode","arrcountry"
"AF","Air France","Nnamdi Azikiwe Intl","Nigeria","ABV","Charles De Gaulle","CDG","France"
"AF","Air France","Nnamdi Azikiwe Intl","Nigeria","ABV","Port Harcourt Intl","PHC","Nigeria"
"AF","Air France","Murtala Muhammed","Nigeria","LOS","Charles De Gaulle","CDG","France"
"AF","Air France","Port Harcourt Intl","Nigeria","PHC","Nnamdi Azikiwe Intl","ABV","Nigeria"
"AT","Royal Air Maroc","Murtala Muhammed","Nigeria","LOS","Mohammed V Intl","CMN","Morocco"
"AZ","Alitalia - Compagnia Aerea Italiana Spa","Murtala Muhammed","Nigeria","LOS","Kotoka Intl","ACC","Ghana"
"BA","British Airways","Nnamdi Azikiwe Intl","Nigeria","ABV","Heathrow","LHR","United Kingdom"
"BA","British Airways","Murtala Muhammed","Nigeria","LOS","Heathrow","LHR","United Kingdom"
"DL","Delta Air Lines","Nnamdi Azikiwe Intl","Nigeria","ABV","Charles De Gaulle","CDG","France"
"DL","Delta Air Lines","Nnamdi Azikiwe Intl","Nigeria","ABV","Port Harcourt Intl","PHC","Nigeria"
"DL","Delta Air Lines","Port Harcourt Intl","Nigeria","PHC","Nnamdi Azikiwe Intl","ABV","Nigeria"
"E2","Eagle Atlantic Airlines","Murtala Muhammed","Nigeria","LOS","Kotoka Intl","ACC","Ghana"
"EK","Emirates","Murtala Muhammed","Nigeria","LOS","Dubai Intl","DXB","United Arab Emirates"
"ET","Ethiopian Airlines","Nnamdi Azikiwe Intl","Nigeria","ABV","Bole Intl","ADD","Ethiopia"
"ET","Ethiopian Airlines","Nnamdi Azikiwe Intl","Nigeria","ABV","Cadjehoun","COO","Benin"
"ET","Ethiopian Airlines","Nnamdi Azikiwe Intl","Nigeria","ABV","Gnassingbe Eyadema Intl","LFW","Togo"
"ET","Ethiopian Airlines","Nnamdi Azikiwe Intl","Nigeria","ABV","Ndjamena Hassan Djamous","NDJ","Chad"
"ET","Ethiopian Airlines","Nnamdi Azikiwe Intl","Nigeria","ABV","Diori Hamani","NIM","Niger"
"ET","Ethiopian Airlines","Enugu","Nigeria","ENU","Bole Intl","ADD","Ethiopia"
"ET","Ethiopian Airlines","Murtala Muhammed","Nigeria","LOS","Abidjan Felix Houphouet Boigny Intl","ABJ","Cote d'Ivoire"
"ET","Ethiopian Airlines","Murtala Muhammed","Nigeria","LOS","Bole Intl","ADD","Ethiopia"
"ET","Ethiopian Airlines","Murtala Muhammed","Nigeria","LOS","Leon M Ba","LBV","Gabon"
"ET","Ethiopian Airlines","Murtala Muhammed","Nigeria","LOS","Gnassingbe Eyadema Intl","LFW","Togo"
"EY","Etihad Airways","Murtala Muhammed","Nigeria","LOS","Abu Dhabi Intl","AUH","United Arab Emirates"
"IB","Iberia","Murtala Muhammed","Nigeria","LOS","Barajas","MAD","Spain"
"KL","KLM-Royal Dutch Airlines","Nnamdi Azikiwe Intl","Nigeria","ABV","Charles De Gaulle","CDG","France"
"KL","KLM-Royal Dutch Airlines","Nnamdi Azikiwe Intl","Nigeria","ABV","Port Harcourt Intl","PHC","Nigeria"
"KL","KLM-Royal Dutch Airlines","Murtala Muhammed","Nigeria","LOS","Schiphol","AMS","Netherlands"
"KL","KLM-Royal Dutch Airlines","Port Harcourt Intl","Nigeria","PHC","Nnamdi Azikiwe Intl","ABV","Nigeria"
"KQ","Kenya Airways","Murtala Muhammed","Nigeria","LOS","Cadjehoun","COO","Benin"
"KQ","Kenya Airways","Murtala Muhammed","Nigeria","LOS","Jomo Kenyatta International","NBO","Kenya"
"LH","Lufthansa German Airlines","Nnamdi Azikiwe Intl","Nigeria","ABV","Frankfurt Main","FRA","Germany"
"LH","Lufthansa German Airlines","Nnamdi Azikiwe Intl","Nigeria","ABV","Malabo","SSG","Equatorial Guinea"
"LH","Lufthansa German Airlines","Murtala Muhammed","Nigeria","LOS","Frankfurt Main","FRA","Germany"
"LH","Lufthansa German Airlines","Murtala Muhammed","Nigeria","LOS","Port Harcourt Intl","PHC","Nigeria"
"LH","Lufthansa German Airlines","Port Harcourt Intl","Nigeria","PHC","Murtala Muhammed","LOS","Nigeria"
"ME","Middle East Airlines","Mallam Aminu Intl","Nigeria","KAN","Rafic Hariri Intl","BEY","Lebanon"
"ME","Middle East Airlines","Murtala Muhammed","Nigeria","LOS","Abidjan Felix Houphouet Boigny Intl","ABJ","Cote d'Ivoire"
"ME","Middle East Airlines","Murtala Muhammed","Nigeria","LOS","Rafic Hariri Intl","BEY","Lebanon"
"MS","Egyptair","Nnamdi Azikiwe Intl","Nigeria","ABV","Cairo Intl","CAI","Egypt"
"MS","Egyptair","Mallam Aminu Intl","Nigeria","KAN","Cairo Intl","CAI","Egypt"
"MS","Egyptair","Murtala Muhammed","Nigeria","LOS","Cairo Intl","CAI","Egypt"
"QC","Camair-Co","Murtala Muhammed","Nigeria","LOS","Abidjan Felix Houphouet Boigny Intl","ABJ","Cote d'Ivoire"
"QC","Camair-Co","Murtala Muhammed","Nigeria","LOS","Douala","DLA","Cameroon"
"QR","Qatar Airways","Murtala Muhammed","Nigeria","LOS","Doha Intl","DOH","Qatar"
"RJ","Royal Jordanian","Murtala Muhammed","Nigeria","LOS","Kotoka Intl","ACC","Ghana"
"RJ","Royal Jordanian","Murtala Muhammed","Nigeria","LOS","Queen Alia Intl","AMM","Jordan"
"SA","South African Airways","Murtala Muhammed","Nigeria","LOS","Johannesburg Intl","JNB","South Africa"
"SD","Sudan Airways","Mallam Aminu Intl","Nigeria","KAN","Khartoum","KRT","Sudan"
"SN","Brussels Airlines","Murtala Muhammed","Nigeria","LOS","Frankfurt Main","FRA","Germany"
"SN","Brussels Airlines","Murtala Muhammed","Nigeria","LOS","Port Harcourt Intl","PHC","Nigeria"
"SN","Brussels Airlines","Port Harcourt Intl","Nigeria","PHC","Murtala Muhammed","LOS","Nigeria"
"TK","Turkish Airlines","Murtala Muhammed","Nigeria","LOS","Ataturk","IST","Turkey"
"UA","United Airlines","Murtala Muhammed","Nigeria","LOS","Frankfurt Main","FRA","Germany"
"UA","United Airlines","Murtala Muhammed","Nigeria","LOS","George Bush Intercontinental","IAH","United States"
"UA","United Airlines","Murtala Muhammed","Nigeria","LOS","Port Harcourt Intl","PHC","Nigeria"
"UA","United Airlines","Port Harcourt Intl","Nigeria","PHC","Murtala Muhammed","LOS","Nigeria"
"VS","Virgin Atlantic Airways","Murtala Muhammed","Nigeria","LOS","Heathrow","LHR","United Kingdom"
"W3","Arik Air","Nnamdi Azikiwe Intl","Nigeria","ABV","Kotoka Intl","ACC","Ghana"
"W3","Arik Air","Nnamdi Azikiwe Intl","Nigeria","ABV","Benin","BNI","Nigeria"
"W3","Arik Air","Nnamdi Azikiwe Intl","Nigeria","ABV","Calabar","CBQ","Nigeria"
"W3","Arik Air","Nnamdi Azikiwe Intl","Nigeria","ABV","Enugu","ENU","Nigeria"
"W3","Arik Air","Nnamdi Azikiwe Intl","Nigeria","ABV","Ibadan","IBA","Nigeria"
"W3","Arik Air","Nnamdi Azikiwe Intl","Nigeria","ABV","Ilorin","ILR","Nigeria"
"W3","Arik Air","Nnamdi Azikiwe Intl","Nigeria","ABV","Mallam Aminu Intl","KAN","Nigeria"
"W3","Arik Air","Nnamdi Azikiwe Intl","Nigeria","ABV","Murtala Muhammed","LOS","Nigeria"
"W3","Arik Air","Nnamdi Azikiwe Intl","Nigeria","ABV","Maiduguri","MIU","Nigeria"
"W3","Arik Air","Nnamdi Azikiwe Intl","Nigeria","ABV","Port Harcourt Intl","PHC","Nigeria"
"W3","Arik Air","Nnamdi Azikiwe Intl","Nigeria","ABV","Imo Airport","QOW","Nigeria"
"W3","Arik Air","Nnamdi Azikiwe Intl","Nigeria","ABV","Warri Airport","QRW","Nigeria"
"W3","Arik Air","Nnamdi Azikiwe Intl","Nigeria","ABV","Sadiq Abubakar Iii Intl","SKO","Nigeria"
"W3","Arik Air","Nnamdi Azikiwe Intl","Nigeria","ABV","Yola","YOL","Nigeria"
"W3","Arik Air","Benin","Nigeria","BNI","Nnamdi Azikiwe Intl","ABV","Nigeria"
"W3","Arik Air","Benin","Nigeria","BNI","Murtala Muhammed","LOS","Nigeria"
"W3","Arik Air","Calabar","Nigeria","CBQ","Nnamdi Azikiwe Intl","ABV","Nigeria"
"W3","Arik Air","Calabar","Nigeria","CBQ","Murtala Muhammed","LOS","Nigeria"
"W3","Arik Air","Enugu","Nigeria","ENU","Nnamdi Azikiwe Intl","ABV","Nigeria"
"W3","Arik Air","Enugu","Nigeria","ENU","Murtala Muhammed","LOS","Nigeria"
"W3","Arik Air","Ibadan","Nigeria","IBA","Nnamdi Azikiwe Intl","ABV","Nigeria"
"W3","Arik Air","Ilorin","Nigeria","ILR","Nnamdi Azikiwe Intl","ABV","Nigeria"
"W3","Arik Air","Yakubu Gowon","Nigeria","JOS","Murtala Muhammed","LOS","Nigeria"
"W3","Arik Air","Kaduna","Nigeria","KAD","Murtala Muhammed","LOS","Nigeria"
"W3","Arik Air","Mallam Aminu Intl","Nigeria","KAN","Nnamdi Azikiwe Intl","ABV","Nigeria"
"W3","Arik Air","Mallam Aminu Intl","Nigeria","KAN","Murtala Muhammed","LOS","Nigeria"
"W3","Arik Air","Murtala Muhammed","Nigeria","LOS","Nnamdi Azikiwe Intl","ABV","Nigeria"
"W3","Arik Air","Murtala Muhammed","Nigeria","LOS","Kotoka Intl","ACC","Ghana"
"W3","Arik Air","Murtala Muhammed","Nigeria","LOS","Benin","BNI","Nigeria"
"W3","Arik Air","Murtala Muhammed","Nigeria","LOS","Calabar","CBQ","Nigeria"
"W3","Arik Air","Murtala Muhammed","Nigeria","LOS","Leopold Sedar Senghor Intl","DKR","Senegal"
"W3","Arik Air","Murtala Muhammed","Nigeria","LOS","Douala","DLA","Cameroon"
"W3","Arik Air","Murtala Muhammed","Nigeria","LOS","Enugu","ENU","Nigeria"
"W3","Arik Air","Murtala Muhammed","Nigeria","LOS","Freetown Lungi","FNA","Sierra Leone"
"W3","Arik Air","Murtala Muhammed","Nigeria","LOS","John F Kennedy Intl","JFK","United States"
"W3","Arik Air","Murtala Muhammed","Nigeria","LOS","Johannesburg Intl","JNB","South Africa"
"W3","Arik Air","Murtala Muhammed","Nigeria","LOS","Yakubu Gowon","JOS","Nigeria"
"W3","Arik Air","Murtala Muhammed","Nigeria","LOS","Kaduna","KAD","Nigeria"
"W3","Arik Air","Murtala Muhammed","Nigeria","LOS","Mallam Aminu Intl","KAN","Nigeria"
"W3","Arik Air","Murtala Muhammed","Nigeria","LOS","Luanda 4 De Fevereiro","LAD","Angola"
"W3","Arik Air","Murtala Muhammed","Nigeria","LOS","Heathrow","LHR","United Kingdom"
"W3","Arik Air","Murtala Muhammed","Nigeria","LOS","Port Harcourt Intl","PHC","Nigeria"
"W3","Arik Air","Murtala Muhammed","Nigeria","LOS","Imo Airport","QOW","Nigeria"
"W3","Arik Air","Murtala Muhammed","Nigeria","LOS","Warri Airport","QRW","Nigeria"
"W3","Arik Air","Maiduguri","Nigeria","MIU","Nnamdi Azikiwe Intl","ABV","Nigeria"
"W3","Arik Air","Port Harcourt Intl","Nigeria","PHC","Nnamdi Azikiwe Intl","ABV","Nigeria"
"W3","Arik Air","Port Harcourt Intl","Nigeria","PHC","Murtala Muhammed","LOS","Nigeria"
"W3","Arik Air","Imo Airport","Nigeria","QOW","Nnamdi Azikiwe Intl","ABV","Nigeria"
"W3","Arik Air","Imo Airport","Nigeria","QOW","Murtala Muhammed","LOS","Nigeria"
"W3","Arik Air","Warri Airport","Nigeria","QRW","Nnamdi Azikiwe Intl","ABV","Nigeria"
"W3","Arik Air","Warri Airport","Nigeria","QRW","Murtala Muhammed","LOS","Nigeria"
"W3","Arik Air","Sadiq Abubakar Iii Intl","Nigeria","SKO","Nnamdi Azikiwe Intl","ABV","Nigeria"
"W3","Arik Air","Yola","Nigeria","YOL","Nnamdi Azikiwe Intl","ABV","Nigeria"
"WB","Rwandair Express","Murtala Muhammed","Nigeria","LOS","Kotoka Intl","ACC","Ghana"
"WB","Rwandair Express","Murtala Muhammed","Nigeria","LOS","Kigali Intl","KGL","Rwanda"

So we know the first point airports, next time we’ll take things a bit further…..