#' Andmebaasi loomine ja ühendamine
#'
system("ogr2ogr --long-usage")

str <- " { 'FIELD' : 'code', 'INPUT' : '/data/gpkg/artiklid/artikkel_210127_valga_matsalu_lahemaa/mikihiir.gpkg|layername=piir_landuse_a', 'OPERATOR' : 0, 'OUTPUT' : 'ogr:dbname=\'/data/gpkg/artiklid/artikkel_210127_valga_matsalu_lahemaa/mikihiir.gpkg\' table=\"piir_landuse_a_residential\" (geom)', 'VALUE' : '7203' }"
ruut::construct_qgis_output_result_to_better_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)

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

## --------------- testi algus 'ruudu' funktsioonidele ------------------
geojson_txt <- paste('{"type":"MultiPoint","coordinates":[[658300,6474800],[658300,6475000],[658700,6476000],[659600,6475000],[659600,6474800],[658300,6474800]]}')
# read geojson from string:
# geojson_txt <- paste('{"type":"MultiPoint","coordinates":[[658300,6474800],
# [658300,6475000],[658400,6475100],[658600,6475000],[658600,6474800],
# [658300,6474800]]}')
x <- sf::read_sf(geojson_txt) %>% sf::st_cast("POLYGON")
sf::st_crs(x) <- 3301
colnames(x)
sf::st_geometry(x) %>% graphics::plot()

obj <- "pk_marja"
conf$schema <- obj
## postgisi loomine
ruut::pk_sellest_alustame_db_loomist(pk = x, obj = obj)
# Layers list
ruut::db_schema_tablenames(conf = conf)
## ruudustike lisamine
ruut::pk_lisame_ruudustikud(obj = obj)
## polügoonide lisamine ja teisendamine
ruut::pk_lisame_polygoonid(obj = obj)
ruut::pk_teisendame_polygoone(obj = obj)
## joonte lisamine ja teisendamine
ruut::pk_lisame_jooned(obj = obj)
ruut::pk_teisendame_jooni(obj = obj)
# punktide lisamine ja teisendamine
ruut::pk_lisame_punktid(obj = obj)
ruut::pk_teisendame_punkte(obj = obj)



gpkg_home <- "/data/gpkg/artiklid/artikkel_210127_valga_matsalu_lahemaa"
dsn <- sprintf("%s/%s.gpkg", gpkg_home, obj)
# Layers list
sf::st_layers(dsn = dsn)



## Loeme andmebaasist piiri ja piirikasti.
# Layers list
gpkg_info <- sf::st_layers(dsn = dsn)
layer_names <- gpkg_info$name
for (layer_name in layer_names) {
  cat(sprintf("\nFailist %s laeti alla: %s", dsn, layer_name))
  assign(layer_name, sf::read_sf(dsn = dsn, layer = layer_name))
}
## Test plot
sf::st_geometry(bb_epk02t_grid) %>% graphics::plot(border = 0, lwd = 0.3, col = "#ffffff") # valge
sf::st_geometry(bb) %>% graphics::plot(add = T, border = 3, lwd = 0.3, col = "#d3fffb")
sf::st_geometry(piir) %>% graphics::plot(add = T, border = 3, lwd = 0.3, col = "#a3fffb")

sf::st_geometry(bb_epk02t_grid) %>% graphics::plot(border = 0, lwd = 0.3, col = "#ffffff") # valge
sf::st_geometry(bb) %>% graphics::plot(add = T, border = 3, lwd = 0.3, col = "#d3fffb")
sf::st_geometry(bb_epk02t_grid) %>% graphics::plot(add = T, border = 3, lwd = 0.3)
sf::st_geometry(bb_shp_katastriyksus) %>% graphics::plot(add = T, border = 2, lwd = 0.3)

sf::st_geometry(bb_epk02t_grid) %>% graphics::plot(border = 0, lwd = 0.3, col = "#ffffff") # valge
sf::st_geometry(piir) %>% graphics::plot(add = T, border = 3, lwd = 0.3, col = "#d3fffb")
sf::st_geometry(bb_epk02t_grid) %>% graphics::plot(add = T, border = 3, lwd = 0.3)
sf::st_geometry(piir_shp_katastriyksus) %>% graphics::plot(add = T, border = 2, lwd = 0.3)

# Layers list
sf::st_layers(dsn = dsn)


y <- unique(as.data.frame(sf::read_sf(dsn = dsn, layer = "piir_aadressandmed", as_tibble = T))[, c("adob_liik"), drop = F])
nrow(y)
y$adob_liik

## ------------------ testi lõpp -------------------






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

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

conf$schema <- "xxx_artikkel_210127"
source("geomeetria_teisendused/pk_piir.R")
gpkg_home <- "/data/gpkg/artiklid/artikkel_210127_valga_matsalu_lahemaa"

for (i in 1:length(objektid)) {
  #   ## ---------------- 1. piirkonna piir ------------------
  ## Muutujad: pk - piirkond
  obj <- objektid[i]
  x <- pk_piir(obj = obj)
  sf::st_geometry(x) %>% plot()
  gpkg_home <- "/data/gpkg/artiklid/artikkel_210127_valga_matsalu_lahemaa"
  ## GPKG loomine
  ruut::gpkg_sellest_alustame_gpkg_loomist(pk = x, obj = obj, gpkg_home = gpkg_home)
  # Layers list
  dsn <- sprintf("%s/%s.gpkg", gpkg_home, obj)
  sf::st_layers(dsn = dsn)
  ## GPKG ruudustike lisamine
  ruut::gpkg_piirkonnale_ruudustike_lisamine(obj = obj, gpkg_home = gpkg_home)
  ## GPKG polügoonide lisamine
  ruut::gpkg_piirkonnale_polygoonide_lisamine(obj = obj, gpkg_home = gpkg_home)
  ## GPKG joonte lisamine
  ruut::gpkg_piirkonnale_joonte_lisamine(obj = obj, gpkg_home = gpkg_home)
  # GPKG punktide lisamine
  ruut::gpkg_piirkonnale_punktide_lisamine(obj = obj, gpkg_home = gpkg_home)
}


obj = obj; pk = pk; gpkg_home = gpkg_home; obj_only = TRUE
m <- piirkonnale_maatriksi_loomine(obj = obj, pk = pk, gpkg_home = gpkg_home, obj_only = TRUE)
m_filename <- sprintf("minu_%s_maatriks.csv", obj)
write.table(m, file = m_filename, sep = ";", row.names = T)
y <- read.table(m_filename, header = TRUE, sep = ";")
unlink(m_filename) # Kustuta fail





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