ruut/R/db_repair_tabel_geometry.R

#' Postgis andmebaasi vigase geomeetria parandamine
#'
#' Funktsioon parandab andmebaasi vigase geomeetria. Olemasolevale andmebaasile listakse ette laiend 'old_'. Parnadatud geomeetriaga failiga kirjutatakse olemasolev fail üle.
#' @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()], [ruut::copy_qgis_object_to_db()]
#' @keywords postgis, gpkg
#' @export
#' @examples
#' \dontrun{
#'
#' conf <- ruut::get_config()
#' # conf$schema <- "maaamet"
#' # conf$table <- "maakond"
#' # conf$table <- "omavalitsus"
#' # conf$table <- "omavalitsusvalispiirini"
#' conf$table <- "asustusyksus"
#' db_repair_tabel_geometry(conf = conf)
#' }
db_repair_tabel_geometry <- function(conf = conf) {
  ## Algorithm
  # ruut::qgis_algorithm_search_by_word(str = "fix")
  # algorithm <- "native:fixgeometries"
  postgres <- sprintf(
    "postgres://dbname=\'%s\' host=%s port=%s user=\'%s\' sslmode=%s password=\'%s\' key=\'id\' srid=3301 type=Polygon checkPrimaryKeyUnicity=\'1\' ",
    conf$dbname, conf$host, conf$port, conf$user, conf$sslmode, conf$password
  )
  ## Fix geometries
  cmd <- sprintf("qgis_process run native:fixgeometries --INPUT='%s table=\"%s\".\"%s\" (geom)' --OUTPUT='%s table=\"%s\".\"tmp_%s\" (geom)'", postgres, conf$schema, conf$table, postgres, conf$schema, conf$table)
  system(cmd)
  conn <- ruut::db_connect()
  ## Rename
  q <- sprintf("
  ALTER TABLE %s.%s RENAME TO old_%s; \
  drop table if exists %s.%s cascade; \
  ALTER TABLE %s.tmp_%s RENAME TO %s;", conf$schema, conf$table, conf$table, conf$schema, conf$table, conf$schema, conf$table, conf$table)
  cat(q)
  DBI::dbExecute(conn, q)
}