Builds a consolidated CSV of all known world flights based upon data at http://www.openflights.org/data.html

generate_flight_details.r

# usage: download routes.dat, airports.dat, and airlines.dat from
#    http://openflights.org/data.html
# replace this location with the path where you downloaded the files
mypath = "~/"
# run the script!

# set working directory
setwd(mypath)

# load the openflights data files
# source data can be obtained here: http://openflights.org/data.html
routes = read.csv("routes.dat")
airports = read.csv("airports.dat")
airlines = read.csv("airlines.dat")

# set column names for the data files (they are not column-headered)
colnames(routes) = c("AirlineId","Airline_OpenFlightsId","OriginId","Origin_OpenFlightsId","DestinationId","Destination_OpenFlightsId","Codeshare","Stops","Equipment")
colnames(airports) = c("Airport_OpenFlightsId", "Airport_Name","Airport_City","Airport_Country","Airpoirt_IataFaaCode","Airport_IcaoCode","Airport_Latitude","Airport_Longitude","Airport_Altitude","Airport_Timezone","Airport_DaylightSavings","Airport_OlsonTimezone")
colnames(airlines) = c("Airline_OpenFlightsId","Airline_Name","Airline_Alias","Airline_IataCode","Airline_IcaoCode","Airline_Callsign","Airline_CountryOfBase","Airline_Active")

# merge route and airline information
newroutes = merge(x = routes, y=airlines, by=c("Airline_OpenFlightsId"), all.x=TRUE)

# merge route origin and airport information
newroutes = merge(x = newroutes, y = subset(airports, select=c("Airport_OpenFlightsId","Airport_Name","Airport_City","Airport_Country","Airport_Latitude","Airport_Longitude","Airport_Altitude","Airport_OlsonTimezone")), by.x=c("Origin_OpenFlightsId"), by.y=c("Airport_OpenFlightsId"), all.x=TRUE)
colnames(newroutes)[17:23] = c("Origin_Airport_Name","Origin_Airport_City","Origin_Airport_Country","Origin_Airport_Latitude","Origin_Airport_Longitude","Origin_Airport_Altitude","Origin_Airport_OlsonTimezone")

# merge route destination and airport information
newroutes = merge(x = newroutes, y = subset(airports, select=c("Airport_OpenFlightsId","Airport_Name","Airport_City","Airport_Country","Airport_Latitude","Airport_Longitude","Airport_Altitude","Airport_OlsonTimezone")), by.x=c("Destination_OpenFlightsId"), by.y=c("Airport_OpenFlightsId"), all.x=TRUE)
colnames(newroutes)[24:30] = c("Destination_Airport_Name","Destination_Airport_City","Destination_Airport_Country","Destination_Airport_Latitude","Destination_Airport_Longitude","Destination_Airport_Altitude","Destination_Airport_OlsonTimezone")

# generate Well-Known Text for LINESTRINGs between Origin and Destination Lat/Logs for import into GIS tools
#   note: line geometries ARE NOT actual flight paths; merely links between origin and destination that are useful for network analysis
#   note: a spatial index is strongly recommended for this data
linestring_prefix = "LINESTRING("
linestring_suffix = ")"
newroutes$Linestring = paste(linestring_prefix,paste(paste(newroutes$Origin_Airport_Longitude,newroutes$Origin_Airport_Latitude, sep=" "), paste(newroutes$Destination_Airport_Longitude, newroutes$Destination_Airport_Latitude, sep=" "), sep = ","), linestring_suffix, sep="")

#output the results to a new file
write.csv(newroutes,"flights.csv",row.names=FALSE,na="")