estmap/R/maaamet_aadressandmed.R

#' Maa-ameti aadressandmed
#'
#' Source: \url{https://xgis.maaamet.ee/adsavalik/valjav6te/}. Andmed salvestatakse postgisi andmebaasi. Schema = 'maamet'. Koniguratsiooni muutmiseks muuda konfiguratsiooni. Muutujate vaikeväärtused on sellised, et ei ole vaja midagi muuta. Kehtivad ja ootel olekus aadressiobjektid ning nendega seotud aadressid. Kui objektil on enam kui 1 aadress, siis esineb ta väljavõttes enam kui 1 kord. Samuti esinevad aadressid väljavõttes mitu korda, kui nad on seotud mitme objektiga.
#'
#' ADOB_LIIK:
#' Aadressiobjekti liigid (Kood, Nimetus, UnikInit, taseOrig, register)
#' - MK	maakond           1	maaregister
#' - OV	omavalitsus   	  2	maaregister
#' - AY	asustusüksus    	3	maaregister
#' - LO	linnaosa    		  3	maaregister
#' - VK	väikekoht   		  4	kohanimeregister
#' - LP	liikluspind   	  5	kohanimeregister
#' - CU	maaüksus		      6,7	maaregister
#' - EE	elukondlik hoone	6,7	ehitisregister
#' - ETAKME	mitteelukondlik hoone	6,7	ehitisregister
#' - ETAKER	eluruum   		8	ehitisregister
#' - ADSMR	mitteeluruum	8	ehitisregister
#' - ADS
#' @importFrom magrittr %>%
#' @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
#' ## Not run:
#' ##
#' ## maaamet_aadressandmed()
#' ##
#' ## End(**Not run**)
maaamet_aadressandmed <- function(conf = NULL) {
  ans <- utils::askYesNo("Do you want to import aadressandmed into database? It takes 2 hours!")
  if (!ans) {
    cat("\n------------------------\n")
    cat("Kaardikihte ei lisatud.")
    cat("\n------------------------\n")
  }
  if (ans) {
    # Temp directory
    tmp_dir <- "/tmp/maaamet_aadressandmed"
    if (!dir.exists(tmp_dir)) {
      dir.create(tmp_dir)
    }
    # Download link
    url <- "https://xgis.maaamet.ee/adsavalik/valjav6te/"
    # Estonia shapefile (ZIP archive)
    map_shapefile <- "aadressandmed.zip"
    # Download and save
    # Aadressandmete õige lingi tuvastamine
    adsavalik <- jsonlite::fromJSON(url)
    url_download <- sprintf("%s%s", url, adsavalik$fail[which(adsavalik$vvnr == 1 & is.na(adsavalik$kov))])
    saveTo <- sprintf("%s/%s", tmp_dir, map_shapefile)
    if (!file.exists(saveTo)) {
      utils::download.file(
        url = url_download,
        destfile = saveTo, method = "curl", extra = "-L"
      )
    }
    # Unzip
    utils::unzip(saveTo, overwrite = T, exdir = tmp_dir)
    # 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"))
    # Export to postgis
    if (is.null(conf)) {
      conf <- ruut::get_config()
      conf$schema <- "maaamet"
      conf$table <- "aadressandmed"
    }
    # Multi layer: paneme kõik csv failid üheks failiks.
    # test <- read.csv(file = "/tmp/maaamet_aadressandmed/1_4022021_01505_3.csv",
    #                  nrows = 10, sep = ";", dec = ",", stringsAsFactors = F)
    # test$ADS_KEHTIV <- as.POSIXct(strptime(test$ADS_KEHTIV, "%d.%m.%Y %H:%M:%S"))
    #
    # write.csv(x = test, file = "/tmp/maaamet_aadressandmed/xxxx.csv", row.names = F)
    # testx <- read.csv(file = "/tmp/maaamet_aadressandmed/xxxx.csv", nrows = 10,sep = ",")

    # Versioon 1 kokkupakkimisest
    # vignette("readr")
    # vignette("locales")
    dataset <- list.files(
      path = "/tmp/maaamet_aadressandmed",
      # pattern = "*.csv",
      pattern = "*505*",
      full.names = T
    ) %>%
      purrr::map_df(~ readr::read_csv2(.,
        col_types = readr::cols(
          TASE3_KOOD = readr::col_character(),
          TASE4_KOOD = readr::col_skip(),
          TASE4_NIMETUS = readr::col_skip(),
          TASE4_NIMETUS_LIIGIGA = readr::col_skip(),
          TASE5_KOOD = readr::col_skip(),
          TASE5_NIMETUS = readr::col_skip(),
          TASE5_NIMETUS_LIIGIGA = readr::col_skip(),
          TASE6_KOOD = readr::col_skip(),
          TASE6_NIMETUS = readr::col_skip(),
          TASE6_NIMETUS_LIIGIGA = readr::col_skip(),
          TASE7_KOOD = readr::col_skip(),
          TASE7_NIMETUS = readr::col_skip(),
          TASE7_NIMETUS_LIIGIGA = readr::col_skip(),
          TASE8_KOOD = readr::col_skip(),
          TASE8_NIMETUS = readr::col_skip(),
          TASE8_NIMETUS_LIIGIGA = readr::col_skip(),
          HOONE_OID = readr::col_skip()
        ),
        skip = 0,
        col_names = T,
        na = character(),
        quote = "\"",
        # trim_ws = TRUE,
        # n_max = 20,
        locale = readr::locale(
          date_names = "et", # date_names_langs()
          date_format = "%d.%m.%Y %H:%M:%S",
          time_format = "%H:%M:%S",
          decimal_mark = ",",
          grouping_mark = "",
          tz = "UTC",
          encoding = "ISO-8859-4",
          asciify = FALSE
        ) # readr::default_locale()
      ))
    # write.csv(x = dataset, file = "/tmp/maaamet_aadressandmed/xxxx.csv", row.names = F)
    # Versioon 2 kokkupakkimisest
    # dataset <- do.call("rbind", lapply(ls_long[1:5], FUN = function(files) {
    #   read.csv(files, sep = ";", dec = ",", stringsAsFactors = F, nrows = 2)
    # }))
    saveTo <- sprintf("%s/%s", tmp_dir, "aadressandmed.csv")
    utils::write.csv(dataset, file = saveTo, row.names = F, na = "")

    if (file.exists(saveTo)) {
      # New schema
      ruut::db_create_new_schema(conf = conf)
      ## Export to postgis database.
      conf$table <- "aadressandmed"
      pg <- ruut::construct_ogr2ogr_PG_connect_str()
      cmd <- sprintf(
        paste0(
          "ogr2ogr -overwrite --config PG_USE_COPY YES -f PostgreSQL ",
          "%s -lco SCHEMA=%s -lco OVERWRITE=yes -skipfailures -nln %s \"%s/%s\""
        ), pg, conf$schema, conf$table, tmp_dir, "aadressandmed.csv"
      )
      # cmd <- sprintf(
      #   paste0(
      #     "export PGCLIENTENCODING=ISO-8859-4; ",
      #     "ogr2ogr -overwrite --config PG_USE_COPY YES --config  PGCLIENTENCODING ISO-8859-4 -f PostgreSQL ",
      #     "%s -lco SCHEMA=%s -lco OVERWRITE=yes -skipfailures -nln %s \"%s/%s\""
      #   ), pg, conf$schema, conf$table, tmp_dir, "aadressandmed.csv"
      # )
      cat(cmd)
      # -overwrite vs. -append  -progress
      # -sql ST_Transform(ST_SetSRID(ST_MakePoint(viitepunkt_x::double precision, viitepunkt_y::double precision),3301),4326) as geometry
      system(cmd)
      # Add geometry column
      conn <- ruut::db_connect()
      # Import source CSV
      # q <- sprintf("
      # COPY %s.%s FROM '%s/%s' DELIMITER ';' CSV header ENCODING 'utf8';",
      #              conf$schema, conf$table, tmp_dir, "aadressandmed.csv")
      # DBI::dbExecute(conn, q)
      q <- sprintf(
        "
      -- ALTER TABLE %s.%s DROP COLUMN geometry; \
      ALTER TABLE %s.%s ADD geometry geometry DEFAULT NULL; \
      UPDATE %s.%s SET viitepunkt_x=NULL where viitepunkt_x=''; \
      UPDATE %s.%s SET viitepunkt_y=NULL where viitepunkt_y=''; \
      ALTER TABLE %s.%s ALTER COLUMN viitepunkt_x TYPE double precision USING viitepunkt_x::double precision; \
      ALTER TABLE %s.%s ALTER COLUMN viitepunkt_y TYPE double precision USING viitepunkt_y::double precision; \
      ALTER TABLE %s.%s ALTER COLUMN ads_kehtiv TYPE date USING ads_kehtiv::date;",
        conf$schema, conf$table, conf$schema, conf$table,
        conf$schema, conf$table, conf$schema, conf$table,
        conf$schema, conf$table, conf$schema, conf$table,
        conf$schema, conf$table
      )
      cat(q)
      DBI::dbExecute(conn, q)
      # Insert data into geometry
      q <- sprintf(
        "
      UPDATE %s.%s as a SET geometry =subquery.geom \
      FROM (\
        SELECT adob_id, \
        ST_Transform(ST_SetSRID(ST_MakePoint(viitepunkt_x::double precision, \
        viitepunkt_y::double precision),3301),4326) as geom \
        FROM %s.%s a \
      ) AS subquery \
      WHERE a.adob_id=subquery.adob_id;",
        conf$schema, conf$table, conf$schema, conf$table
      )
      cat(q)
      DBI::dbExecute(conn, q)

      # ogr2ogr help.
      # system("ogr2ogr --long-usage")
    }
    # Delete temp directory
    system(sprintf("rm -rf %s/*.csv", tmp_dir))
  }
}