FlightSegment.class_eval do
URL = 'http://www.transtats.bts.gov/DownLoad_Table.asp?Table_ID=293&Has_Group=3&Is_Zipped=0'
FORM_DATA = %{
UserTableName=T_100_Segment__All_Carriers&
DBShortName=Air_Carriers&
RawDataTable=T_T100_SEGMENT_ALL_CARRIER&
sqlstr=+SELECT+DEPARTURES_SCHEDULED%2CDEPARTURES_PERFORMED%2CPAYLOAD%2CSEATS%2CPASSENGERS%2CFREIGHT%2CMAIL%2CDISTANCE%2CRAMP_TO_RAMP%2CAIR_TIME%2CUNIQUE_CARRIER%2CAIRLINE_ID%2CUNIQUE_CARRIER_NAME%2CUNIQUE_CARRIER_ENTITY%2CREGION%2CCARRIER%2CCARRIER_NAME%2CCARRIER_GROUP%2CCARRIER_GROUP_NEW%2CORIGIN%2CORIGIN_CITY_NAME%2CORIGIN_CITY_NUM%2CORIGIN_STATE_ABR%2CORIGIN_STATE_FIPS%2CORIGIN_STATE_NM%2CORIGIN_COUNTRY%2CORIGIN_COUNTRY_NAME%2CORIGIN_WAC%2CDEST%2CDEST_CITY_NAME%2CDEST_CITY_NUM%2CDEST_STATE_ABR%2CDEST_STATE_FIPS%2CDEST_STATE_NM%2CDEST_COUNTRY%2CDEST_COUNTRY_NAME%2CDEST_WAC%2CAIRCRAFT_GROUP%2CAIRCRAFT_TYPE%2CAIRCRAFT_CONFIG%2CYEAR%2CQUARTER%2CMONTH%2CDISTANCE_GROUP%2CCLASS%2CDATA_SOURCE+FROM++T_T100_SEGMENT_ALL_CARRIER+WHERE+Month+%3D__MONTH_NUMBER__+AND+YEAR%3D__YEAR__&
varlist=DEPARTURES_SCHEDULED%2CDEPARTURES_PERFORMED%2CPAYLOAD%2CSEATS%2CPASSENGERS%2CFREIGHT%2CMAIL%2CDISTANCE%2CRAMP_TO_RAMP%2CAIR_TIME%2CUNIQUE_CARRIER%2CAIRLINE_ID%2CUNIQUE_CARRIER_NAME%2CUNIQUE_CARRIER_ENTITY%2CREGION%2CCARRIER%2CCARRIER_NAME%2CCARRIER_GROUP%2CCARRIER_GROUP_NEW%2CORIGIN%2CORIGIN_CITY_NAME%2CORIGIN_CITY_NUM%2CORIGIN_STATE_ABR%2CORIGIN_STATE_FIPS%2CORIGIN_STATE_NM%2CORIGIN_COUNTRY%2CORIGIN_COUNTRY_NAME%2CORIGIN_WAC%2CDEST%2CDEST_CITY_NAME%2CDEST_CITY_NUM%2CDEST_STATE_ABR%2CDEST_STATE_FIPS%2CDEST_STATE_NM%2CDEST_COUNTRY%2CDEST_COUNTRY_NAME%2CDEST_WAC%2CAIRCRAFT_GROUP%2CAIRCRAFT_TYPE%2CAIRCRAFT_CONFIG%2CYEAR%2CQUARTER%2CMONTH%2CDISTANCE_GROUP%2CCLASS%2CDATA_SOURCE&
grouplist=&
suml=&
sumRegion=&
filter1=title%3D&
filter2=title%3D&
geo=All%A0&
time=__MONTH_NAME__&
timename=Month&
GEOGRAPHY=All&
XYEAR=__YEAR__&
FREQUENCY=__MONTH_NUMBER__&
AllVars=All&
VarName=DEPARTURES_SCHEDULED&
VarDesc=DepScheduled&
VarType=Num&
VarName=DEPARTURES_PERFORMED&
VarDesc=DepPerformed&
VarType=Num&
VarName=PAYLOAD&
VarDesc=Payload&
VarType=Num&
VarName=SEATS&
VarDesc=Seats&
VarType=Num&
VarName=PASSENGERS&
VarDesc=Passengers&
VarType=Num&
VarName=FREIGHT&
VarDesc=Freight&
VarType=Num&
VarName=MAIL&
VarDesc=Mail&
VarType=Num&
VarName=DISTANCE&
VarDesc=Distance&
VarType=Num&
VarName=RAMP_TO_RAMP&
VarDesc=RampToRamp&
VarType=Num&
VarName=AIR_TIME&
VarDesc=AirTime&
VarType=Num&
VarName=UNIQUE_CARRIER&
VarDesc=UniqueCarrier&
VarType=Char&
VarName=AIRLINE_ID&
VarDesc=AirlineID&
VarType=Num&
VarName=UNIQUE_CARRIER_NAME&
VarDesc=UniqueCarrierName&
VarType=Char&
VarName=UNIQUE_CARRIER_ENTITY&
VarDesc=UniqCarrierEntity&
VarType=Char&
VarName=REGION&
VarDesc=CarrierRegion&
VarType=Char&
VarName=CARRIER&
VarDesc=Carrier&
VarType=Char&
VarName=CARRIER_NAME&
VarDesc=CarrierName&
VarType=Char&
VarName=CARRIER_GROUP&
VarDesc=CarrierGroup&
VarType=Num&
VarName=CARRIER_GROUP_NEW&
VarDesc=CarrierGroupNew&
VarType=Num&
VarName=ORIGIN&
VarDesc=Origin&
VarType=Char&
VarName=ORIGIN_CITY_NAME&
VarDesc=OriginCityName&
VarType=Char&
VarName=ORIGIN_CITY_NUM&
VarDesc=OriginCityNum&
VarType=Num&
VarName=ORIGIN_STATE_ABR&
VarDesc=OriginState&
VarType=Char&
VarName=ORIGIN_STATE_FIPS&
VarDesc=OriginStateFips&
VarType=Char&
VarName=ORIGIN_STATE_NM&
VarDesc=OriginStateName&
VarType=Char&
VarName=ORIGIN_COUNTRY&
VarDesc=OriginCountry&
VarType=Char&
VarName=ORIGIN_COUNTRY_NAME&
VarDesc=OriginCountryName&
VarType=Char&
VarName=ORIGIN_WAC&
VarDesc=OriginWac&
VarType=Num&
VarName=DEST&
VarDesc=Dest&
VarType=Char&
VarName=DEST_CITY_NAME&
VarDesc=DestCityName&
VarType=Char&
VarName=DEST_CITY_NUM&
VarDesc=DestCityNum&
VarType=Num&
VarName=DEST_STATE_ABR&
VarDesc=DestState&
VarType=Char&
VarName=DEST_STATE_FIPS&
VarDesc=DestStateFips&
VarType=Char&
VarName=DEST_STATE_NM&
VarDesc=DestStateName&
VarType=Char&
VarName=DEST_COUNTRY&
VarDesc=DestCountry&
VarType=Char&
VarName=DEST_COUNTRY_NAME&
VarDesc=DestCountryName&
VarType=Char&
VarName=DEST_WAC&
VarDesc=DestWac&
VarType=Num&
VarName=AIRCRAFT_GROUP&
VarDesc=AircraftGroup&
VarType=Num&
VarName=AIRCRAFT_TYPE&
VarDesc=AircraftType&
VarType=Char&
VarName=AIRCRAFT_CONFIG&
VarDesc=AircraftConfig&
VarType=Num&
VarName=YEAR&
VarDesc=Year&
VarType=Num&
VarName=QUARTER&
VarDesc=Quarter&
VarType=Num&
VarName=MONTH&
VarDesc=Month&
VarType=Num&
VarName=DISTANCE_GROUP&
VarDesc=DistanceGroup&
VarType=Num&
VarName=CLASS&
VarDesc=Class&
VarType=Char&
VarName=DATA_SOURCE&
VarDesc=DataSource&
VarType=Char
}.gsub /[\s]+/,''
data_miner do
schema Earth.database_options do
string 'row_hash'
integer 'departures_performed'
integer 'passengers'
integer 'total_seats'
float 'payload' # theoretical max freight + mail + passengers; needs to be a float because the import includes a units conversion
string 'payload_units'
float 'freight' # this needs to be a float because the import includes a units conversion
string 'freight_units'
float 'mail' # this needs to be a float because the import includes a units conversion
string 'mail_units'
float 'load_factor'
float 'freight_share'
float 'seats'
float 'distance'
string 'distance_units'
string 'aircraft_bts_code'
string 'airline_iata_code'
string 'origin_airport_iata_code'
string 'origin_country_iso_3166_code'
string 'destination_airport_iata_code'
string 'destination_country_iso_3166_code'
integer 'year'
integer 'quarter'
integer 'month'
date 'approximate_date'
# integer 'bts_aircraft_group_code'
# string 'configuration_id'
# integer 'bts_aircraft_configuration_code'
# string 'distance_group'
# integer 'bts_distance_group_code'
# string 'bts_data_source_code'
# integer 'ramp_to_ramp'
# integer 'air_time'
# integer 'departures_scheduled'
# string 'dot_airline_id_code'
# string 'unique_carrier_name'
# string 'unique_carrier_entity'
# string 'region'
# string 'current_airline_iata_code'
# string 'carrier_name'
# integer 'carrier_group'
# integer 'carrier_group_new'
# string 'origin_city_name'
# integer 'origin_city_num'
# string 'origin_state_abr'
# string 'origin_state_fips'
# string 'origin_state_nm'
# string 'origin_country_name'
# integer 'origin_wac'
# string 'dest_city_name'
# integer 'dest_city_num'
# string 'dest_state_abr'
# string 'dest_state_fips'
# string 'dest_state_nm'
# string 'dest_country_name'
# integer 'dest_wac'
index 'airline_iata_code'
index 'aircraft_bts_code'
index 'origin_airport_iata_code'
index 'destination_airport_iata_code'
end
months = Hash.new
(2008..2009).each do |year|
# (2008..2008).each do |year| # DEBUG MODE!
(1..12).each do |month|
# (1..1).each do |month| # DEBUG MODE!
time = Time.gm year, month
form_data = FORM_DATA.dup
form_data.gsub! '__YEAR__', time.year.to_s
form_data.gsub! '__MONTH_NUMBER__', time.month.to_s
form_data.gsub! '__MONTH_NAME__', time.strftime('%B')
months[time] = form_data
end
end
# creating dictionaries by hand so that a new one doesn't get created for every month
# distance_group_dictionary = DataMiner::Dictionary.new :input => 'Code', :output => 'Description', :url => 'http://www.transtats.bts.gov/Download_Lookup.asp?Lookup=L_DISTANCE_GROUP_500'
months.each do |month, form_data|
import "T100 flight segment data from #{month.strftime('%B %Y')}",
:url => URL,
:form_data => form_data,
:compression => :zip,
:glob => '/*.csv' do
key 'row_hash'
# store 'bts_aircraft_group_code', :field_name => 'AIRCRAFT_GROUP'
# store 'configuration_id', :field_name => 'AIRCRAFT_CONFIG', :dictionary => configuration_dictionary
# store 'bts_aircraft_configuration_code', :field_name => 'AIRCRAFT_CONFIG'
# store 'distance_group', :field_name => 'DISTANCE_GROUP', :dictionary => distance_group_dictionary
# store 'bts_distance_group_code', :field_name => 'DISTANCE_GROUP'
# store 'bts_data_source_code', :field_name => 'DATA_SOURCE'
# store 'departures_scheduled', :field_name => 'DEPARTURES_SCHEDULED'
store 'departures_performed', :field_name => 'DEPARTURES_PERFORMED'
store 'passengers', :field_name => 'PASSENGERS'
store 'total_seats', :field_name => 'SEATS'
store 'payload', :field_name => 'PAYLOAD', :from_units => :pounds, :to_units => :kilograms
store 'freight', :field_name => 'FREIGHT', :from_units => :pounds, :to_units => :kilograms
store 'mail', :field_name => 'MAIL', :from_units => :pounds, :to_units => :kilograms
store 'distance', :field_name => 'DISTANCE', :from_units => :miles, :to_units => :kilometres
store 'aircraft_bts_code', :field_name => 'AIRCRAFT_TYPE'
store 'airline_iata_code', :field_name => 'UNIQUE_CARRIER'
store 'origin_airport_iata_code', :field_name => 'ORIGIN'
store 'origin_country_iso_3166_code', :field_name => 'ORIGIN_COUNTRY'
store 'destination_airport_iata_code', :field_name => 'DEST'
store 'destination_country_iso_3166_code', :field_name => 'DEST_COUNTRY'
store 'year', :field_name => 'YEAR'
store 'quarter', :field_name => 'QUARTER'
store 'month', :field_name => 'MONTH'
# store 'ramp_to_ramp', :field_name => 'RAMP_TO_RAMP'
# store 'air_time', :field_name => 'AIR_TIME'
# store 'dot_airline_id_code', :field_name => 'AIRLINE_ID'
# store 'unique_carrier_name', :field_name => 'UNIQUE_CARRIER_NAME'
# store 'unique_carrier_entity', :field_name => 'UNIQUE_CARRIER_ENTITY'
# store 'region', :field_name => 'REGION'
# store 'current_airline_iata_code', :field_name => 'CARRIER'
# store 'carrier_name', :field_name => 'CARRIER_NAME'
# store 'carrier_group', :field_name => 'CARRIER_GROUP'
# store 'carrier_group_new', :field_name => 'CARRIER_GROUP_NEW'
# store 'origin_city_name', :field_name => 'ORIGIN_CITY_NAME'
# store 'origin_city_num', :field_name => 'ORIGIN_CITY_NUM'
# store 'origin_state_abr', :field_name => 'ORIGIN_STATE_ABR'
# store 'origin_state_fips', :field_name => 'ORIGIN_STATE_FIPS'
# store 'origin_state_nm', :field_name => 'ORIGIN_STATE_NM'
# store 'origin_country_name', :field_name => 'ORIGIN_COUNTRY_NAME'
# store 'origin_wac', :field_name => 'ORIGIN_WAC'
# store 'dest_city_name', :field_name => 'DEST_CITY_NAME'
# store 'dest_city_num', :field_name => 'DEST_CITY_NUM'
# store 'dest_state_abr', :field_name => 'DEST_STATE_ABR'
# store 'dest_state_fips', :field_name => 'DEST_STATE_FIPS'
# store 'dest_state_nm', :field_name => 'DEST_STATE_NM'
# store 'dest_country_name', :field_name => 'DEST_COUNTRY_NAME'
# store 'dest_wac', :field_name => 'DEST_WAC'
end
end
process "Derive freight share as a fraction of the total weight carried" do
update_all 'freight_share = (freight + mail) / (freight + mail + (passengers * 90.718474))', '(freight + mail + passengers) > 0'
end
process "Derive load factor, which is passengers divided by the total seats available" do
update_all 'load_factor = passengers / total_seats', 'total_seats > 0'
end
process "Assume a load factor of 1 where passengers > total seats available" do
update_all 'load_factor = 1', 'passengers > total_seats AND total_seats > 0'
end
process "Derive average seats per departure" do
update_all 'seats = total_seats / departures_performed', 'departures_performed > 0'
end
process "Add a useful date field" do
update_all 'approximate_date = DATE(CONCAT_WS("-", year, month, "14"))'
end
# FIXME TODO make this verification check actual aircraft codes in Aircraft
verify 'All segments have an associated aircraft' do
FlightSegment.where(:aircraft_bts_code => [nil, '']).first.nil?
end
# FIXME TODO finish verification
end
end