ruut/.Rhistory

library(ruut)
styler:::style_active_file()
styler:::style_active_file()
system("ogr2ogr --long-usage")
styler:::style_active_file()
styler:::style_active_file()
library(ruut)
#'
#' INSPIRE grid map
#'
library(dplyr)
library(ruut)
# Source: https://www.eea.europa.eu/data-and-maps/data/eea-reference-grids-2
# Temp directory
tmp_dir <- tempdir()
# Download link
url <- "https://www.eea.europa.eu/data-and-maps/data/eea-reference-grids-2/gis-files/estonia-shapefile/download"
# Estonia shapefile (ZIP archive)
map_shapefile <- "Estonia_shapefile.zip"
# Download and sav
saveTo <- sprintf("%s/%s", tmp_dir, map_shapefile)
download.file(
url = download_url,
destfile = saveTo, method = "curl", extra = "-L"
)
# Unzip
unzip(saveTo, overwrite = T, exdir = tmp_dir)
# List of files
ls <- list.files(path = tmp_dir, pattern = ".shp")
ls_long <- list.files(path = tmp_dir, pattern = ".shp", full.names = T)
tbl_names <- strsplit(x = ls, split = ".shp") %>% unlist()
# Export to postgis
schema <- "inspire"
i <- 1
config <- get_config()
config$schema <- schema
# New schema
db_create_new_schema(conf = config)
library(dplyr)
library(ruut)
# Source: https://www.eea.europa.eu/data-and-maps/data/eea-reference-grids-2
# Temp directory
tmp_dir <- tempdir()
# Download link
url <- "https://www.eea.europa.eu/data-and-maps/data/eea-reference-grids-2/gis-files/estonia-shapefile/download"
# Estonia shapefile (ZIP archive)
map_shapefile <- "Estonia_shapefile.zip"
# Download and sav
saveTo <- sprintf("%s/%s", tmp_dir, map_shapefile)
download.file(
url = url,
destfile = saveTo, method = "curl", extra = "-L"
)
# Unzip
unzip(saveTo, overwrite = T, exdir = tmp_dir)
# List of files
ls <- list.files(path = tmp_dir, pattern = ".shp")
ls_long <- list.files(path = tmp_dir, pattern = ".shp", full.names = T)
tbl_names <- strsplit(x = ls, split = ".shp") %>% unlist()
# Export to postgis
schema <- "inspire"
i <- 1
config <- get_config()
config$schema <- schema
# New schema
db_create_new_schema(conf = config)
print(tbl_names[i])
config$table <- tbl_names[i]
source <- sprintf('"%s" "%s"', tmp_dir, tbl_names[i])
crs_source <- "-s_srs EPSG:3035"
crs_target <- "-t_srs EPSG:4326"
fid <- "fid"
ruut::copy_shp_to_db(dir = tmp_dir, layer = tbl_names[i], conf = config,
id = fid, crs_source = crs_source, crs_target = crs_target,
geometry_type = 'POLYGON')
dir = tmp_dir; layer = tbl_names[i]; conf = config;
id = fid; crs_source = crs_source; crs_target = crs_target;
geometry_type = 'POLYGON'
length(list.files(path = dir, pattern = sprintf("%s*", layer)))
length(list.files(path = dir, pattern = sprintf("%s*", layer))) > 0
source <- sprintf('"%s" "%s"', dir, layer)
source
cmd <- sprintf(
paste0(
"ogr2ogr -progress --conf PG_USE_COPY YES -f PostgreSQL ",
"PG:\" dbname='%s' host=%s port=%d user='%s' password='%s' ",
"sslmode=%s active_schema=%s \" -lco DIM=2 %s -overwrite -nlt GEOMETRY ",
"-lco GEOMETRY_NAME=geometry -lco FID=%s -nln %s.%s ",
"%s %s -nlt %s"
),
conf$dbname, conf$host, conf$port, conf$user, conf$password,
conf$sslmode, conf$schema, source, id, conf$schema, conf$table,
crs_source, crs_target, geometry_type
)
cmd
system(cmd)
dir = tmp_dir; layer = tbl_names[i]; conf = config;
id = fid; crs_source = crs_source; crs_target = crs_target;
geometry_type = 'PROMOTE_TO_MULTI'
cmd <- sprintf(
paste0(
"ogr2ogr -progress --conf PG_USE_COPY YES -f PostgreSQL ",
"PG:\" dbname='%s' host=%s port=%d user='%s' password='%s' ",
"sslmode=%s active_schema=%s \" -lco DIM=2 %s -overwrite -nlt GEOMETRY ",
"-lco GEOMETRY_NAME=geometry -lco FID=%s -nln %s.%s ",
"%s %s -nlt %s"
),
conf$dbname, conf$host, conf$port, conf$user, conf$password,
conf$sslmode, conf$schema, source, id, conf$schema, conf$table,
crs_source, crs_target, geometry_type
)
cmd
ruut::db_create_new_schema(conf)
system(cmd)
cmd <- sprintf(
paste0(
"ogr2ogr -progress --config PG_USE_COPY YES -f PostgreSQL ",
"PG:\" dbname='%s' host=%s port=%d user='%s' password='%s' ",
"sslmode=%s active_schema=%s \" -lco DIM=2 %s -overwrite -nlt GEOMETRY ",
"-lco GEOMETRY_NAME=geometry -lco FID=%s -nln %s.%s ",
"%s %s -nlt PROMOTE_TO_MULTI"
),
config$dbname, config$host, config$port, config$user, config$password,
config$sslmode, config$schema, source, fid, config$schema, config$table,
crs_source, crs_target
)
cmd
system(cmd)
geometry_type = 'POLYGON'
cmd <- sprintf(
paste0(
"ogr2ogr -progress --config PG_USE_COPY YES -f PostgreSQL ",
"PG:\" dbname='%s' host=%s port=%d user='%s' password='%s' ",
"sslmode=%s active_schema=%s \" -lco DIM=2 %s -overwrite -nlt GEOMETRY ",
"-lco GEOMETRY_NAME=geometry -lco FID=%s -nln %s.%s ",
"%s %s -nlt %s"
),
config$dbname, config$host, config$port, config$user, config$password,
config$sslmode, config$schema, source, fid, config$schema, config$table,
crs_source, crs_target, geometry_type
)
cmd
system(cmd)
system("ogr2ogr --long-usage")
cmd <- sprintf(
paste0(
"ogr2ogr -progress --config PG_USE_COPY YES -f PostgreSQL ",
"PG:\" dbname='%s' host=%s port=%d user='%s' password='%s' ",
"sslmode=%s active_schema=%s \" -lco DIM=2 %s -overwrite -nlt GEOMETRY ",
"-lco GEOMETRY_NAME=geometry -lco FID=%s -nln %s.%s ",
"-s_srs%s -t_srs -nlt %s"
),
config$dbname, config$host, config$port, config$user, config$password,
config$sslmode, config$schema, source, fid, config$schema, config$table,
crs_source, crs_target, geometry_type
)
system(cmd)
cmd <- sprintf(
paste0(
"ogr2ogr -progress --config PG_USE_COPY YES -f PostgreSQL ",
"PG:\" dbname='%s' host=%s port=%d user='%s' password='%s' ",
"sslmode=%s active_schema=%s \" -lco DIM=2 %s -overwrite -nlt GEOMETRY ",
"-lco GEOMETRY_NAME=geometry -lco FID=%s -nln %s.%s ",
"%s %s -nlt %s"
),
config$dbname, config$host, config$port, config$user, config$password,
config$sslmode, config$schema, source, fid, config$schema, config$table,
crs_source, crs_target, geometry_type
)
system(cmd)
crs_target
crs_source
cmd
cmd <- sprintf(
paste0(
"ogr2ogr -progress --conf PG_USE_COPY YES -f PostgreSQL ",
"PG:\" dbname='%s' host=%s port=%d user='%s' password='%s' ",
"sslmode=%s active_schema=%s \" -lco DIM=2 %s -overwrite -nlt GEOMETRY ",
"-lco GEOMETRY_NAME=geometry -lco FID=%s -nln %s.%s ",
"%s %s -nlt %s"
),
conf$dbname, conf$host, conf$port, conf$user, conf$password,
conf$sslmode, conf$schema, source, id, conf$schema, conf$table,
crs_source, crs_target, geometry_type
)
cmd
crs_target
crs_source = "EPSG:4326"
cmd <- sprintf(
paste0(
"ogr2ogr -progress --conf PG_USE_COPY YES -f PostgreSQL ",
"PG:\" dbname='%s' host=%s port=%d user='%s' password='%s' ",
"sslmode=%s active_schema=%s \" -lco DIM=2 %s -overwrite -nlt GEOMETRY ",
"-lco GEOMETRY_NAME=geometry -lco FID=%s -nln %s.%s ",
"%s %s -nlt %s"
),
conf$dbname, conf$host, conf$port, conf$user, conf$password,
conf$sslmode, conf$schema, source, id, conf$schema, conf$table,
crs_source, crs_target, geometry_type
)
source('~/apps/R/packages/ruut/R/copy_shp_to_db.R', echo=TRUE)
cmd
cmd <- sprintf(
paste0(
"ogr2ogr -progress --conf PG_USE_COPY YES -f PostgreSQL ",
"PG:\" dbname='%s' host=%s port=%d user='%s' password='%s' ",
"sslmode=%s active_schema=%s \" -lco DIM=2 %s -overwrite -nlt GEOMETRY ",
"-lco GEOMETRY_NAME=geometry -lco FID=%s -nln %s.%s ",
"-s_srs %s -t_srs %s -nlt %s"
),
conf$dbname, conf$host, conf$port, conf$user, conf$password,
conf$sslmode, conf$schema, source, id, conf$schema, conf$table,
crs_source, crs_target, geometry_type
)
copy_shp_to_db <- function(dir = NULL, layer = NULL, conf = NULL, id = "fid",
crs_source = "EPSG:4326",
crs_target = "EPSG:4326",
geometry_type = "PROMOTE_TO_MULTI") {
# Command 'ogr2ogr' help
# system("ogr2ogr --long-usage")
# Kontrollime kas etteantud kataloogis paiknevad failid
if (length(list.files(path = dir, pattern = sprintf("%s*", layer))) > 0) {
source <- sprintf('"%s" "%s"', dir, layer)
cmd <- sprintf(
paste0(
"ogr2ogr -progress --conf PG_USE_COPY YES -f PostgreSQL ",
"PG:\" dbname='%s' host=%s port=%d user='%s' password='%s' ",
"sslmode=%s active_schema=%s \" -lco DIM=2 %s -overwrite -nlt GEOMETRY ",
"-lco GEOMETRY_NAME=geometry -lco FID=%s -nln %s.%s ",
"-s_srs %s -t_srs %s -nlt %s"
),
conf$dbname, conf$host, conf$port, conf$user, conf$password,
conf$sslmode, conf$schema, source, id, conf$schema, conf$table,
crs_source, crs_target, geometry_type
)
ruut::db_create_new_schema(conf)
Sys.sleep(1)
system(cmd)
} else {
cat("\nEi leitud antud kataloogist faile.")
return(NULL)
}
}
ruut::copy_shp_to_db(dir = tmp_dir, layer = tbl_names[i], conf = config,
id = fid, crs_source = crs_source, crs_target = crs_target,
geometry_type = 'POLYGON')
cmd <- sprintf(
paste0(
"ogr2ogr -progress --config PG_USE_COPY YES -f PostgreSQL ",
"PG:\" dbname='%s' host=%s port=%d user='%s' password='%s' ",
"sslmode=%s active_schema=%s \" -lco DIM=2 %s -overwrite -nlt GEOMETRY ",
"-lco GEOMETRY_NAME=geometry -lco FID=%s -nln %s.%s ",
"-s_srs %s -t_srs %s -nlt %s"
),
conf$dbname, conf$host, conf$port, conf$user, conf$password, conf$sslmode,
conf$schema, x, id, conf$schema, conf$table, crs_source, crs_target,
geometry_type
)
source <- sprintf('"%s" "%s"', dir, layer)
cmd <- sprintf(
paste0(
"ogr2ogr -progress --conf PG_USE_COPY YES -f PostgreSQL ",
"PG:\" dbname='%s' host=%s port=%d user='%s' password='%s' ",
"sslmode=%s active_schema=%s \" -lco DIM=2 %s -overwrite -nlt GEOMETRY ",
"-lco GEOMETRY_NAME=geometry -lco FID=%s -nln %s.%s ",
"-s_srs %s -t_srs %s -nlt %s"
),
conf$dbname, conf$host, conf$port, conf$user, conf$password,
conf$sslmode, conf$schema, source, id, conf$schema, conf$table,
crs_source, crs_target, geometry_type
)
cmd
system(cmd)
cmd <- sprintf(
paste0(
"ogr2ogr -progress --config PG_USE_COPY YES -f PostgreSQL ",
"PG:\" dbname='%s' host=%s port=%d user='%s' password='%s' ",
"sslmode=%s active_schema=%s \" -lco DIM=2 %s -overwrite -nlt GEOMETRY ",
"-lco GEOMETRY_NAME=geometry -lco FID=%s -nln %s.%s ",
"%s %s -nlt %s"
),
config$dbname, config$host, config$port, config$user, config$password,
config$sslmode, config$schema, source, fid, config$schema, config$table,
crs_source, crs_target, geometry_type
)
system(cmd)
source <- sprintf('"%s" "%s"', tmp_dir, tbl_names[i])
crs_source <- "EPSG:3035"
crs_target <- "EPSG:4326"
fid <- "fid"
cmd <- sprintf(
paste0(
"ogr2ogr -progress --config PG_USE_COPY YES -f PostgreSQL ",
"PG:\" dbname='%s' host=%s port=%d user='%s' password='%s' ",
"sslmode=%s active_schema=%s \" -lco DIM=2 %s -overwrite -nlt GEOMETRY ",
"-lco GEOMETRY_NAME=geometry -lco FID=%s -nln %s.%s ",
"-s_srs %s -t_srs %s -nlt %s"
),
config$dbname, config$host, config$port, config$user, config$password,
config$sslmode, config$schema, source, fid, config$schema, config$table,
crs_source, crs_target, geometry_type
)
system(cmd)
config
conf
config
cmd <- sprintf(
paste0(
"ogr2ogr -progress --config PG_USE_COPY YES -f PostgreSQL ",
"PG:\" dbname='%s' host=%s port=%d user='%s' password='%s' ",
"sslmode=%s active_schema=%s \" -lco DIM=2 %s -overwrite -nlt GEOMETRY ",
"-lco GEOMETRY_NAME=geometry -lco FID=%s -nln %s.%s ",
"-s_srs %s -t_srs %s -nlt %s"
),
config$dbname, config$host, config$port, config$user, config$password,
config$sslmode, config$schema, source, fid, config$schema, config$table,
crs_source, crs_target, geometry_type
)
system(cmd)
cmd <- sprintf(
paste0(
"ogr2ogr -progress --conf PG_USE_COPY YES -f PostgreSQL ",
"PG:\" dbname='%s' host=%s port=%d user='%s' password='%s' ",
"sslmode=%s active_schema=%s \" -lco DIM=2 %s -overwrite -nlt GEOMETRY ",
"-lco GEOMETRY_NAME=geometry -lco FID=%s -nln %s.%s ",
"-s_srs %s -t_srs %s -nlt %s"
),
conf$dbname, conf$host, conf$port, conf$user, conf$password,
conf$sslmode, conf$schema, source, id, conf$schema, conf$table,
crs_source, crs_target, geometry_type
)
system(cmd)
cmd <- sprintf(
paste0(
"ogr2ogr -progress --config PG_USE_COPY YES -f PostgreSQL ",
"PG:\" dbname='%s' host=%s port=%d user='%s' password='%s' ",
"sslmode=%s active_schema=%s \" -lco DIM=2 %s -overwrite -nlt GEOMETRY ",
"-lco GEOMETRY_NAME=geometry -lco FID=%s -nln %s.%s ",
"-s_srs %s -t_srs %s -nlt %s"
),
conf$dbname, conf$host, conf$port, conf$user, conf$password,
conf$sslmode, conf$schema, source, fid, conf$schema, conf$table,
crs_source, crs_target, geometry_type
)
system(cmd)
styler:::style_active_file()
library(ruut)
styler:::style_active_file()
styler:::style_active_file()
library(ruut)
styler:::style_active_file()
styler:::style_active_file()
library(ruut)
styler:::style_active_file()
library(ruut)
styler:::style_active_file()
styler:::style_active_file()
library(ruut)
styler:::style_active_file()
styler:::style_active_file()
styler:::style_active_file()
styler:::style_active_file()
library(ruut)
conf <- ruut::get_config()
conf
styler:::style_active_file()
library(ruut)
library(ruut)
conn <- ruut::db_connect()
config <- get_config()
config$schema <- "public"
conf = config
q <- sprintf("select t.table_name
from information_schema.tables t
where t.table_schema = '%s'
and t.table_type = 'BASE TABLE'
order by t.table_name;", conf$schema)
conf$schema
res <- DBI::dbGetQuery(conn, q)
res
res <- as.array(DBI::dbGetQuery(conn, q))
res <- DBI::dbGetQuery(conn, q)
res[,'table_name']
styler:::style_active_file()
library(ruut)
config
config$schema <- 'maaamet'
db_schema_tablenames(conf = config)
library(ruut)
styler:::style_active_file()
library(ruut)
library(ruut)
styler:::style_active_file()
construct_qgis_output_result_to_beter_format <- function(str = "") {
#' str - Processing algorithm… Algorithm '......' starting…
#' Input parameters: -> kopeerin need loogilistes sulgudes
#' olevad väärtused argumendi "str" väärtuseks.
str <- "{ 'CREATEINDEX' : True, 'DATABASE' : 'Data', 'DROP_STRING_LENGTH' : False, 'ENCODING' : 'UTF-8', 'FORCE_SINGLEPART' : False, 'GEOMETRY_COLUMN' : 'geometry', 'INPUT' : '/data/gpkg/teed/teed_l.gpkg|layername=teed_l', 'LOWERCASE_NAMES' : True, 'OVERWRITE' : True, 'PRIMARY_KEY' : 'fid', 'SCHEMA' : 'data', 'TABLENAME' : 'mikihiir' }"
str <- gsub("'", "", str)
str <- gsub(":", "=", str)
str <- gsub(" ", "", str)
str <- gsub(",", "',", str)
str <- gsub("=", "='", str)
str <- gsub("'True'", "1", str)
str <- gsub("'False'", "0", str)
str <- gsub(",", ",\n", str)
str <- gsub("[{]", "\nalgorithm=algorithm,\n", str)
str <- gsub("[}]", "'\n", str)
str <- gsub("layername='", "layername=", str)
cat(str)
str
}
str <- "{ 'CREATEINDEX' : True, 'DATABASE' : 'Data', 'DROP_STRING_LENGTH' : False, 'ENCODING' : 'UTF-8', 'FORCE_SINGLEPART' : False, 'GEOMETRY_COLUMN' : 'geometry', 'INPUT' : '/data/gpkg/teed/teed_l.gpkg|layername=teed_l', 'LOWERCASE_NAMES' : True, 'OVERWRITE' : True, 'PRIMARY_KEY' : 'fid', 'SCHEMA' : 'data', 'TABLENAME' : 'mikihiir' }"
params <- construct_qgis_output_result_to_beter_format(str = str)
styler:::style_active_file()
styler:::style_active_file()
library(ruut)
library(ruut)
styler:::style_active_file()
library(ruut)
library(ruut)
library(ruut)
styler:::style_active_file()
library(ruut)
library(ruut)
library(ruut)
library(ruut)
styler:::style_active_file()
construct_ogr2ogr_PG_connect_str()
construct_ogr2ogr_PG_connect_str <- function(gpkg_home = "/tmp", gpkg_file = "test", gpkg_table = "layer_one") {
dsn <- sprintf("%s/%s.gpkg", gpkg_home, gpkg_file)
ogr <- sprintf("ogr:dbname='%s'", dsn)
output <- sprintf('%s table=\"%s\" (geometry)', ogr, gpkg_table)
output
}
construct_ogr2ogr_PG_connect_str()
library(ruut)
styler:::style_active_file()
conf <- ruut::get_config()
PG <- sprintf(
'postgres://dbname=\'%s\' host=%s port=%s user=\'%s\' password=\'%s\' sslmode=%s key=\'id\' srid=3301 type=Polygon checkPrimaryKeyUnicity=\'1\' table=\"%s\".\"%s\" (geometry)',
conf$dbname, conf$host, conf$port, conf$user, conf$password, conf$sslmode, conf$schema, conf$table
)
PG
styler:::style_active_file()
PG
library(ruut)
conf <- ruut::get_config()
construct_to_gpkg_output_postgres_str <- function(conf = NULL) {
if (is.null(conf)) conf <- ruut::get_config()
PG <- sprintf(
'postgres://dbname=\'%s\' host=%s port=%s user=\'%s\' password=\'%s\' sslmode=%s key=\'id\' srid=3301  checkPrimaryKeyUnicity=\'1\' table=\"%s\".\"%s\" (geometry)',
conf$dbname, conf$host, conf$port, conf$user, conf$password, conf$sslmode, conf$schema, conf$table
)
PG
}
construct_to_gpkg_output_postgres_str(conf =
conf
)
conf <- ruut::get_config()
conf
library(ruut)
conf <- ruut::get_config()
conf
conf <- ruut::get_config()
construct_to_gpkg_output_file_str(conf = conf)
construct_to_gpkg_output_file_str <- function(conf = conf) {
dsn <- sprintf("%s/%s.gpkg", conf$gpkg_home, conf$gpkg_file)
ogr <- sprintf("ogr:dbname='%s'", dsn)
output <- sprintf('%s table=\"%s\" (geometry)', ogr, conf$gpkg_table)
output
}
construct_to_gpkg_output_file_str(conf = conf)
styler:::style_active_file()
library(ruut)
config <- get_config()
llibrary(ruut)
library(ruut)
config <- get_config()
config$schema <- "data"
db_schema_tablenames(conf = config)
conn <- ruut::db_connect()
q <- sprintf("select t.table_name
from information_schema.tables t
where t.table_schema = '%s'
and t.table_type = 'BASE TABLE'
order by t.table_name;", conf$schema)
res <- DBI::dbGetQuery(conn, q)
conn <- ruut::db_connect()
q <- sprintf("select t.table_name
from information_schema.tables t
where t.table_schema = '%s'
and t.table_type = 'BASE TABLE'
order by t.table_name;", conf$schema)
conf$schema
config <- get_config()
config$schema <- "data"
conf <- get_config()
conf$schema <-
"data"
conn <- ruut::db_connect()
q <- sprintf("select t.table_name
from information_schema.tables t
where t.table_schema = '%s'
and t.table_type = 'BASE TABLE'
order by t.table_name;", conf$schema)
res <- DBI::dbGetQuery(conn, q)
nrow(res)
if(nrow(res)==0) NULL else res[, "table_name"]
if(nrow(res)==0) NULL else res[, "table_name"]
styler:::style_active_file()
library(ruut)