R/getCollectionTable.R

In birdscanR: Migration Traffic Rate Calculation Package for 'Birdscan MR1' Radars

#### getCollectionTable ------------------------------------------------------------
#' @title  Get BirdScan collection table
#' @description load collection from 'Birdscan MR1' 'SQL' database
#' @author Fabian Hertner, \email{fabian.hertner@@swiss-birdradar.com}; 
#' Birgen Haest, \email{birgen.haest@@vogelwarte.ch}
#' @param dbConnection a valid  database connection
#' @param dbDriverChar the name of the driver. If different from 'PostgreSQL' 
#' it connects to cloud.birdradar.com
#'
#' @return A dataframe with the collection table
#' @export
#' @examples
#' \dontrun{
#' # Set server and database settings
#' # ===========================================================================
#'   dbServer       = "MACHINE\\SERVERNAME"     # Set the name of your SQL server
#'   dbName         = "db_Name"                   # Set the name of your database
#'   dbDriverChar   = "SQL Server"                # Set either "SQL Server" or "PostgreSQL"
#'
#' # Open the connection with the database
#' # ===========================================================================
#' dsn = paste0("driver=", dbDriverChar, ";server=", dbServer,
#'              ";database=", dbName,
#'              ";uid=", rstudioapi::askForPassword("Database user"),
#'              ";pwd=", rstudioapi::askForPassword("Database password"))
#' dbConnection = RODBC::odbcDriverConnect(dsn)
#' 
#' collectionTable = getCollectionTable(dbConnection, dbDriverChar)
#' }
#' 
getCollectionTable = function(dbConnection , dbDriverChar){
  # Set feature name translations
  # ===========================================================================
    featureNames = data.frame(feature = c("feature1", "feature2", "feature3", 	
                                          "feature4", "feature5", "feature6", 	
                                          "feature7", "feature8", "feature9", 	
                                          "feature10", "feature11", "feature12",
                                          "feature13", "feature14", "feature15", 
                                          "feature16", "feature17", "feature18",
                                          "feature19", "feature20", "feature21",
                                          "feature22", "feature23", "feature24",
                                          "feature25", "feature26", "feature27",
                                          "feature28", "feature29", "feature30",
                                          "feature31", "feature32", "feature33",
                                          "feature34", "feature35", "feature36", 
                                          "feature37", "feature38", "feature39",
                                          "feature40", "feature41", "feature42", 
                                          "feature43", "feature44", "feature45",
                                          "feature46", "feature47", "feature48",
                                          "feature49", "feature50"), 
                              featureNames = c("altitude_AGL",         # Altitude above ground level (m)
                                               "azimuth",              # Azimuth (true north)
                                               "speed",                # Speed (m/s)
                                               NA, NA, "rotationFreq", # Rotation frequency in relation to sampling frequency
                                               NA, NA, NA, NA, NA, NA,
                                               "freqRatio",            # Frequency Ratio
                                               "maxLevel",             # Maximum level
                                               "polRatio",             # Polarisation ratio
                                               "absPolarisation",      # Absolute polarisation
                                               "rcs",                  # Radar cross section (m^2)
                                               "sqrt(RCS)",            # Square root of radar cross section (m)
                                               "durationOfEcho",       # Duration of echo (seconds)
                                               "durationOfEchoInSTC",  # Duration of echo in STC
                                               NA, NA, NA,
                                               "alpha",                # Alpha: |AlphaEnd – AlphaStart|
                                               "theta",                # Theta: 2⋅EpsilonCalc
                                               NA, NA, NA, NA, 
                                               "altitudeLeftSideOfEcho",  # Altitude left side of echo (m)
                                               "altitudeRightSideOfEcho", # Altitude right side of echo (m)
                                               NA,
                                               "distLeftToBottom",     # Distance between left side and bottom of echo (samples)
                                               "nSamplesInEcho",       # Length of echo (samples)
                                               "areaOfEcho",           # Area of echo (seconds multiplied by meter)
                                               NA,
                                               "speed",                # Speed (m/s) - This is the new speed variable, included in the Birdscan software as of v1.7
                                               NA, NA, NA, NA, NA, NA, 
                                               NA, NA, NA, NA, NA, NA, NA))
    
   # load collection from 'MS-SQL' database
   # ===========================================================================
   if (dbDriverChar != 'PostgreSQL'){
      collectionTable            = QUERY(dbConnection, 
                                         dbDriverChar, 
                                         paste0("Select * From collection order by row asc"))
      collectionTable_time_stamp = QUERY(dbConnection, 
                                         dbDriverChar, 
                                         paste0("Select time_stamp From collection order by row asc"), 
                                         as.is = TRUE)
      collectionTable$time_stamp = collectionTable_time_stamp$time_stamp
   # load collection from 'PostgreSQL' database
   # ===========================================================================
   } else {
      collectionTable            = QUERY(dbConnection, 
                                         dbDriverChar, 
                                         paste0("Select *, time_stamp::character varying ts From collection order by row asc"))
      collectionTable$time_stamp = collectionTable$ts
      collectionTable$ts         =  NULL
      #colnames(collectionTable)[colnames(collectionTable) == "ts"] = "time_stamp"
   }
   colnames(collectionTable)[colnames(collectionTable) == "echoid"] = "echoID"
   colnames(collectionTable)[colnames(collectionTable) == "protocolid"] = "protocolID"
   
   # rename "old" features and remove unused feature columns in collectionTable
   featureCols                                 = match(names(collectionTable), featureNames$feature)
   colNames                                    = paste(featureNames$feature, featureNames$featureNames, sep = ".")
   colNames[is.na(featureNames$featureNames) ] = NA
   names(collectionTable)[!is.na(featureCols)] = colNames [stats::na.omit(featureCols)]
   collectionTable                             = collectionTable[!is.na(names(collectionTable))]
   
   names(collectionTable)[names(collectionTable) == "mtr_fact" ]                   = "mtr_factor_old"
   names(collectionTable)[names(collectionTable) == "statistical_classification" ] = "statistical_classification_old"
   
   return(collectionTable)
}