ruut/R/pk_teisendame_polygoone.R

#' Piirkonnale andmebaasides olevate polügoonide teisendamine
#'
#' Etteantud piirkonna geomeetrilise piirjoone ('piir') ja selle joone piirikasti ('bb') järele leitakse nende aladega kaetud polügoonid. Andmed salvestatakse postgis andmebaasi.
#'
#' @param obj str Objekti nimi. Edaspidi on oluline ainult see nimi. Piirkonna geomeetrilist joont ei ole vaja lisada.
#' @param conf A list() of configuration variables. Default values \code{\link[ruut]{get_config}}.
#' @return Uute andmebaasi kihtide 'piir_...' ja 'bb_...' loomine.
#' @seealso [sf::st_read()], [sf::write_sf()],[sf::st_transform()],[ruut::pk_sellest_alustame_db_loomist()],[ruut::pk_lisame_ruudustikud()] ,[ruut::pk_lisame_polygoonid()],[ruut::pk_lisame_jooned()],[ruut::pk_lisame_punktid()],[ruut::pk_teisendame_polygoone()],[ruut::pk_teisendame_jooni()],[ruut::pk_teisendame_punkte()]
#' @keywords postgis, boundary box, EPSG:3301
#' @export
#' @examples
#' \dontrun{
#'
#' obj <- "marja"
#' pk_teisendame_polygoone(obj = obj, conf = NULL)
#'
#' # Layers list.
#' ruut::db_schema_tablenames(conf = conf)
#' }
pk_teisendame_polygoone <- function(obj = NULL, conf = NULL) {
  ## ------------- muutujad ja teisendused ---------------
  vars <- ajutised_muutujad(pk = NULL, obj, conf)
  obj <- vars$obj
  piir <- vars$pk
  conf <- vars$conf
  ## Konfiguratsiooni muutujale väärtuste omistamine, kui seda pole antud.
  if (!any("a00_piir" %in% ruut::db_schema_tablenames(conf = conf))) {
    cat("\nAndmebaas loomata. Palun funktsiooniga ruut::pk_sellest_alustame_db_loomist() andmebaasi loomist.\n")
    return(NULL)
  }

  ## ====================== teisendused =====================
  # Layers list
  layer_names <- ruut::db_schema_tablenames(conf = conf)
  layer_names <- layer_names[!layer_names %in% c("a00_bb2", "a00_piir_bb", "bb2_epk10t_grid", "bb2_epk02t_grid", "bb2_epk2t_grid")]
  for (layer_name in layer_names) {
    conn <- ruut::db_connect(conf = conf)
    try(ruumiline_obj <- sf::st_read(dsn = conn, layer = c(conf$schema, layer_name)))
    ## Kontlrollime kas geomeetriline objekt on polügoon.
    ## Arvutame sel juhul pindala.
    is_polygon <- any(grepl("polygon", tolower(attributes(ruumiline_obj$geom)$class), fixed = TRUE))
    if (is_polygon) {

      ## 1. Lõikame objekti ruudistikga tükkideks. Ühte ruutu võib jääda mitu tükki.
      ## Peame need pärast ühendama
      # ruut::qgis_algorithm_search_by_word("Intersection")
      algorithm <- "native:intersection"
      conf$table <- sprintf("%s", layer_name)
      input <- ruut::construct_to_gpkg_output_postgres_str(
        conf = conf, geometry_type = "MultiPolygon", srid = 3301,
        checkPrimaryKeyUnicity = TRUE, key = "id", geometry_field = "geom"
      )
      conf$table <- sprintf("%s", "bb2_epk02t_grid")
      overlay <- ruut::construct_to_gpkg_output_postgres_str(
        conf = conf, geometry_type = "Polygon", srid = 3301,
        checkPrimaryKeyUnicity = TRUE, key = "id", geometry_field = "geom"
      )
      output <- vars$tmp_gpkg_file_output_1 # ajutine fail
      str <- sprintf("{ 'INPUT' : '%s', 'INPUT_FIELDS' : ['fid'], 'OUTPUT' : '%s', 'OVERLAY' : '%s', 'OVERLAY_FIELDS' : [], 'OVERLAY_FIELDS_PREFIX' : '' }", input, output, overlay)
      cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm)
      system(cmd)

      ## 2. Ühendame ruudus olevad pinnad
      # ruut::qgis_algorithm_search_by_word("Dissolve")
      algorithm <- "native:dissolve"
      input <- vars$tmp_gpkg_file_input_1 # ajutine fail
      output <- vars$tmp_gpkg_file_output_2 # ajutine fail
      str <- sprintf("{ 'FIELD' : ['%s'], 'INPUT' : '%s', 'OUTPUT' : '%s' }", "id", input, output)
      cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm)
      system(cmd)

      ## 3. Lisame pindade pindalad ja perimeetri
      # ruut::qgis_algorithm_search_by_word("attributes")
      algorithm <- "qgis:exportaddgeometrycolumns"
      input <- vars$tmp_gpkg_file_input_2 # ajutine fail
      output <- vars$tmp_gpkg_file_output_1 # ajutine fail
      str <- sprintf("{ 'CALC_METHOD' : 0, 'INPUT' : '%s', 'OUTPUT' : '%s' }", input, output)
      cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm)
      system(cmd)

      ## 4. Add field
      ## Arvutame pindala protsendi ruudust
      # ruut::qgis_algorithm_search_by_word("pythonfield")
      algorithm <- "qgis:advancedpythonfieldcalculator"
      input <- vars$tmp_gpkg_file_input_1 # ajutine fail
      epk02t_table_name <- sprintf("epk02t_%s", gsub("^data_", "", layer_name))
      conf$table <- epk02t_table_name
      output <- ruut::construct_to_gpkg_output_postgres_str(conf = conf, geometry_field = "geom", geometry_type = "Polygon", srid = 3301, checkPrimaryKeyUnicity = FALSE, key = "id")
      str <- sprintf("{ 'FIELD_NAME' : 'area_pc', 'FIELD_TYPE' : 1, 'FIELD_LENGTH' : 4, 'FIELD_PRECISION' : 2, 'GLOBAL' : '', 'FORMULA' : 'value = round($geom.area()/10000,4)', 'INPUT' : '%s', 'OUTPUT' : '%s' }", input, output)
      cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm)
      system(cmd)

      ## ------------- TIFF ----------------
      ## 5. Lisame pindade pindalad ja perimeetri
      # ruut::qgis_algorithm_search_by_word("attributes")
      algorithm <- "native:joinattributesbylocation"
      conf$table <- "bb2_epk02t_grid"
      input <- ruut::construct_to_gpkg_output_postgres_str(conf = conf, geometry_field = "geom", geometry_type = "MultiPolygon", srid = 3301, checkPrimaryKeyUnicity = TRUE, key = "id")
      conf$table <- epk02t_table_name
      join <- ruut::construct_to_gpkg_output_postgres_str(conf = conf, geometry_field = "geom", geometry_type = "MultiPolygon", srid = 3301, checkPrimaryKeyUnicity = TRUE, key = "id")
      output <- vars$tmp_gpkg_file_output_1
      str <- sprintf("{ 'DISCARD_NONMATCHING' : False, 'INPUT' : '%s', 'JOIN' : '%s', 'JOIN_FIELDS' : ['area_pc'], 'METHOD' : 0, 'OUTPUT' : '%s', 'PREDICATE' : [0], 'PREFIX' : '' }", input, join, output)
      cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm)
      system(cmd)

      ## 6. Asendame NULL väärtustega 0.
      algorithm <- "qgis:advancedpythonfieldcalculator"
      input <- vars$tmp_gpkg_file_input_1 # ajutine fail
      output <- vars$tmp_gpkg_file_output_2
      cmd <- sprintf("qgis_process run %s --FIELD_NAME='value' --FIELD_TYPE=1 --FIELD_LENGTH=4 --FIELD_PRECISION=2 --GLOBAL=\"def getValue(x):
    if not x:
        value = 0
    else:
        value = x * 100
    return value\" --FORMULA='value = getValue( <area_pc> )' --INPUT='%s' --OUTPUT='%s' ", algorithm, input, output)
      system(cmd)

      ## 7. Eemaldame üleliigsed veerud
      # ruut::qgis_algorithm_search_by_word("Drop ")
      algorithm <- "native:deletecolumn"
      input <- vars$tmp_gpkg_file_input_2
      conf$table <- sprintf("grid_%s", gsub("^data_", "", layer_name))
      output <- ruut::construct_to_gpkg_output_postgres_str(conf = conf, geometry_field = "geom", geometry_type = "Polygon", srid = 3301, checkPrimaryKeyUnicity = FALSE, key = "fid")
      cmd <- sprintf("qgis_process run %s --COLUMN='area_pc' --INPUT='%s' --OUTPUT='%s' ", algorithm, input, output)
      system(cmd)

      ## 8. tif salvestamine tmp kataloogi
      grid_layer <- sf::read_sf(dsn = conn, layer = c(conf$schema, "bb2_epk02t_grid"))
      # sf::st_crs(grid_layer) <- 3301
      pk_attributes <- attributes(grid_layer$geom)
      extent <- sprintf("%s,%s,%s,%s [EPSG:3301]", round((pk_attributes$bbox["xmin"] / 100), digits = 0) * 100, ceiling((pk_attributes$bbox["xmax"] / 100)) * 100, round((pk_attributes$bbox["ymin"] / 100), digits = 0) * 100, ceiling((pk_attributes$bbox["ymax"] / 100)) * 100)
      # ruut::qgis_algorithm_search_by_word("Rasterize")
      algorithm <- "gdal:rasterize"
      # ruut::qgis_show_help(algorithm = algorithm)
      input <- ruut::construct_to_gpkg_output_postgres_str(
        conf = conf, geometry_type = "MultiPolygon", srid = 3301,
        checkPrimaryKeyUnicity = TRUE, key = "id", geometry_field = "geom"
      )
      tif_file_name <- sprintf("tif_%s", gsub("^data_", "", layer_name))
      output <- sprintf("%s/%s.tif", vars$tmp_dir, tif_file_name)
      str <- sprintf("{ 'BURN' : 0, 'DATA_TYPE' : 5, 'EXTENT' : '%s', 'EXTRA' : '-a_srs epsg:3301', 'FIELD' : 'value', 'HEIGHT' : 100, 'INIT' : None, 'INPUT' : '%s', 'INVERT' : False, 'NODATA' : -1, 'OPTIONS' : '', 'OUTPUT' : '%s', 'UNITS' : 1, 'WIDTH' : 100 }", extent, input, output)
      cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm)
      system(cmd) # Salvestame kataloogi

      ## 9. ----------- TIF TO POSTGIS -------------
      (cmd <- sprintf(
        "export PGPASSWORD=%s && raster2pgsql -d -I %s %s.%s_tif | psql -U %s -d %s -h %s -p %s",
        conf$password, output, conf$schema, tif_file_name, conf$user, conf$dbname, conf$host, conf$port
      ))
      system(cmd)
    }
  }
  ## Layers list
  conf$schema <- obj
  ruut::db_schema_tablenames(conf = conf)
  sf::st_layers(dsn = vars$tmp_gpkg_file)
}