data-raw/HYDAT_internal_data/tinyhydat_proc.R

In bcgov/tidyhydat: Extract and Tidy Canadian 'Hydrometric' Data

# Copyright 2017 Province of British Columbia
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and limitations under the License.


## Load in packages
library(purrr)
library(dplyr)
library(dbplyr)

## Create a subset of the data
hydat_con <- DBI::dbConnect(RSQLite::SQLite(), file.path(hy_dir(), "Hydat.sqlite3"))

all_tables <- DBI::dbListTables(hydat_con)

# table_vector <- DBI::dbListTables(hydat_con)
# Don't need all the tables. I will export what I need for testing
table_vector <- c(
  "ANNUAL_INSTANT_PEAKS", "ANNUAL_STATISTICS",
  "DLY_FLOWS", "DLY_LEVELS", "SED_DLY_LOADS", "SED_DLY_SUSCON", "SED_SAMPLES", "SED_SAMPLES_PSD",
  "STATIONS", "STN_REGULATION", "STN_REMARKS", "STN_DATUM_CONVERSION", "STN_DATA_RANGE", "STN_DATA_COLLECTION",
  "STN_OPERATION_SCHEDULE", "STN_DATUM_UNRELATED"
)

## List of tables with STATION_NUMBER INFORMATION
list_of_small_tables <- table_vector %>%
  map(~ tbl(src = hydat_con, .) %>%
    filter(STATION_NUMBER %in% c(
      "08MF005", "08NM083", "08NE102",
      "05AA008", "05HD008"
    )) %>%
    head(2000) %>%
    collect()) %>%
  set_names(table_vector)

## All tables without STATION_NUMBER
no_stn_table_vector <- all_tables[!all_tables %in% table_vector]

list_of_no_stn_tables <- no_stn_table_vector %>%
  map(~ tbl(src = hydat_con, .) %>%
    head(50) %>%
    collect()) %>%
  set_names(no_stn_table_vector)

SED_DATA_TYPES <- dplyr::tbl(hydat_con, "SED_DATA_TYPES") %>% collect()

DBI::dbDisconnect(hydat_con)

## Create the new smaller database
createIndex <- TRUE

db_path <- "./inst/test_db/tinyhydat.sqlite3"

con <- DBI::dbConnect(RSQLite::SQLite(), db_path)

## Add tables to mini database
imap(table_vector, ~ {
  DBI::dbWriteTable(con, .x, list_of_small_tables[[.x]], overwrite = TRUE)
})
imap(no_stn_table_vector, ~ {
  DBI::dbWriteTable(con, .x, list_of_no_stn_tables[[.x]], overwrite = TRUE)
})

DBI::dbDisconnect(con)

## Check to make sure the tables that I want are in the db
testdb <- DBI::dbConnect(RSQLite::SQLite(), db_path)

## Check to make sure all the tables from HYDAT are in the test db
all(DBI::dbListTables(testdb) %in% all_tables) == TRUE

DBI::dbDisconnect(con)