Central Valley Enhanced

Acoustic Tagging Project

logo 
library(leaflet)
library(maps)
library(htmlwidgets)
library(leaflet.extras)
library(tidyverse)
#library(gsheet)
library(googlesheets4)
library(janitor)
library(lubridate)
library(dbplyr)
library(dplyr)
library(DBI)
library(odbc)
library(data.table)

setwd(paste(file.path(Sys.getenv("USERPROFILE"),"Desktop",fsep="\\"), "\\Real-time data massaging\\products", sep = ""))
latest <- read.csv("latest_download.csv", stringsAsFactors = F)$x

# Create connection with cloud database
con <- dbConnect(odbc(),
                 Driver = "SQL Server",
                 Server = "calfishtrack-server.database.windows.net",
                 Database = "realtime_detections",
                 UID = "realtime_user",
                 PWD = "Pass@123",
                 Port = 1433)

# Use dbplyr to load realtime_locs and qryHexCodes sql table
gen_locs <- tbl(con, "realtime_locs") %>% collect()
# gen_locs <- read.csv("realtime_locs.csv", stringsAsFactors = F)

# Read deployment data from 2021 CAT spreadsheet -------------------------------

url <- 'https://docs.google.com/spreadsheets/d/1_0OZQ3gpdKPRR-DaZCP69fDIuyWKS2a-s5xOxLPW-Oc/edit?gid=1590088234#gid=1590088234'

jsats_deploy_dat <- read_sheet(url, skip = 2)
jsats_locs <- read_sheet(url, skip = 1, sheet = "Receiver Locations")

jsats_deploy_dat <- merge(jsats_deploy_dat,jsats_locs[,c("Location","GeneralLocation","Lat","Lon","rkm")], by.x = "LOCATION", by.y = "Location", all.x = T)

# Rename columns, skip first line, remove empty lines
jsats_deploy_dat <- jsats_deploy_dat %>% 
  select(
    general_location = "GeneralLocation",
    station_id = "LOCATION",
    hydrophone_lat = "HYDROPHONE_LATITUDE",
    hydrophone_lon = "HYDROPHONE_LONGITUDE",
    shore_lat = "SHORE_POINT_LATITUDE",
    shore_lon = "SHORE_POINT_LONGITUDE",
    archive_lat = "Lat",
    archive_lon = "Lon",
    batt_start_time = "BattStart_DateTime",
    start_time = "IN_WATER_DATETIME",
    end_time = "OUT_WATER_DATETIME",
    #rec_make = "SYSTEM_MAKE",
    #rec_model = "SYSTEM_MODEL",
    RiverKM = "rkm",
    recv = "INSTRUMENT_SERIAL_NO.",
    include = "DEPLOYED? ACTIVELY COLLECTING DATA? (yes or no)"
  ) # %>% 
  # #slice(2:n()) %>% 
  # filter((include == "Yes" | include == "Y" | include == "y" | include == "yes")) %>%
  # select(!include)

# Fix date times ----------------------------------------------------------

# Parse the date times using the different formats expressed in the column
# Change lat/lon cols to numeric
jsats_deploy_dat <- jsats_deploy_dat %>% 
  mutate(
    across(
      .cols = c(hydrophone_lat, hydrophone_lon, shore_lat, shore_lon),
      .fns = as.numeric
    )
  )

if(is.POSIXct(jsats_deploy_dat$start_time)==F){
  jsats_deploy_dat <- jsats_deploy_dat %>% 
  mutate(
    start_time = parse_date_time(start_time,
                                       orders = c("mdy", "mdy HM", "mdy HMSp","mdy HMS", "ymd HM")),
    end_time = parse_date_time(end_time,
                                   orders = c("mdy", "mdy HM", "mdy HMSp","mdy HMS", "ymd HM"))
  )
}

jsats_deploy_dat <- jsats_deploy_dat %>% 
  # If hydrophone lat/lon isn't empty use those, otherwise use shore lat/lon
  # Filter out NA start times
  mutate(
    lat = ifelse(!is.na(hydrophone_lat), hydrophone_lat, shore_lat),
    lon = ifelse(!is.na(hydrophone_lon), hydrophone_lon, shore_lon),
    StartTime = as.character(start_time),
    EndTime = as.character(end_time),
    EndTime = ifelse(is.na(EndTime), "Active", EndTime),
    #EndTime = "Active",
    Site_Name = station_id,
    recv = recv
  ) %>% 
  filter(
    !is.na(start_time)
  )



deploy <- jsats_deploy_dat %>% 
  # Do this again, and adopt archived lat lons if still NA
  # Filter out NA lat,  or lon
  mutate(
    lat = ifelse(!is.na(lat), lat, archive_lat),
    lon = ifelse(!is.na(lon), lon, archive_lon),
  ) %>% 
  filter(
    !is.na(lat),
    !is.na(lon)
  )

# deploy[is.na(deploy$EndTime), "EndTime"] <- "Active"
# deploy$Site_Name <- deploy$station_id
# deploy <- deploy[!(is.na(deploy$start_time) | is.na(deploy$lat) | is.na(deploy$lon)),]

deploy$realtime <- F
deploy[deploy$recv %in% unique(gen_locs$recv),"realtime"] <- T
## Since people continuously forget negative in front of lon and it messes up map
deploy$lon <- -abs(deploy$lon)

library(DT)

deploy$Latitude <- round(deploy$lat,3)
deploy$Longitude <- round(deploy$lon,3)
deploy_print <- deploy[,c("Site_Name", "general_location","recv",   "Latitude", "Longitude", "RiverKM", "StartTime", "EndTime")]

tot_deploy <- nrow(deploy[deploy$EndTime == "Active",])

deploy_print <- deploy_print[order(deploy_print$StartTime, decreasing = T),]

#deploy$RiverKM <- round(deploy$RiverKM, 1)


Data current as of 2025-05-16 10:00:00. Updates occur hourly. All times in Pacific Standard Time.


Receiver Deployments


Completed and active JSATS receiver deployments since 2024-03-06:


Map of Receiver Sites



Total currently active individual receivers: 277



For questions or comments, please contact