ruut/R/pk_teisendame_punkte.R

#' Piirkonnale andmebaasides olevate punktide teisendamine
#'
#' Etteantud piirkonna geomeetrilise piirjoone ('piir') ja selle joone piirikasti ('bb') järele leitakse nende aladega kaetud punktid. 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_punkte(obj = obj, conf = NULL)
#'
#' # Layers list.
#' ruut::db_schema_tablenames(conf = conf)
#' }
pk_teisendame_punkte <- 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)
  }
  ## Tabelid mis on juba andmebaasis ja mida ümber ei kirjutata.
  olemasolevad_tabelid <- ruut::db_schema_tablenames(conf = conf)

  ## ====================== teisendused =====================
  # Layers list
  layer_names <- ruut::db_schema_tablenames(conf = conf)
  layer_names <-
    layer_names[!layer_names %in% c(
      "a00_piir",
      "a00_bb2",
      "a00_piir_bb",
      "bb2_epk10t_grid",
      "bb2_epk02t_grid",
      "bb2_epk2t_grid"
    )]
  layer_names <- layer_names[grepl("^data_p_", layer_names)]
  for (layer_name in layer_names) {
    ## Kui olemasolev tabel eksisteerib andmebaasis, siis jätame arvutused
    ## selle tabeliga vahele.
    uus_tabel <- sprintf("grid_%s", gsub("^data_", "", layer_name))
    if (uus_tabel %in% olemasolevad_tabelid) {
      cat(sprintf("\nTabel %s on juba andmebaasis olemas.\n", uus_tabel))
      next
    }
    conn <- ruut::db_connect(conf = conf)
    try(ruumiline_obj <-
          sf::st_read(dsn = conn,
                      layer = c(conf$schema, layer_name)))
    DBI::dbDisconnect(conn)
    ## ------------- Punktide arv ruudus ---------------
    ## Kontlrollime kas geomeetriline objekt on punkt.
    (is_point <-
       any(grepl(
         "point", tolower(attributes(ruumiline_obj$geom)$class), fixed = TRUE
       )))
    if (is_point) {
      ## 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("Count")
      algorithm <- "native:countpointsinpolygon"
      conf$table <- sprintf("%s", layer_name)
      points <- ruut::construct_to_gpkg_output_postgres_str(
        conf = conf,
        geometry_type = "Point",
        srid = 3301,
        checkPrimaryKeyUnicity = TRUE,
        key = "id",
        geometry_field = "geom"
      )
      conf$table <- sprintf("%s", "bb2_epk02t_grid")
      polygons <- 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(
          "{ 'CLASSFIELD' : '', 'FIELD' : 'value', 'OUTPUT' : '%s', 'POINTS' : '%s', 'POLYGONS' : '%s', 'WEIGHT' : '' }",
          output,
          points,
          polygons
        )
      cmd <-
        ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm)
      system(cmd)

      ## 2. ------------- TIFF ----------------
      conn <- ruut::db_connect(conf = conf)
      grid_layer <-
        sf::read_sf(dsn = conn,
                    layer = c(conf$schema, "bb2_epk02t_grid"))
      DBI::dbDisconnect(conn)
      # 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 <- vars$tmp_gpkg_file_input_1
      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

      ## 3. ----------- TIF TO POSTGIS -------------
      (
        cmd <- sprintf(
          "export PGPASSWORD=%s && raster2pgsql -s 3301 -d -I -C -M %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)

      ## 4. Eemaldame üleliigsed veerud ja salvestame tiff alusfaili andmebaasi
      # ruut::qgis_algorithm_search_by_word("Drop ")
      algorithm <- "native:deletecolumn"
      input <- vars$tmp_gpkg_file_input_1
      conf$table <-
        sprintf("grid_%s", gsub("^data_", "", layer_name))
      output <-
        ruut::construct_to_gpkg_output_postgres_str(
          conf = conf,
          geometry_field = "geom",
          geometry_type = "Point",
          srid = 3301,
          checkPrimaryKeyUnicity = FALSE,
          key = "id"
        )
      cmd <-
        sprintf(
          "qgis_process run %s --COLUMN='fid' --COLUMN='left' --COLUMN='top' --COLUMN='right' --COLUMN='bottom'  --INPUT='%s' --OUTPUT='%s' ",
          algorithm,
          input,
          output
        )
      system(cmd)

      ## 5. Kustutame geom veeru.
      conn <- ruut::db_connect(conf = conf)
      q <-
        sprintf("ALTER TABLE \"%s\".\"%s\" DROP COLUMN geom;",
                conf$schema,
                conf$table)
      cat(sprintf("\n-----------------\n%s\n\n", q))
      DBI::dbSendQuery(conn, q)
      ## 6. Vaakum.
      q <-
        sprintf(
          "VACUUM (FULL, FREEZE, VERBOSE, ANALYZE, INDEX_CLEANUP) \"%s\".\"%s\";",
          conf$schema,
          conf$table
        )
      cat(sprintf("\n-----------------\n%s\n\n", q))
      DBI::dbSendQuery(conn, q)
      ## 6. Add foreign key.
      q <-
        sprintf(
          "ALTER TABLE \"%s\".\"%s\" ADD CONSTRAINT %s_fk FOREIGN KEY (id) REFERENCES %s.bb2_epk02t_grid(id);",
          conf$schema,
          conf$table,
          conf$table,
          conf$schema
        )
      cat(sprintf("\n-----------------\n%s\n\n", q))
      DBI::dbSendQuery(conn, q)
      DBI::dbDisconnect(conn)
    }
  }
  ## Layers list
  conf$schema <- obj
  ruut::db_schema_tablenames(conf = conf)
  sf::st_layers(dsn = vars$tmp_gpkg_file)
}