The summer just begins and a lot of people start to experience flight delay. I am also interested in seeing which cites in the US are well connected by flights. The OpenFlights project provides free flight information. Then I used R to download the data and loaded them into a SQLite database, since I try to keep them as the persistent data. RSQLite facilities R to join and query tables in the database. Finally the flight routes and the airports were visualized by ggplot2 and ggmap.
RankIATA codeCityArriving flight routes
2LAXLos Angeles438
4JFKNew York363
6DFWDallas-Fort Worth281
7SFOSan Francisco259
Wiki says that Atlanta is the busiest airport in the US according to total passenger boardings. However, from the number of the incoming flight routs, it only ranks 5th, following Chicago, Los Angeles, Denver and New York. Possibly Atlanta is the hub mostly for passengers to do connection. If somebody really loves air traveling, Chicago(with ORD) and New York(with both JFK and EWR) are the two most convenient cities to stay with, because they have the most options.
This post is inspired by one post on the blog Data Science and R
# Import libraries and set up directory
setwd("C:/Google Drive/Codes")

# Read data directly from URLs
airport <- read.csv("", header = F)
route <- read.csv("", header = F)

# Remove the airports without IATA codes and rename the variables
airport <- airport[airport$V5!='', c('V3', 'V4', 'V5','V7','V8','V9')]
colnames(airport) <- c("City", "Country", "IATA", "lantitude", "longitude", "altitude")
route <- route[c('V3', 'V5')]
colnames(route) <- c("Departure", "Arrival")

# Store data to SQLite database
conn <- dbConnect("SQLite", dbname = "air.db")
dbSendQuery(conn, "drop table if exists airport;")
dbWriteTable(conn, "airport", airport)
dbSendQuery(conn, "drop table if exists route;")
dbWriteTable(conn, "route", route)

# Manipulate data in SQLite database
conn <- dbConnect("SQLite", dbname = "air.db")
sqlcmd01 <- dbSendQuery(conn, "
select a.type, as iata, a.frequency,,, b.lantitude, b.longitude
from (select 'depart' as type, departure as city, count(departure) as frequency
from route
group by departure
order by frequency desc, type) as a
left join airport as b on = b.iata
order by frequency desc
top <- fetch(sqlcmd01, n = -
sqlcmd02 <- dbSendQuery(conn, "
select route.rowid as id, route.departure as point, airport.lantitude as lantitude, airport.longitude as longitude
from route left join airport on route.departure = airport.iata
select route.rowid as id, route.arrival as point, airport.lantitude as lantitude, airport.longitude as longitude
from route left join airport on route.arrival = airport.iata
order by id
combine <- fetch(sqlcmd02, n = -

# Draw the flight routes and the airports on Google map
ggmap(get_googlemap(center = 'us', zoom =
4, maptype = 'roadmap'), extent = 'device') +
geom_line(data = combine, aes(x = longitude, y = lantitude, group = id), size =
alpha =
0.05,color = 'red4') +
geom_point(data = top, aes(x = longitude, y = lantitude, size = frequency), colour = "blue", alpha =
0.3) +