flight_paramaters_commuting_only.R

# Developed by Tom Evans at Lund University: tom.evans@biol.lu.se
# You are welcome to use parts of this code, but please give credit when using it extensively.
# Code available at https://github.com/thomasevans/lbbg_gps


# A script to analyse each flight, with various
# paramaters, such as maximum altitude, calculated.
# First the database will be queried to pull out
# the data we need for our analysis.
# Second. Various paramaters and information about
# the flights will be calculated.
# Third. This will be ouput to a new database table
# specifically for flights.


# Database functions   ------
# To link to database
library(RODBC)

# Establish a connection to the database
gps.db <- odbcConnectAccess2007('D:/Dropbox/tracking_db/GPS_db.accdb')

# See what tables are available
# sqlTables(gps.db)


# Get commuting flights table
flights.com <- sqlQuery(gps.db, query=
                       "SELECT DISTINCT f.*
  FROM lund_flights_commuting AS f
  ORDER BY f.flight_id ASC;")

# Close connection
odbcClose(gps.db)




# Get a vector of flight numbers -----
# str(flights.com$flight_id)
flight_id <- sort(flights.com$flight_id)
f <- length(flight_id)

# source("flight_info.R")

gc()



fun.wrap <- function(id){
  source("flight_info.R")
  x <- flight.info(id, type = "com")
  
  if(is.na(x[3])){
    # If it fails try again ten times, wait 1 s between each try then give up
    count <- 0
    while((is.na(x[3])) & (count < 20)){
      source("flight_info.R")  # load function again
      # Not sure whey there's an error, but probably some how related to database access,     
      # waiting some time may help with this
      Sys.sleep(1)
      x <- flight.info(as.character(id), type = "com")
      count <- count + 1
    }
  }
  
  x <- t(x)
  gc()
  #output data as list (this will be appended to the global list, lst.
  return(x)
}



lst <- list()
system.time({
for(i in 1:f){
  id <- flight_id[i]
  lst[[i]] <- fun.wrap(id)
#   odbcCloseAll()
}
})

save(lst, file = "com_fl_par_data.RData")

str(lst)


#Create dataframe of flights  ------
#names for the dataframe
names.flights <- c("flight_id",
                   "points", "start_time",
                   "end_time", "duration",
                   "dist_max", "dist_total",
                   "interval_mean", "interval_min",
                   "device_info_serial",
                   "start_long", "start_lat",
                   "end_long", "end_lat",
                   "dist_nest_start",
                   "dist_nest_end",
                   "dist_nest_dif", "dist_a_b",
                   "straigtness", "bearing_a_b",
                   "speed_a_b", "speed_inst_mean",
                   "speed_inst_med",
                   "speed_inst_var", "alt_max",
                   "alt_min", "alt_mean", "alt_med",
                   "rho", "ang_dev", "ang_var")

#make a dataframe from the list generated by the above function.
flights <- data.frame(matrix(unlist(lst), nrow = f, byrow = TRUE))
# flights <- data.frame(matrix(unlist(lst), nrow = 10, byrow = TRUE))

row.names(flights) <- NULL

names(flights) <- names.flights

#origin of UNIX date_time epoch, required for coversion back to datetime objects for start_time and end_time
startdate <- "1970-01-01"
startdate <- as.Date(startdate)

#convert the end_time back to datetime format
flights$end_time <- as.POSIXct(
  as.POSIXlt(flights$end_time, origin=startdate,
             tz= "GMT",format="%Y-%m-%d %H:%M:%S"))

#conver the start_time back to datetime format
flights$start_time <- as.POSIXct(
  as.POSIXlt(flights$start_time, origin=startdate,
             tz= "GMT",format="%Y-%m-%d %H:%M:%S"))

# save(flights, file = "flights_part1.Rdata")


fx <- function(x){
  x <- as.character(x)
  as.numeric(x)
}

flights2 <- cbind(sapply(flights[,c(1,2)], fx), flights[,c(3,4)],sapply(flights[,5:31], fx))
# summary(is.na(flights2$flight_id))


#output data to database##################

# Re-open RODBC channel
gps.db <- odbcConnectAccess2007('D:/Dropbox/tracking_db/GPS_db.accdb')


#export flight information to the database
#will be neccessary to edit table in Access after to define data-types and primary keys and provide descriptions for each variable.
sqlSave(
  gps.db, flights2, tablename = "lund_flights_commuting_par",
  append = FALSE, rownames = FALSE,
  colnames = FALSE, verbose = FALSE,
  safer = TRUE, addPK = FALSE,
  fast = TRUE, test = FALSE,
  nastring = NULL,
  varTypes = c(start_time = "Date", end_time= "Date"))
  
message("After exporting table to DB, edit table in Access to define data-types and primary keys")

odbcCloseAll()

# 
# fails_1 <- fails
#   save(fails_1, file = "fails1.RData")