ruut/.Rhistory

db_is_shema_exist(schema = "x_matsalu")
db_create_new_schema <- function(conf) {
conn <- db_connect()
all_schemas <- DBI::dbGetQuery(conn,sprintf(
"-- DROP SCHEMA x_matsalu;
CREATE SCHEMA %s AUTHORIZATION %s;", conf$schema, conf$user))
}
db_create_new_schema(conf)
styler:::style_active_file()
db_create_new_schema <- function(conf) {
conn <- db_connect()
DBI::dbExecute(conn, sprintf(
"-- DROP SCHEMA x_matsalu;
CREATE SCHEMA %s AUTHORIZATION %s;", conf$schema, conf$user
))
}
conf <- ruut::get_config()
conf
db_create_new_schema <- function(conf) {
conn <- db_connect()
DBI::dbExecute(conn, sprintf(
"-- DROP SCHEMA x_matsalu;
CREATE SCHEMA %s AUTHORIZATION %s;", conf$schema, conf$user
))
}
db_create_new_schema(conf)
conf$schema <- "x_valga"
db_create_new_schema(conf)
source(paste0(getwd(), "/R/db_all_schemas"))
source(paste0(getwd(), "/R/db_all_schemas.R"))
paste0(getwd(), "/R/db_all_schemas.R")
source(paste0(getwd(), "/R/db_all_schemas.R"))
db_is_shema_exist(schema = "x_matsalu")
db_is_shema_exist(schema = "x_matsalu")
db_is_shema_exist(schema = "zx_matsalu")
styler:::style_active_file()
styler:::style_active_file()
db_create_new_schema <- function(conf) {
source(paste0(getwd(), "/R/db_is_shema_exist.R"))
if(db_is_shema_exist(schema)) {
conn <- db_connect()
DBI::dbExecute(conn, sprintf(
"-- DROP SCHEMA x_matsalu;
CREATE SCHEMA %s AUTHORIZATION %s;", conf$schema, conf$user
))
}
}
conf <- ruut::get_config()
conf$schema <- "x_valga"
db_create_new_schema(conf)
conf <- ruut::get_config()
conf$schema <- "x_valga1"
db_create_new_schema(conf)
conf <- ruut::get_config()
conf
conf$schema <- "x_valga1"
conf
db_create_new_schema(conf)
conf$schema
db_create_new_schema <- function(conf) {
source(paste0(getwd(), "/R/db_is_shema_exist.R"))
if(!db_is_shema_exist(conf$schema)) {
conn <- db_connect()
DBI::dbExecute(conn, sprintf(
"-- DROP SCHEMA x_matsalu;
CREATE SCHEMA %s AUTHORIZATION %s;", conf$schema, conf$user
))
}
}
conf <- ruut::get_config()
conf$schema <- "x_valga1"
db_create_new_schema(conf)
conf <- ruut::get_config()
conf$schema <- "x_valga1"
db_create_new_schema(conf)
library(ruut)
conf <- ruut::get_config()
conf
conf$schema <- "x_valga1"
db_create_new_schema(conf)
db_create_new_schema <- function(conf) {
source(paste0(getwd(), "/R/db_is_shema_exist.R"))
if(!db_is_shema_exist(conf$schema)) {
conn <- db_connect()
DBI::dbExecute(conn, sprintf(
"-- DROP SCHEMA x_matsalu;
CREATE SCHEMA %s AUTHORIZATION %s;", conf$schema, conf$user
))
}
}
db_create_new_schema(conf)
DBI::dbDisconnect(conn)
conf <- ruut::get_config()
conf$schema <- "x_valga1"
db_create_new_schema(conf)
conf <- ruut::get_config()
conf$schema <- "x_valga1"
db_create_new_schema(conf)
conf <- ruut::get_config()
conf$schema <- "x_valga"
db_create_new_schema(conf)
conf <- ruut::get_config()
conf$schema <- "x_valga"
db_create_new_schema(conf)
conf <- ruut::get_config()
conf$schema <- "x_valga"
db_create_new_schema(conf)
conf <- ruut::get_config()
conf$schema <- "x_valga"
db_create_new_schema(conf)
styler:::style_active_file()
library(ruut)
styler:::style_active_file()
library(ruut)
styler:::style_active_file()
library(ruut)
library(ruut)
library(ruut)
library(ruut)
library(ruut)
ogr2ogr --long-usage
system("ogr2ogr --long-usage")
library(ruut)
library(ruut)
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)