Pull temperature data from the database

temperature_data

# table references
tbl_locations <- tbl(db, 'locations') %>%
rename(location_id=id, location_name=name, location_description=description) %>%
select(-created_at, -updated_at)
tbl_series <- tbl(db, 'series') %>%
rename(series_id=id) %>%
select(-created_at, -updated_at)
tbl_variables <- tbl(db, 'variables') %>%
rename(variable_id=id, variable_name=name, variable_description=description) %>%
select(-created_at, -updated_at)
tbl_values <- tbl(db, 'values') %>%
rename(value_id=id)
 
# fetch temperature data
df_values <- left_join(tbl_series,
select(tbl_variables, variable_id, variable_name),
by=c('variable_id'='variable_id')) %>%
select(-file_id) %>%
filter(location_id %in% df_locations$location_id,
variable_name=="TEMP") %>%
left_join(tbl_values,
by=c('series_id'='series_id')) %>%
left_join(select(tbl_locations, location_id, location_name, latitude, longitude, featureid=catchment_id),
by=c('location_id'='location_id')) %>%
left_join(tbl_agencies,
by=c('agency_id'='agency_id')) %>%
collect %>%
mutate(datetime=with_tz(datetime, tzone='EST'))
summary(df_values)
 
# create temperatureData input dataset
temperatureData <- select(df_values, location_id, agency_name, location_name, latitude, longitude, featureid, variable_name, datetime, value, flagged) %>%
mutate(agency_name=factor(agency_name),
location_name=factor(location_name),
variable_name=factor(variable_name),
date = as.Date(datetime))
summary(temperatureData)
 
testdf <- temperatureData %>%
group_by(location_id, date) %>%
dplyr::summarise(day_site = n())