In jacciz/shiny_wisdot_crash_dashboard: WisDOTcrashdashboard

ppl %>% filter(CRSHDATE >= "07-01-2020",WISINJ == "Fatal Injury") %>% count(ROLE)
library(magrittr)
library(dplyr)
pool <- pool::dbPool(RSQLite::SQLite(), dbname = "inst/app/www/crash_db.db")

crash_df <-DBI::dbReadTable(pool, "2017crash")
person_df <-DBI::dbReadTable(pool, "2017person")
# crash_df %>% mutate(CRSHDATE = lubridate::mdy(CRSHDATE))

# library(magrittr)
# crash <- wisdotcrashdatabase::import_db_data("C:/CSV/csv_from_sas/fst/", "crash", years = "19")

crash %>% dplyr::mutate(crash_location = dplyr::case_when(
  RLTNTRWY == "Non Trafficway - Parking Lot" ~ "parking lot",
  CRSHLOC %in% c("Private Property","Tribal Land") ~ "private property",
  INTTYPE == "Not At Intersection" ~ "non-intersection",
  INTTYPE != "" ~ "intersection",
  INTDIS > 0 ~ "non-intersection",
  TRUE ~ "intersection"
)) %>% dplyr::select(RLTNTRWY, CRSHLOC, INTTYPE, INTDIS , crash_location) %>% dplyr::count(crash_location)
# 2019: 41743 intersection, 14299 parking lot, 2780 private property
all_flags %>% count(intersection_flag)
all_flags %>% distinct(CRSHNMBR)

crash_dt <- dplyr::tbl(pool, "2020crash") %>% dtplyr::lazy_dt() #%>% filter(CNTYCODE %in% 40, CRSHSVR %in% "Fatal") 
microbenchmark::microbenchmark(
dplyr::tbl(pool, "2020crash") %>% filter(CNTYCODE %in% 40, CRSHSVR %in% "Fatal") %>% as.data.frame(), # 45439 microseconds
crash_dt %>% filter(CNTYCODE %in% 40, CRSHSVR %in% "Fatal") # 535
)

microbenchmark::microbenchmark(
  dplyr::tbl(pool, "2020crash") %>% dtplyr::lazy_dt()
)