reload `bcfishpass` object to get correct modelling for Sandstone
Closed this issue · 5 comments
going to load the .fdb as the quickest route. it has issues (duplicates and missing columns but st seems fine so should work).
# get the crossings layer from aws until we get set up with a stable db. file was downloaded from https://bcfishpass.s3.us-west-2.amazonaws.com/crossings.fgb (see dff-2022/background_layers.sh)
crossings <- sf::st_read('~/Downloads/crossings.fgb',
query = "select * from crossings WHERE watershed_group_code IN ('MORR', 'ZYMO', 'KISP')")
bcfishpass <- crossings %>%
distinct(aggregated_crossings_id, .keep_all = T)
then load to sqlite. will hash out above before saving to avoid confusion later
looks like the fdb will not work because of mismatch of columns with the xref_bcfishpass_names object . These object needs to be rebuilt with the latest .dump file.
Will need to load s3://bcfishpass/bcfishpass.dump.2023-07-19 to PG_DB_DO (production server)
Don't want to mess with "dev server" because it is currently functional as a backup. Prod is way old now
too many steps to take on today dealing with server so will load dump file to local pg database and rebuild objects that way
Error in `select()`:
! Can't subset columns that don't exist.
✖ Columns `all_spawning_km`, `all_rearing_km`, and `all_spawningrearing_km` don't exist.
hmm. might need to pull a full columns regardless and then fdb may work all_* columns should go.
.dump file from simon needs to be regenerated
will test
need to drop the geometry column in the bcfishpass object created from .fdb because fpr_table_bcfp was choking on it!
|........................... | 21% (tab-culvert-bcfp-197379) Quitting from lines 1297-1298 (Skeena2022.Rmd) ata-197379)
Error in `dplyr::mutate()`:
ℹ In argument: `Potential = as.numeric(Potential)`.
Caused by error:
! 'list' object cannot be coerced to type 'double'
found by breaking pipes in the function and viewing resulting object
> test_this <- function (dat = bcfishpass, xref_table = xref_bcfishpass_names,
+ site = my_site, col = stream_crossing_id, ...)
+ {
+ df <- dat %>% dplyr::mutate(across(where(is.numeric), round,
+ 1)) %>% dplyr::filter({
+ {
+ col
+ }
+ } == site) %>% distinct({
+ {
+ col
+ }
+ }, .keep_all = T)
+ tab_results_left <- xref_table %>% dplyr::filter(id_side ==
+ 1) %>% dplyr::arrange(id_join)
+ tab_pull_left <- df %>% select(dplyr::pull(tab_results_left,
+ bcfishpass)) %>% t() %>% as.data.frame() %>% tibble::rownames_to_column()
+ left <- left_join(tab_pull_left, xref_table, by = c(rowname = "bcfishpass"))
+ tab_results_right <- xref_table %>% dplyr::filter(id_side ==
+ 2)
+ tab_pull_right <- df %>% dplyr::select(dplyr::pull(tab_results_right,
+ bcfishpass)) %>% t() %>% as.data.frame() %>% tibble::rownames_to_column()
+ right <- left_join(tab_pull_right, xref_table, by = c(rowname = "bcfishpass"))
+ tab_joined <- left_join(dplyr::select(left, report, V1, id_join),
+ dplyr::select(right, report, V1, id_join), by = "id_join") %>%
+ select(-id_join) %>% purrr::set_names(c("Habitat", "Potential",
+ "remove", "Remediation Gain"))
+ # dplyr::mutate(Potential = as.numeric(Potential),
+ # `Remediation Gain` = as.numeric(`Remediation Gain`)) %>%
+ # dplyr::mutate(`Remediation Gain (%)` = round(`Remediation Gain`/Potential *
+ # 100, 0), Habitat = stringr::str_replace_all(Habitat,
+ # "Ha", "(ha)"), Habitat = stringr::str_replace_all(Habitat,
+ # "Km", "(km)"), Habitat = stringr::str_replace_all(Habitat,
+ # "Lakereservoir", "Lake and Reservoir"), Habitat = stringr::str_replace_all(Habitat,
+ # "Spawningrearing ", "Spawning and Rearing ")) %>%
+ # dplyr::select(-remove)
+ tab_joined
+
+ }
> test_this()
Habitat Potential remove Remediation Gain
1 ST Network (km) 0.8 ST Below Barriers Network (km) 0.8
2 ST Lake Reservoir (ha) 0 ST Below Barriers Lake Reservoir (ha) 0
3 ST Wetland (ha) 0 ST Below Barriers Wetland (ha) 0
4 ST Slopeclass03 Waterbodies (km) 0 ST Below Barriers Slopeclass03 Waterbodies (km) 0
5 ST Slopeclass03 (km) 0.1 ST Below Barriers Slopeclass03 (km) 0.1
6 ST Slopeclass05 (km) 0 ST Below Barriers Slopeclass05 (km) 0
7 ST Slopeclass08 (km) 0.7 ST Below Barriers Slopeclass08 (km) 0.7
8 ST Spawning (km) 0.1 ST Spawning Below Barriers (km) 0.1
9 ST Rearing (km) 0.8 ST Rearing Below Barriers (km) 0.8
10 CH Spawning (km) 0.1 CH Spawning Below Barriers (km) 0.1
11 CH Rearing (km) 0.1 CH Rearing Below Barriers (km) 0.1
12 CO Spawning (km) 0.1 CO Spawning Below Barriers (km) 0.1
13 CO Rearing (km) 0.1 CO Rearing Below Barriers (km) 0.1
14 CO Rearing (ha) 0 CO Rearing Below Barriers (ha) 0
15 SK Spawning (km) 0 SK Spawning Below Barriers (km) 0
16 SK Rearing (km) 0 SK Rearing Below Barriers (km) 0
17 SK Rearing (ha) 0 SK Rearing Below Barriers (ha) 0
18 <NA> 945309.7, 1020251.5 <NA> 945309.7, 1020251.5