JaneSullivan-NOAA
commented
1 year ago notes and draft code for these queries... what a mess.
akfin <- connect(db = "akfin")
db = akfin
sp = 'Spiny dogfish'
yr = 1998
# iphc raw data queries ----
# raw set data with halibut catch data (iphc.fiss_set_p_halb_bu is old and
# contains fewer cols)
dplyr::tbl(db, dplyr::sql('iphc.fiss_set_p_halb')) %>%
dplyr::rename_with(tolower)
# raw non-halibut catch data
dplyr::tbl(db, dplyr::sql('iphc.fiss_non_p_halb')) %>%
dplyr::rename_with(tolower)
# metadata for raw data - translation between iphc's website data column names
# and those on akfin, along with descriptions
dplyr::tbl(db, dplyr::sql('iphc.fiss_metadata_akfin_names')) %>%
dplyr::rename_with(tolower)
# iphc processed data queries ----
# metadata for iphc fisheries independent setline survey (fiss) cpue and
# relative population numbers (rpns) available on
# \href{https://psmfc.sharefile.com/share/view/s1f6d3167ef3f47c88f55ce8991f9a9d0}{akfin}
# a summary of methods used to generate iphc fiss cpue and rpn methods is
# available on
# \href{https://psmfc.sharefile.com/share/view/s1c1726d52d7545f0830e3c3b251c857a}{akfin}
# iphc fiss cpue and rpn indices are only available for 16 species:
# 1 Arrowtooth flounder
# 2 Big skate
# 3 Sleeper shark
# 4 ABA skate complex
# 5 Greenland turbot
# 6 Spiny dogfish
# 7 Yelloweye rockfish
# 8 REBS
# 9 Longnose skate
# 10 Shortspine thornyhead
# 11 Pacific cod
# 12 Common sharks
# 13 Sablefish
# 14 Shortraker rockfish
# 15 Lingcod
# 16 Redbanded rockfish
# cleaned iphc survey catch data
dplyr::tbl(db, dplyr::sql('afsc_host.fiss_cleaned')) %>%
dplyr::rename_with(tolower) %>% colnames
# iphc cpue
# iphc fiss cpue data are available with the following 'area_combo' x 'area' combinations:
# 1 FMP (with Inside waters) BSAI
# 2 FMP (with Inside waters) CAN
# 3 FMP (with Inside waters) GOA
# 4 FMP (with Inside waters) WC
# 5 FMP (without Inside waters) BSAI
# 6 FMP (without Inside waters) CAN
# 7 FMP (without Inside waters) GOA
# 8 FMP (without Inside waters) WC
# 9 NMFS management area AI
# 10 NMFS management area BS
# 11 NMFS management area CAN
# 12 NMFS management area CGOA
# 13 NMFS management area EGOA
# 14 NMFS management area INSIDE
# 15 NMFS management area WC
# 16 NMFS management area WGOA
dplyr::tbl(db, dplyr::sql('afsc_host.fiss_cpue')) %>%
dplyr::rename_with(tolower) %>%
dplyr::distinct(area_combo, area) %>%
dplyr::arrange(area_combo) %>%
dplyr::collect()
# iphc fiss rpn data are available with the following fmpsubarea and depth strata definitions
# dplyr::tbl(db, dplyr::sql('afsc_host.fiss_rpn')) %>%
# dplyr::rename_with(tolower) %>%
# distinct(fmp_sub_area, rpn_strata) %>%
# arrange(fmp_sub_area) %>%
# collect() %>%
# print(n=Inf)
# 1 AI AI0
# 2 AI AI100
# 3 AI AI200
# 4 AI AI300500
# 5 BS BS0
# 6 BS BS100
# 7 BS BS200400
# 8 BS BS400600
# 9 CGOA CG0
# 10 CGOA CG100
# 11 CGOA CG200
# 12 CGOA CG300500
# 13 EY/SE EY/SE0
# 14 EY/SE EY/SE100
# 15 EY/SE EY/SE200
# 16 EY/SE EY/SE300500
# 17 WGOA WG0
# 18 WGOA WG100
# 19 WGOA WG200
# 20 WGOA WG300500
# 21 WY WY0
# 22 WY WY100
# 23 WY WY200
# 24 WY WY300500
# if the user wants rpns at the rpn level:
dplyr::tbl(db, dplyr::sql('afsc_host.fiss_rpn')) %>%
dplyr::rename_with(tolower) %>%
dplyr::select(species, year = survey_year, rpn_strata,
fmp_subarea = fmp_sub_area, n_stations,
n_pos_catch, boot_strata_rpn,
boot_lci, boot_uci, akfin_load_date) %>%
dplyr::filter(species %in% sp,
year <= yr)
# if the user wants rpns at the fmpsubarea level:
dplyr::tbl(db, dplyr::sql('afsc_host.fiss_rpn')) %>%
dplyr::rename_with(tolower) %>%
dplyr::rename(year = survey_year,
fmp_subarea = fmp_sub_area) %>%
dplyr::filter(species %in% sp,
year <= yr) %>%
dplyr::group_by(species, year, fmp_subarea, fmp_rpn, fmp_lci, fmp_uci, akfin_load_date) %>%
dplyr::summarise(n_stations = sum(n_stations, na.rm = TRUE),
n_pos_catch = sum(n_pos_catch, na.rm = TRUE))
IPHC data available on AKFIN:
Lengths from IPHC special collections: these are not currently available on AKFIN, but we've talked about archiving them on AKFIN (relevant for spiny dogfish and pcod, maybe for yelloweye rockfish)