#' 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")