estmap/R/statistika_csv.R

#' Statistikaameti REL andmed CSV kujul ja import andmebaasi.
#'
#' Statistikaamet csv andmete sidumine andmebaasiga 'rel_1x1km'.  Source: \url{https://estat.stat.ee/StatistikaKaart/VKR}. Lae sealt käsitsi alla 1x1 km andmestiku shp fail. Andmed salvestatakse postgisi andmebaasi. Schema = 'statistikaamet'. Koniguratsiooni muutmiseks muuda konfiguratsiooni. Muutujate vaikeväärtused on sellised, et ei ole vaja midagi muuta.
#' @param conf A list() of configuration variables. Default values \code{\link[ruut]{get_config}}.
#' @return No output.
#' @seealso [ruut::get_config()], [ruut::copy_shp_to_db()]
#' @keywords postgis, maps, ESRI Shpfile, OSM
#' @export
#' @examples
#' \dontrun{
#'
#' conf <- ruut::get_config()
#' statistika_csv(conf = conf)
#' }
statistika_csv <- function(conf = NULL) {
  ## --------------------- muutujad ja teisendused -----------------------------
  vars <- ajutised_muutujad(conf = conf)
  conf <- vars$conf

  # conf$schema <- "minu_teed"
  # conf$table <- "zzz"
  # output <- ruut::construct_to_gpkg_output_postgres_str(conf = conf, geometry_field = "geom")
  # cat(paste(paste0('"', ruut::db_table_colnames(conf = conf)$column_name, '"'), collapse = ","))

  # Directory for csv files.
  tmp_dir <- sprintf("%s/tmp/%s", system.file(package = "estmap"), "statistika_csv")
  if (!dir.exists(tmp_dir)) {
    dir.create(tmp_dir)
  }


  ## -------- 1.1 Kontrollime andmebaasis vajalike tabelite olemasolu ----------
  conf$schema <- "statistikaamet"
  conf$table <- "rel_1x1km"

  tbl_names <- ruut::db_schema_tablenames(conf = conf)
  if (!all(c("rel_1x1km") %in% tbl_names)) {
    cat("\n------------------\nPuuduvad vajalikud andmebaasitabelid.\n")
    return(NULL)
  }

  # List of files
  ls <- list.files(path = tmp_dir, pattern = ".csv")
  ls_long <- list.files(path = tmp_dir, pattern = ".csv", full.names = T)
  tbl_names <- unlist(strsplit(x = ls, split = ".csv"))
  tbl_names <- tolower(gsub("_[(]Ruudustik_1_x_1_km_2011[)]", "", tbl_names))
  tbl_names <- gsub("__", "_", tbl_names)
  tbl_names <- gsub("-", "_", tbl_names)
  tbl_names <- gsub("[(]", "", tbl_names)
  tbl_names <- gsub("[)]", "", tbl_names)
  tbl_names <- gsub(" ", "", tbl_names)
  tbl_names <- gsub("\u00E4", "a", tbl_names) #ä
  tbl_names <- gsub("^([0-9])", "n\\1", tbl_names) # postgis'i tabeli nimi ei tohi alata numbriga

  # Connect db
  conn <- ruut::db_connect()

  for (i in 1:length(tbl_names)) {
    ## -------------------- 1.1 Copy csv file to postgis -----------------------
    conf$table <- tbl_names[i]
    conf$table <- "ajutine"
    PG <- ruut::construct_ogr2ogr_PG_connect_str(conf = conf)
    # ogr2ogr oskab csv faili lugeda juhul kui laiend on csv.
    cmd <- sprintf("ogr2ogr -f PostgreSQL %s -lco SCHEMA=%s -lco OVERWRITE=yes -lco FID=%s -nln \"%s\" \"%s\"", PG, conf$schema, "id", conf$table, ls_long[i])
    system(cmd)
    postgis_tbl_colnames <- ruut::db_table_colnames(conf = conf)

    # Connect db
    conn <- ruut::db_connect()
    ## --------------- 1.2 Kopeerime väärtused põhitabelisse  ------------------
    # Drop column
    conf$table <- "rel_1x1km"
    q <- sprintf("ALTER TABLE \"%s\".\"%s\" DROP COLUMN  IF EXISTS %s;", conf$schema, conf$table, tbl_names[i])
    cat(q)
    DBI::dbExecute(conn, q)
    # # Drop column
    conf$table <- "rel_1x1km"
    q <- sprintf("ALTER TABLE \"%s\".\"%s\" DROP COLUMN  IF EXISTS %s;", conf$schema, conf$table, "value")
    cat(q)
    DBI::dbExecute(conn, q)
    # Rename columns
    conf$table <- "ajutine"
    q <- sprintf("ALTER TABLE \"%s\".\"%s\" RENAME COLUMN \"%s\" TO \"%s\";", conf$schema, conf$table, postgis_tbl_colnames[3, "column_name"], "value")
    cat(q)
    DBI::dbExecute(conn, q)
    Sys.sleep(1)
    conf$table <- "ajutine"
    q <- sprintf("ALTER TABLE \"%s\".\"%s\" RENAME COLUMN \"%s\" TO \"%s\";", conf$schema, conf$table, postgis_tbl_colnames[2, "column_name"], "kood")
    cat(q)
    DBI::dbExecute(conn, q)
    # Set '<4' to '3'
    conf$table <- "ajutine"
    q <- sprintf("UPDATE  \"%s\".\"%s\" SET value='3' WHERE value='<4';", conf$schema, conf$table)
    cat(q)
    DBI::dbExecute(conn, q)
    # Lisame uue veeru baastabelisse
    conf$table <- "rel_1x1km"
    q <- sprintf("ALTER TABLE \"%s\".\"%s\" ADD value int4 NULL DEFAULT 0;", conf$schema, conf$table)
    cat(q)
    DBI::dbExecute(conn, q)
    # Kopeerime andmed
    conf$table <- "rel_1x1km"
    q <- sprintf("UPDATE \"%s\".\"%s\" as r \
    SET value = CAST(a.value AS INT) \
    FROM statistikaamet.ajutine a \
    WHERE r.kood = a.kood;", conf$schema, conf$table)
    cat(q)
    DBI::dbExecute(conn, q)
    # Muudame veeru nime
    conf$table <- "rel_1x1km"
    q <- sprintf("ALTER TABLE \"%s\".\"%s\" RENAME COLUMN value TO %s;", conf$schema, conf$table, tbl_names[i])
    cat(q)
    DBI::dbExecute(conn, q)
  }
  # Kustutame ajutise tabeli.
  conf$table <- "ajutine"
  q <- sprintf("drop table if exists %s.%s cascade;", conf$schema, conf$table)
  cat(q)
  DBI::dbExecute(conn, q)
}