210127_artikkel/00_algandmed.R

#' Andmebaasi loomine ja ühendamine
#'

str <- ""
ruut::construct_qgis_output_result_to_beter_format(str = str)

source("functions/delete_existing_variables.R")

library(dplyr)
library(qgisprocess)
library(sf)

par(oma = c(0, 0, 0, 0)) # outer margin
par(mar = c(0, 0, 0, 0) + 0.0)

source("01_funktsioonid.R")

# Uue schema loomine
conf <- ruut::get_config()
conf$schema <- "xxx_artikkel_210127"
ruut::db_create_new_schema(conf = conf)


# Objektide nimekiri
objektid <- c("valga", "matsalu", "lahemaa")

# ----------------- Loe piirkond (objekt) -----------------
## Piirkonna 'pk'
conn <- ruut::db_connect(conf = conf)
## Valitud objekti indeks
i <- 1

for (i in 1:length(objektid)) {
  ## ---------------- 1. piirkonna piir ------------------
  ## Muutujad: pk - piirkond
  obj <- objektid[i]
  pk <- pk_piir(obj = obj)
  sf::st_geometry(pk) %>% plot()
  gpkg_home <- "/data/gpkg/artiklid/artikkel_210127_valga_matsalu_lahemaa"
  dsn <- sprintf("%s/%s.gpkg", gpkg_home, obj)
  input_layer_name <- "piir"
  input_layer <- sprintf("%s|layername=%s", dsn, input_layer_name)
  output_layer_name <- "boundarybox_3301"
  tmp_gpkg_file <- tempfile(fileext = ".gpkg")
  # write to gpkg
  class(pk)
  sf::write_sf(pk,
    dsn = dsn,
    layer = "piir", driver = "gpkg", append = FALSE
  )

  ## ------------ 2. piirkonna 3301 projektsiooniga piirikast --------------
  ## 2.1  Esmalt leiame milliset 500x500 meetri ruutudega on objekt kaetud.
  # ruut::qgis_algorithm_search_by_word(str = "grid")
  algorithm <- "native:creategrid"
  # cat(qgisprocess::qgis_show_help(algorithm = algorithm))
  result <- qgisprocess::qgis_run_algorithm(
    algorithm = algorithm,
    CRS = "EPSG:3301",
    # EXTENT = '25.454305528,26.259893095,59.454118579,59.714621582 [EPSG:4326]',
    EXTENT = input_layer,
    HOVERLAY = 0,
    HSPACING = 500,
    TYPE = 2,
    VOVERLAY = 0,
    VSPACING = 500,
    OUTPUT = qgisprocess::qgis_tmp_file(ext = ".gpkg")
    # .quiet = TRUE
  )
  result
  pk_grid <- sf::read_sf(qgisprocess::qgis_output(result, "OUTPUT"))
  # sf::st_geometry(pk_grid) %>% plot()
  ## 2.2 Leitud ruutudele leiame piirikasti.
  algorithm <- "qgis:minimumboundinggeometry"
  # cat(qgisprocess::qgis_show_help(algorithm = algorithm))
  result <- qgisprocess::qgis_run_algorithm(
    algorithm = algorithm,
    FIELD = "",
    INPUT = pk_grid,
    TYPE = 3,
    OUTPUT = tmp_gpkg_file
    # .quiet = TRUE
  )
  result
  pk_grid_bb <- sf::read_sf(qgisprocess::qgis_output(result, "OUTPUT"))
  sf::st_geometry(pk_grid_bb) %>% plot(add = T)
  system(sprintf("ogr2ogr -f GPKG -overwrite  %s %s -nln %s", dsn, tmp_gpkg_file, output_layer_name))

  ## ------------- 3. piirkonna epk10t (5x5 km) ruudud --------------------
  # 3.1 kogu ruutvõrgustik
  ruudud <- c("epk10t_grid", "epk2t_grid", "epk02t_grid")
  j <- 4
  for (j in 1:length(ruudud)) {
    ruut <- ruudud[j]
    output_layer_name <- ruut
    conf <- ruut::get_config()
    pg <- ruut::construct_ogr2ogr_PG_connect_str(conf = conf)
    input <- sprintf(
      'postgres://dbname=\'%s\' host=%s port=%s user=\'%s\' sslmode=%s password=\'%s\' key=\'fid\' srid=4326 type=Polygon checkPrimaryKeyUnicity=\'1\' table=\"%s\".\"%s\" (geometry)',
      conf$dbname, conf$host, conf$port, conf$user, conf$sslmode, conf$password,
      "maaamet", ruut
    )
    # ruut::qgis_algorithm_search_by_word(str = "extract")
    algorithm <- "native:extractbylocation"
    # cat(qgisprocess::qgis_show_help(algorithm = algorithm))
    result <- qgisprocess::qgis_run_algorithm(
      algorithm = algorithm,
      INPUT = input,
      INTERSECT = sprintf("%s|layername=%s", dsn, "boundarybox_3301"),
      OUTPUT = tmp_gpkg_file,
      PREDICATE = c(0, 1)
      # .quiet = TRUE
    )
    result
    assign(ruut, sf::read_sf(qgisprocess::qgis_output(result, "OUTPUT")))
    system(sprintf("ogr2ogr -f GPKG -overwrite  %s %s -nln %s", dsn, tmp_gpkg_file, output_layer_name))
  }
  sf::st_geometry(epk10t_grid) %>% plot(add = T)

  # 3.2 ainult piirkonnaga seotud ning informatsiooni sisaldav ruutvõrgustik
  ruudud <- c("epk10t", "epk2t")
  j <- 4
  for (j in 1:length(ruudud)) {
    ruut <- ruudud[j]
    output_layer_name <- ruut
    conf <- ruut::get_config()
    pg <- ruut::construct_ogr2ogr_PG_connect_str(conf = conf)
    input <- sprintf(
      'postgres://dbname=\'%s\' host=%s port=%s user=\'%s\' sslmode=%s password=\'%s\' key=\'fid\' srid=4326 type=Polygon checkPrimaryKeyUnicity=\'1\' table=\"%s\".\"%s\" (geometry)',
      conf$dbname, conf$host, conf$port, conf$user, conf$sslmode, conf$password,
      "maaamet", ruut
    )
    # ruut::qgis_algorithm_search_by_word(str = "extract")
    algorithm <- "native:extractbylocation"
    # cat(qgisprocess::qgis_show_help(algorithm = algorithm))
    result <- qgisprocess::qgis_run_algorithm(
      algorithm = algorithm,
      INPUT = input,
      INTERSECT = input_layer,
      OUTPUT = tmp_gpkg_file,
      PREDICATE = c(0, 1)
      # .quiet = TRUE
    )
    result
    assign(ruut, sf::read_sf(qgisprocess::qgis_output(result, "OUTPUT")))
    system(sprintf("ogr2ogr -f GPKG -overwrite  %s %s -nln %s", dsn, tmp_gpkg_file, output_layer_name))
  }
  sf::st_geometry(epk10t) %>% plot(add = T)
  ## ---------------------- vaata layer'id ----------------------
  # Vaata layer'eid
  sf::st_layers(dsn = dsn)
}







conn <- ruut::db_connect()
q <- sprintf("SELECT * FROM %s.%s_bb", "xxx_artikkel_210127", obj)
cat(sprintf("\n-----------------\n%s\n\n", q))
pk_bb_3301 <- sf::st_read(conn, query = q)
sf::st_geometry(pk) %>% plot()
sf::st_geometry(pk_bb_3301) %>% plot(add = T)

## 3. piirkonna epk10t ruudud
# 3.1 kogu ruutvõrgustik
epk10t_grid <- pk_epk10t_grid(obj = objektid[i])
# 3.1 ainult piirkonna ruutvõrgustik
epk10t <- pk_epk10t(obj = objektid[i])
sf::st_geometry(epk10t_grid) %>% plot(border = 3, lwd = 0.3, col = "#d3fffb")
sf::st_geometry(epk10t) %>% plot(add = T, border = 3, lwd = 0.3, col = "#a3fffb")
sf::st_geometry(pk) %>% plot(add = T)

# 4 piirkonna epk10t kaardiruutude nimekiri ortofotode allalaadimiseks
epk10t_nr <- pk_epk10t_ruutude_nimekiri(objektid[i])

## 5. piirkonna epk2t ruudud
# 3.1 kogu ruutvõrgustik
epk2t_grid <- pk_epk2t_grid(obj = objektid[i])
# 3.1 ainult piirkonna ruutvõrgustik
epk2t <- pk_epk2t(obj = objektid[i])
sf::st_geometry(epk2t_grid) %>% plot(border = 3, lwd = 0.3, col = "#d3fffb")
sf::st_geometry(epk2t) %>% plot(add = T, border = 3, lwd = 0.3, col = "#a3fffb")
sf::st_geometry(pk) %>% plot(add = T)




## -------------- Muud ruudustikega seotud demo joonised ----------------
## 2. Kaardiruudustiku epk200t (100x100km) piirikast
conn <- ruut::db_connect()
q <- sprintf("SELECT * FROM %s.%s", "maaamet", "epk200t_bb")
cat(sprintf("\n-----------------\n%s\n\n", q))
epk200t_bb <- sf::st_read(conn, query = q)
sf::st_geometry(epk200t_bb) %>% plot()

## 3. Kaardiruudustiku epk200t (100x100km)
conn <- ruut::db_connect()
q <- sprintf("SELECT * FROM %s.%s", "maaamet", "epk200t")
cat(sprintf("\n-----------------\n%s\n\n", q))
epk200t <- sf::st_read(conn, query = q)
sf::st_geometry(epk200t) %>% plot(add = T, border = 3, lwd = 0.3, col = "#d3fffb")