estmap/.Rhistory

ruut::copy_shp_to_db(
dir = tmp_dir, layer = tbl_names[i], conf = conf,
id = "fid", crs_source = "EPSG:3301", crs_target = "EPSG:4326",
geometry_type = "POINT"
)
cat(sprintf(
"\nShp fail %s kopeeriti POSTGIS andmebaasi %s.%s\n\n",
ls[i], conf$schema, conf$table
))
}
tbl_names[i]
library(estmap)
conf = NULL
# Temp directory
tmp_dir <- "/tmp/gtfs"
if (!dir.exists(tmp_dir)) {
dir.create(tmp_dir)
}
# Download link
url <- "http://www.peatus.ee/gtfs/"
# Estonia shapefile (ZIP archive)
map_shapefile <- "gtfs.zip"
# Download and save
url_download <- sprintf("%s%s", url, map_shapefile)
saveTo <- sprintf("%s/%s", tmp_dir, map_shapefile)
if (!file.exists(saveTo)) {
utils::download.file(
url = url_download,
destfile = saveTo, method = "curl", extra = "-L"
)
}
# Unzip
utils::unzip(saveTo, overwrite = T, exdir = tmp_dir)
# List of files
ls <- list.files(path = tmp_dir, pattern = ".txt")
ls_long <- list.files(path = tmp_dir, pattern = ".txt", full.names = T)
tbl_names <- tools::file_path_sans_ext(list.files(path = tmp_dir, pattern = "*.txt"))
# Export to postgis
if (is.null(conf)) {
conf <- ruut::get_config()
conf$schema <- "xgtfs"
conf$table <- ""
}
conf$table <- 'shapes'
ruut::db_create_new_schema(conf = conf)
# Postgresql string
pg <- ruut::construct_ogr2ogr_PG_connect_str()
# Connect to db
conn <- ruut::db_connect()
pg
tbl_names
i=8
conf$table <- tbl_names[i]
conf
system(sprintf("mv %s %s/%s.csv", ls_long[i], tmp_dir, tbl_names[i]))
shapes <- utils::read.csv(file = sprintf("%s/%s.csv", tmp_dir, tbl_names[i]))
## --------------- Points to SpatialLines -----------------
cat("\n----------------\nMarsruutide geomeetria loomine\n")
sl <- shapes %>%
dplyr::arrange(shape_id, shape_pt_sequence) %>%
sf::st_as_sf(coords = c("shape_pt_lon", "shape_pt_lat"), agr = "constant") %>%
# dplyr::group_by(shape_id) %>%
# dplyr::summarise() %>%
sf::st_cast("POINT")
sf::st_crs(sl) <- 4326
sl <- shapes %>%
dplyr::arrange(shape_id, shape_pt_sequence) %>%
sf::st_as_sf(coords = c("shape_pt_lon", "shape_pt_lat"), agr = "constant") %>%
# dplyr::group_by(shape_id) %>%
# dplyr::summarise() %>%
sf::st_cast("POINT")
library(dplyr)
sl <- shapes %>%
dplyr::arrange(shape_id, shape_pt_sequence) %>%
sf::st_as_sf(coords = c("shape_pt_lon", "shape_pt_lat"), agr = "constant") %>%
# dplyr::group_by(shape_id) %>%
# dplyr::summarise() %>%
sf::st_cast("POINT")
sf::st_crs(sl) <- 4326
tabeli_suffix <- "_source"
sf::st_write(
obj = sl, dsn = conn, layer_options = c("GEOMETRY=AS_XY", "OVERWRITE=yes"),
layer = sprintf("%s%s", conf$table, tabeli_suffix)
)
# Change schema
q <- sprintf("
drop table if exists %s.%s%s cascade; \
ALTER TABLE %s%s SET SCHEMA %s", conf$schema, conf$table, tabeli_suffix, conf$table, tabeli_suffix, conf$schema)
cat("\n----------------\nMuudame schema.\n")
cat(q)
DBI::dbExecute(conn, q)
output <- sprintf(
'postgres://dbname=\'%s\' host=%s port=%s user=\'%s\' sslmode=%s password=\'%s\' table=\"%s\".\"%s\" (geom)',
conf$dbname, conf$host, conf$port, conf$user, conf$sslmode, conf$password,
conf$schema, conf$table
)
input <- sprintf(
'postgres://dbname=\'%s\' host=%s port=%s user=\'%s\' sslmode=%s password=\'%s\' srid=4326 type=Point checkPrimaryKeyUnicity=\'1\' table=\"%s\".\"%s_source\" (geometry)',
conf$dbname, conf$host, conf$port, conf$user, conf$sslmode, conf$password,
conf$schema, conf$table
)
result <- qgisprocess::qgis_run_algorithm(
algorithm = algorithm,
CLOSE_PATH = 0,
DATE_FORMAT = "",
GROUP_FIELD = "shape_id",
INPUT = input,
ORDER_FIELD = "shape_pt_sequence",
OUTPUT = output,
.quiet = TRUE
)
# qgis_algorithm_search_by_word(str = "pointstopath")
algorithm <- "qgis:pointstopath"
# cat(qgis_show_help(algorithm = algorithm))
# Run algorithm.
## !!!!!!!!!!!!!!!!!!!! NB! pead muutma pythoni skripti 188 rida.
## File "/usr/share/qgis/python/plugins/processing/algs/qgis/PointsToPaths.py", line 188, in processAlgorithm
## Panin sinna mingi faili, sest temp faili ei osanud python kirjutada ja pärast lugeda.
output <- sprintf(
'postgres://dbname=\'%s\' host=%s port=%s user=\'%s\' sslmode=%s password=\'%s\' table=\"%s\".\"%s\" (geom)',
conf$dbname, conf$host, conf$port, conf$user, conf$sslmode, conf$password,
conf$schema, conf$table
)
input <- sprintf(
'postgres://dbname=\'%s\' host=%s port=%s user=\'%s\' sslmode=%s password=\'%s\' srid=4326 type=Point checkPrimaryKeyUnicity=\'1\' table=\"%s\".\"%s_source\" (geometry)',
conf$dbname, conf$host, conf$port, conf$user, conf$sslmode, conf$password,
conf$schema, conf$table
)
result <- qgisprocess::qgis_run_algorithm(
algorithm = algorithm,
CLOSE_PATH = 0,
DATE_FORMAT = "",
GROUP_FIELD = "shape_id",
INPUT = input,
ORDER_FIELD = "shape_pt_sequence",
OUTPUT = output,
.quiet = TRUE
)
styler:::style_active_file()
conf = NULL
library(dplyr)
# Temp directory
tmp_dir <- "/tmp/gtfs"
if (!dir.exists(tmp_dir)) {
dir.create(tmp_dir)
}
# Download link
url <- "http://www.peatus.ee/gtfs/"
# Estonia shapefile (ZIP archive)
map_shapefile <- "gtfs.zip"
# Download and save
url_download <- sprintf("%s%s", url, map_shapefile)
saveTo <- sprintf("%s/%s", tmp_dir, map_shapefile)
if (!file.exists(saveTo)) {
utils::download.file(
url = url_download,
destfile = saveTo, method = "curl", extra = "-L"
)
}
# Unzip
utils::unzip(saveTo, overwrite = T, exdir = tmp_dir)
# List of files
ls <- list.files(path = tmp_dir, pattern = ".txt")
ls_long <- list.files(path = tmp_dir, pattern = ".txt", full.names = T)
tbl_names <- tools::file_path_sans_ext(list.files(path = tmp_dir, pattern = "*.txt"))
# Export to postgis
if (is.null(conf)) {
conf <- ruut::get_config()
conf$schema <- "gtfs"
conf$table <- ""
}
# Multi layer: paneme kõik csv failid üheks failiks.
if (file.exists(saveTo)) {
## Export to postgis database.
# New schema
ruut::db_create_new_schema(conf = conf)
# Postgresql string
pg <- ruut::construct_ogr2ogr_PG_connect_str()
# Connect to db
conn <- ruut::db_connect()
# ---------- Copy CSV!!! data to database ----------
for (i in 1:length(tbl_names)) {
# Tabeli nimi
conf$table <- tbl_names[i]
# ogr2ogr oskab csv faili lugeda juhul kui laiend on csv. Muudame.
system(sprintf("mv %s %s/%s.csv", ls_long[i], tmp_dir, tbl_names[i]))
# Laeme eraldi andmed shape muutujasse, et teede geomeetriat leida
if (tbl_names[i] == "shapes") {
shapes <- utils::read.csv(file = sprintf("%s/%s.csv", tmp_dir, tbl_names[i]))
## --------------- Points to SpatialLines -----------------
cat("\n----------------\nMarsruutide geomeetria loomine\n")
sl <- shapes %>%
dplyr::arrange(shape_id, shape_pt_sequence) %>%
sf::st_as_sf(coords = c("shape_pt_lon", "shape_pt_lat"), agr = "constant") %>%
# dplyr::group_by(shape_id) %>%
# dplyr::summarise() %>%
sf::st_cast("POINT")
sf::st_crs(sl) <- 4326
# # Test plot
# sl %>%
#   ggplot2::ggplot(ggplot2::aes(colour = shape_id)) +
#   ggplot2::geom_sf()
# plot(sl)
# Write to database
tabeli_suffix <- "_source"
sf::st_write(
obj = sl, dsn = conn, layer_options = c("GEOMETRY=AS_XY", "OVERWRITE=yes"),
layer = sprintf("%s%s", conf$table, tabeli_suffix)
)
# Change schema
q <- sprintf("
drop table if exists %s.%s%s cascade; \
ALTER TABLE %s%s SET SCHEMA %s", conf$schema, conf$table, tabeli_suffix, conf$table, tabeli_suffix, conf$schema)
cat("\n----------------\nMuudame schema.\n")
cat(q)
DBI::dbExecute(conn, q)
# ----------- Point to path teisendus ------------
# Algoritmi juhend!
# qgis_algorithm_search_by_word(str = "pointstopath")
algorithm <- "qgis:pointstopath"
# cat(qgis_show_help(algorithm = algorithm))
# Run algorithm.
## !!!!!!!!!!!!!!!!!!!! NB! pead muutma pythoni skripti 188 rida.
## File "/usr/share/qgis/python/plugins/processing/algs/qgis/PointsToPaths.py", line 188, in processAlgorithm
## Panin sinna mingi faili, sest temp faili ei osanud python kirjutada ja pärast lugeda.
output <- sprintf(
'postgres://dbname=\'%s\' host=%s port=%s user=\'%s\' sslmode=%s password=\'%s\' table=\"%s\".\"%s\" (geom)',
conf$dbname, conf$host, conf$port, conf$user, conf$sslmode, conf$password,
conf$schema, conf$table
)
input <- sprintf(
'postgres://dbname=\'%s\' host=%s port=%s user=\'%s\' sslmode=%s password=\'%s\' srid=4326 type=Point checkPrimaryKeyUnicity=\'1\' table=\"%s\".\"%s_source\" (geometry)',
conf$dbname, conf$host, conf$port, conf$user, conf$sslmode, conf$password,
conf$schema, conf$table
)
result <- qgisprocess::qgis_run_algorithm(
algorithm = algorithm,
CLOSE_PATH = 0,
DATE_FORMAT = "",
GROUP_FIELD = "shape_id",
INPUT = input,
ORDER_FIELD = "shape_pt_sequence",
OUTPUT = output,
.quiet = TRUE
)
} else {
cmd <- sprintf(
paste0(
"ogr2ogr -f PostgreSQL ",
"%s -lco SCHEMA=%s -lco OVERWRITE=yes -lco FID=%s -nln \"%s\" \"%s/%s.csv\""
), pg, conf$schema, "fid", conf$table, tmp_dir, tbl_names[i]
)
cat(cmd)
cat("\n\n")
system(cmd)
}
}
# Lisame andmebaasi seosed
q <- sprintf("
ALTER TABLE %s.agency ADD CONSTRAINT agency_un UNIQUE (agency_id); \
ALTER TABLE %s.calendar ADD CONSTRAINT calendar_un UNIQUE (service_id); \
ALTER TABLE %s.fare_attributes ADD CONSTRAINT fare_attributes_un UNIQUE (fare_id); \
ALTER TABLE %s.routes ADD CONSTRAINT routes_un UNIQUE (route_id); \
ALTER TABLE %s.shapes ADD CONSTRAINT shapes_un UNIQUE (shape_id); \
ALTER TABLE %s.stops ADD CONSTRAINT stops_un UNIQUE (stop_id); \
ALTER TABLE %s.trips ADD CONSTRAINT trips_un UNIQUE (trip_id); \
ALTER TABLE %s.fare_rules ADD CONSTRAINT fare_rules_fk FOREIGN KEY (fare_id) REFERENCES %s.fare_attributes(fare_id); \
ALTER TABLE %s.fare_attributes ADD CONSTRAINT fare_attributes_fk FOREIGN KEY (agency_id) REFERENCES %s.agency(agency_id); \
ALTER TABLE %s.routes ADD CONSTRAINT routes_fk FOREIGN KEY (agency_id) REFERENCES %s.agency(agency_id); \
ALTER TABLE %s.calendar_dates ADD CONSTRAINT calendar_dates_fk FOREIGN KEY (service_id) REFERENCES %s.calendar(service_id); \
ALTER TABLE %s.fare_rules ADD CONSTRAINT fare_rules_route_fk FOREIGN KEY (route_id) REFERENCES %s.routes(route_id); \
ALTER TABLE %s.stop_times ADD CONSTRAINT stop_times_stops_fk FOREIGN KEY (stop_id) REFERENCES %s.stops(stop_id); \
ALTER TABLE %s.stop_times ADD CONSTRAINT stop_times_trips_fk FOREIGN KEY (trip_id) REFERENCES %s.trips(trip_id); \
ALTER TABLE %s.trips ADD CONSTRAINT trips_calendar_fk FOREIGN KEY (service_id) REFERENCES %s.calendar(service_id); \
ALTER TABLE %s.trips ADD CONSTRAINT trips_routes_fk FOREIGN KEY (route_id) REFERENCES %s.routes(route_id);", conf$schema, conf$schema, conf$schema, conf$schema, conf$schema, conf$schema, conf$schema, conf$schema, conf$schema, conf$schema, conf$schema, conf$schema, conf$schema, conf$schema, conf$schema, conf$schema, conf$schema, conf$schema, conf$schema, conf$schema, conf$schema, conf$schema, conf$schema, conf$schema, conf$schema)
cat(q)
DBI::dbExecute(conn, q)
# ogr2ogr help.
# system("ogr2ogr --long-usage")
# ogrinfo help.
# system("ogrinfo --help-general")
# pg <- ruut::construct_ogr2ogr_PG_connect_str()
# system(sprintf("ogrinfo %s xgtfs.trips ", pg))
# system(sprintf("ogrinfo %s xgtfs.trips ", pg))
}
# Delete temp directory
# system(sprintf("rm -rf %s/*.csv", tmp_dir))
}
library(estmap)
library(estmap)
urls <- c("19-1.IBehaviorListener.2-=", "19-1.IBehaviorListener.2-")
url_download <- "https://teeregister.mnt.ee/reet/shpreports?"
url_download <- "https://teeregister.mnt.ee/reet/shpreports"
urls <- c("19-1.IBehaviorListener.2-=", "19-1.IBehaviorListener.2-")
i <- 1
url <- sprintf("%s?%s", url_download, urls[i])
cat(url)
url_download <- "https://teeregister.mnt.ee/reet/shpreports"
urls <- c("0-9.IBehaviorListener.0-RIIGITEE_SOIDUTEED", "19-1.IBehaviorListener.2-", "0-2.IBehaviorListener.2-")
i <- 1
url <- sprintf("%s?%s", url_download, urls[i])
cat(url)
url_download <- "https://teeregister.mnt.ee/reet/shpreports"
urls <- c("0-5.IBehaviorListener.0-:idc", "19-1.IBehaviorListener.2-", "0-2.IBehaviorListener.2-")
i <- 1
url <- sprintf("%s?%s", url_download, urls[i])
cat(url)
system.file("inst/testimiseks/","test_1.R",package = "estmap")
system.file("testimiseks/","test_1.R",package = "estmap")
system.file("testimiseks","test_1.R",package = "estmap")
library(estmap)
tmp_dir <- system.file("tmp","gtfs",package = "estmap")
tmp_dir
system.file("tmp","gtfs",package = "estmap")
tmp_dir <- system.file("tmp","Untitled Document.txt",package = "estmap")
tmp_dir
tmp_dir <- system.file("tmp/gtfs","Untitled Document.txt",package = "estmap")
tmp_dir
tmp_dir <- system.file("gtfs","Untitled Document.txt",package = "estmap")
tmp_dir <- system.file("tmp","gtfs/Untitled Document.txt",package = "estmap")
tmp_dir
tmp_dir <- system.file("tmp","Untitled.txt",package = "estmap")
tmp_dir
tmp_dir <- system.file("undelited","undelited.txt",package = "estmap")
tmp_dir
tmp_dir <- system.file("undelited","undelited.txt",package = "estmap")
tmp_dir
system.file("undelited","test_1.R",package = "estmap")
system.file("undelited","undelited.txt",package = "estmap")
system.file("undelited","Undelited.txt",package = "estmap")
system.file("undelited","Undelited.txt",package = "estmap")
system.file("untitled","test_1.R",package = "estmap")
system.file("untitled","test_1.R",package = "estmap")
system.file("untitled","test_1.R",package = "estmap")
system.file("untitled","test_1.R",package = "estmap")
library(estmap)
system.file("undelited","Undelited.txt",package = "estmap")
system.file("untitled","Undelited.txt",package = "estmap")
system.file("untitled","Untitled.txt",package = "estmap")
system.file("tmp","Untitled.txt",package = "estmap")
system.file("tmp/gtfs","Untitled.txt",package = "estmap")
system.file("tmp/gtfs","Untitled.txt",package = "estmap")
system.file("DESCRIPTION",package = "estmap")
system.file("DESCRIPTION",package = "estmap")
system.file(package = "estmap")
tmp_dir <- sprintf("%s", system.file(package = "estmap"))
tmp_dir
tmp_dir <- sprintf("%s/tmp/%s", system.file(package = "estmap"), "gtfs")
tmp_dir
library(estmap)
library(estmap)
ans <- utils::askYesNo("Do you want to import gtfs data into database?")
if (!ans | is.na(ans)) {
cat("\n------------------------\n")
cat("Kaardikihte ei lisatud.")
cat("\n------------------------\n")
return()
}
!ans | is.na(ans)
ans <- utils::askYesNo("Do you want to import gtfs data into database?")
ans
!ans | is.na(ans)
is.na(ans)
ans <- utils::askYesNo("Do you want to import gtfs data into database?")
ans <- utils::askYesNo("Do you want to import gtfs data into database?")
ans
is.na(ans)
!ans | is.na(ans)
ans
ans <- NA
ans
ans <- NA
ans <- utils::askYesNo("Do you want to import gtfs data into database?")
cat("\n------------------------\n")
cat("Kaardikihte ei lisatud.")
cat("\n------------------------\n")
return()
}
if (!ans | is.na(ans)) {
cat("\n------------------------\n")
cat("Kaardikihte ei lisatud.")
cat("\n------------------------\n")
return()
}
if (!ans | is.na(ans)) {
cat("\n------------------------\n")
cat("Kaardikihte ei lisatud.")
cat("\n------------------------\n")
# return()
}
ans <- NA
ans <- utils::askYesNo("Do you want to import gtfs data into database?")
cat("\n------------------------\n")
cat("Kaardikihte ei lisatud.")
cat("\n------------------------\n")
# return()
}
ans <- NA
ans <- utils::askYesNo("Do you want to import gtfs data into database?")
ans
ans <- NA
ans <- utils::askYesNo("Do you want to import gtfs data into database?")
ans <- utils::askYesNo("Do you want to import gtfs data into database?")
ans
ans <- utils::askYesNo("Do you want to import gtfs data into database?")
ans
ans <- utils::askYesNo("Do you want to import gtfs data into database?")
ans
ans <- utils::askYesNo("Do you want to import gtfs data into database?")
ans
ans <- utils::askYesNo("Do you want to import gtfs data into database?")
ans
ans <- utils::askYesNo("Do you want to import gtfs data into database?")
ans
ans <- utils::askYesNo("Do you want to import gtfs data into database?")
ans <- utils::askYesNo("Do you want to import gtfs data into database?", default = F)
ans
ans <- utils::askYesNo("Do you want to import gtfs data into database?", default = F)
ans
styler:::style_active_file()
styler:::style_active_file()
library(estmap)
library(estmap)
library(estmap)
conf = NULL
# Temp directory
tmp_dir <- sprintf("%s/tmp/%s", system.file(package = "estmap"), "osm_shp")
if (!dir.exists(tmp_dir)) {
dir.create(tmp_dir)
}
# Download link
url <- "https://download.geofabrik.de/europe"
# Estonia shapefile (ZIP archive)
map_shapefile <- "estonia-latest-free.shp.zip"
# Download and save
url_download <- sprintf("%s/%s", url, map_shapefile)
saveTo <- sprintf("%s/%s", tmp_dir, map_shapefile)
if (!file.exists(saveTo)) {
utils::download.file(
url = url_download,
destfile = saveTo, method = "curl", extra = "-L"
)
}
# Unzip
utils::unzip(saveTo, overwrite = T, exdir = tmp_dir)
# List of files
ls <- list.files(path = tmp_dir, pattern = ".dbf")
ls_long <- list.files(path = tmp_dir, pattern = ".dbf", full.names = T)
tbl_names <- unlist(strsplit(x = ls, split = ".dbf"))
# Export to postgis
if (is.null(conf)) {
conf <- ruut::get_config()
conf$schema <- "osm_shp"
}
# New schema
ruut::db_create_new_schema(conf = conf)
tbl_names
conf = NULL
# Temp directory
tmp_dir <- sprintf("%s/tmp/%s", system.file(package = "estmap"), "osm_shp")
if (!dir.exists(tmp_dir)) {
dir.create(tmp_dir)
}
# Download link
url <- "https://download.geofabrik.de/europe"
# Estonia shapefile (ZIP archive)
map_shapefile <- "estonia-latest-free.shp.zip"
# Download and save
url_download <- sprintf("%s/%s", url, map_shapefile)
saveTo <- sprintf("%s/%s", tmp_dir, map_shapefile)
file.exists(saveTo)
if (!file.exists(saveTo)) {
utils::download.file(
url = url_download,
destfile = saveTo, method = "curl", extra = "-L"
)
}
# Unzip
utils::unzip(saveTo, overwrite = T, exdir = tmp_dir)
# List of files
ls <- list.files(path = tmp_dir, pattern = ".dbf")
ls_long <- list.files(path = tmp_dir, pattern = ".dbf", full.names = T)
tbl_names <- unlist(strsplit(x = ls, split = ".dbf"))
# Export to postgis
if (is.null(conf)) {
conf <- ruut::get_config()
conf$schema <- "osm_shp"
}
# New schema
ruut::db_create_new_schema(conf = conf)
map_shapefile
# Multi layer
for (i in 1:length(tbl_names)) {
print(tbl_names[i])
conf$table <- gsub("gis_osm_", "", gsub("_free_1", "", tbl_names[i]))
source <- sprintf('"%s" "%s"', tmp_dir, tbl_names[i])
## Export to postgis database.
ruut::copy_shp_to_db(
dir = tmp_dir, layer = tbl_names[i], conf = conf,
id = "fid", crs_source = "EPSG:4326", crs_target = "EPSG:4326",
geometry_type = "PROMOTE_TO_MULTI"
)
cat(sprintf(
"\nShp fail %s kopeeriti POSTGIS andmebaasi %s.%s\n\n",
ls[i], conf$schema, conf$table
))
}
# Delete unnecessary files.
system(sprintf("find %s -type f -not -name '%s' -print0 | xargs -0 rm --", tmp_dir, map_shapefile))
styler:::style_active_file()
styler:::style_active_file()
library(estmap)