#' Teeregistri WFS ja maaameti andmete põhjal oma teeandmete 'minu_teed' loomine #' #' 1. Põhi-, kõrval ja tugimaanteedest ning rambid moodustatakse uus andmebaas 'riigiteedevork'. #' 2. Ilma teenumbrita kohalike teede andmebaasi 'numbrita_kohalik_tee' loomine. #' 3. Eesti kaart ainult suurte saartega (üle 10km2). #' 4. Polügoniseerime riigiteedevõrgu. #' Eesti avaandmete \url{https://avaandmed.eesti.ee/datasets?emsId=35} teabevärava kaudu teederegistri WFS (Web Feature Service) andmete eksportimine postgisandmebaasi. WFS andmestiku kirjeldus: \url{https://teeregister-api.mnt.ee/teenus/wfs?request=GetCapabilities&service=WFS} #' #' @param conf A list() of configuration variables. Default values \code{\link[ruut]{get_config}}. #' @return No output. #' @seealso [ruut::get_config()], [ruut::copy_shp_to_db()], [estmap::teeregister_wfs()] #' @keywords postgis, maps, WFS, OSM #' @export #' @examples #' \dontrun{ #' #' conf <- ruut::get_config() #' minu_teed_loomine(conf = conf) #' } minu_teed_loomine <- function(conf = NULL) { ## ------------- muutujad ja teisendused --------------- if (is.null(conf)) { conf <- ruut::get_config() } ## Defineerime ajutised failid ja muutujad. tmp_dir <- tempdir() # ajutine kataloog obj <- "demo" tmp_gpkg_file <- sprintf("%s/%s.gpkg", tmp_dir, obj) # ajutine gpkg fail conf$gpkg_home <- tmp_dir conf$gpkg_file <- obj conf$primary_key <- "id" ## Loome lingid gpkg ajutistest tabelitest lugemiseks ja kirjutamiseks conf$gpkg_table <- layer_3 <- "test_layer_3" tmp_gpkg_file_output_3 <- ruut::construct_to_gpkg_output_file_str(conf = conf, is_input_str = F) tmp_gpkg_file_input_3 <- ruut::construct_to_gpkg_output_file_str(conf = conf, is_input_str = T) conf$gpkg_table <- layer_2 <- "test_layer_2" tmp_gpkg_file_output_2 <- ruut::construct_to_gpkg_output_file_str(conf = conf, is_input_str = F) tmp_gpkg_file_input_2 <- ruut::construct_to_gpkg_output_file_str(conf = conf, is_input_str = T) conf$gpkg_table <- layer_1 <- "test_layer_1" tmp_gpkg_file_output_1 <- ruut::construct_to_gpkg_output_file_str(conf = conf, is_input_str = F) tmp_gpkg_file_input_1 <- ruut::construct_to_gpkg_output_file_str(conf = conf, is_input_str = T) # ## =================== 1. 'riigiteedevõrk' ======================= # ## Teeregistri WFS avaandmete abil riigiteedevõrgu loomine # # Loe instaleeritud tabelid andmebaasis # installed_tables <- ruut::db_schema_tablenames(conf = conf) # ## Kontrollime ühendatavate andmebaaside olemasolu. # if (all(c("pohimaantee", "ramp", "tugimaantee", "korvalmaantee") %in% installed_tables)) { # ## 1. Ühendame riigiteed üheks võrgustikuks # # ruut::qgis_algorithm_search_by_word("merge") # algorithm <- "native:mergevectorlayers" # # ruut::qgis_show_help(algorithm = algorithm) # conf$schema <- "teeregister_wfs" # conf$table <- "pohimaantee" # layer_1 <- ruut::construct_to_gpkg_output_postgres_str( # conf = conf, geometry_type = "MultiLineString", srid = 3301, # checkPrimaryKeyUnicity = TRUE, key = "id", geometry_field = "geometry" # ) # conf$table <- "tugimaantee" # layer_2 <- ruut::construct_to_gpkg_output_postgres_str( # conf = conf, geometry_type = "MultiLineString", srid = 3301, # checkPrimaryKeyUnicity = TRUE, key = "id", geometry_field = "geometry" # ) # conf$table <- "korvalmaantee" # layer_3 <- ruut::construct_to_gpkg_output_postgres_str( # conf = conf, geometry_type = "MultiLineString", srid = 3301, # checkPrimaryKeyUnicity = TRUE, key = "id", geometry_field = "geometry" # ) # # conf$table <- "ramp" # # layer_4 <- ruut::construct_to_gpkg_output_postgres_str( # # conf = conf, geometry_type = "MultiLineString", srid = 3301, # # checkPrimaryKeyUnicity = TRUE, key = "id", geometry_field = "geometry" # # ) # conf$schema <- "minu_teed" # # New schema # ruut::db_create_new_schema(conf = conf) # conf$table <- "riigiteedevork" # output <- ruut::construct_to_gpkg_output_postgres_str(conf = conf, geometry_field = "geom", geometry_type = "MultiLineString", srid = 3301, checkPrimaryKeyUnicity = FALSE) # cmd <- sprintf( # "qgis_process run native:mergevectorlayers --CRS='epsg:3301' --LAYERS='%s' --LAYERS='%s' --LAYERS='%s' --OUTPUT='%s' ", # layer_1, layer_2, layer_3, output # ) # system(cmd) # cat(sprintf("\n-------------\nLoodi andmebaas '%s'.\n", conf$table)) # } ## ================= Schema 'minu_teed' ==================== conf$schema <- "minu_teed" ruut::db_create_new_schema(conf = conf) ## =================== 2. teedevõrkude loomine ======================= ## 2.1 Riigiteedevõrk (ilma rampideta) <= 30, koos rampide ja muude riigiteedega <= 45. # ruut::qgis_algorithm_search_by_word("extract") algorithm <- "native:extractbyattribute" # ruut::qgis_show_help(algorithm = algorithm) conf$schema <- "eesti" conf$table <- "e_501_tee_j" input <- ruut::construct_to_gpkg_output_postgres_str( conf = conf, geometry_type = "MultiLineString", srid = 3301, checkPrimaryKeyUnicity = TRUE, key = "id", geometry_field = "geom" ) output <- tmp_gpkg_file_output_1 str <- sprintf("{ 'FIELD' : 'tyyp', 'INPUT' : '%s', 'OPERATOR' : 5, 'OUTPUT' : '%s', 'VALUE' : '45' }", input, output) cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm) system(cmd) cat(sprintf("\n-------------\nLoodi andmebaas '%s'.\n", output)) ## 2.1.1 Copy temp gpkg file to postgis. conf$schema <- "minu_teed" conf$table <- "teedevork_riigiteed" PG <- ruut::construct_ogr2ogr_PG_connect_str(conf = conf) cmd <- sprintf('ogr2ogr -progress --config PG_USE_COPY YES -f PostgreSQL %s -lco DIM=2 %s %s -overwrite -nlt %s -lco GEOMETRY_NAME=geom -lco FID=id -nln %s.%s -nlt PROMOTE_TO_MULTI', PG, tmp_gpkg_file, layer_1, "LINESTRING", conf$schema, conf$table) system(cmd) cat(sprintf("\n-------------\nLoodi andmebaas '%s'.\n", conf$table)) ## 2.2 Muud teed s.o tyyp IN (50,60,). ## 2.2.1 Esmalt sorteerime >= 50 # ruut::qgis_algorithm_search_by_word("extract") algorithm <- "native:extractbyattribute" # ruut::qgis_show_help(algorithm = algorithm) conf$schema <- "eesti" conf$table <- "e_501_tee_j" input <- ruut::construct_to_gpkg_output_postgres_str( conf = conf, geometry_type = "MultiLineString", srid = 3301, checkPrimaryKeyUnicity = TRUE, key = "id", geometry_field = "geom" ) output <- tmp_gpkg_file_output_1 str <- sprintf("{ 'FIELD' : 'tyyp', 'INPUT' : '%s', 'OPERATOR' : 3, 'OUTPUT' : '%s', 'VALUE' : '50' }", input, output) cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm) system(cmd) cat(sprintf("\n-------------\nLoodi andmebaas '%s'.\n", output)) ## 2.2.2 Seejärel sorteerime <= 60 input <- tmp_gpkg_file_input_1 output <- tmp_gpkg_file_output_2 str <- sprintf("{ 'FIELD' : 'tyyp', 'INPUT' : '%s', 'OPERATOR' : 5, 'OUTPUT' : '%s', 'VALUE' : '60' }", input, output) cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm) system(cmd) cat(sprintf("\n-------------\nLoodi andmebaas '%s'.\n", output)) ## 2.2.3 Copy temp gpkg file to postgis. conf$schema <- "minu_teed" conf$table <- "teedevork_muud_teed" PG <- ruut::construct_ogr2ogr_PG_connect_str(conf = conf) cmd <- sprintf('ogr2ogr -progress --config PG_USE_COPY YES -f PostgreSQL %s -lco DIM=2 %s %s -overwrite -nlt %s -lco GEOMETRY_NAME=geom -lco FID=id -nln %s.%s -nlt PROMOTE_TO_MULTI', PG, tmp_gpkg_file, layer_2, "LINESTRING", conf$schema, conf$table) system(cmd) cat(sprintf("\n-------------\nLoodi andmebaas '%s'.\n", conf$table)) ## 2.2.4 Leiame need muud teed kus 'tee'=NULL (ilma teenumbrita ehk kvartalissisesed). input <- tmp_gpkg_file_input_2 output <- tmp_gpkg_file_output_3 str <- sprintf("{ 'FIELD' : 'tee', 'INPUT' : '%s', 'OPERATOR' : 8, 'OUTPUT' : '%s' }", input, output) cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm) system(cmd) cat(sprintf("\n-------------\nLoodi andmebaas '%s'.\n", output)) ## 2.2.5 Copy temp gpkg file to postgis. conf$schema <- "minu_teed" conf$table <- "numbrita_kohalik_tee" PG <- ruut::construct_ogr2ogr_PG_connect_str(conf = conf) cmd <- sprintf('ogr2ogr -progress --config PG_USE_COPY YES -f PostgreSQL %s -lco DIM=2 %s %s -overwrite -nlt %s -lco GEOMETRY_NAME=geom -lco FID=id -nln %s.%s -nlt PROMOTE_TO_MULTI', PG, tmp_gpkg_file, layer_3, "LINESTRING", conf$schema, conf$table) system(cmd) cat(sprintf("\n-------------\nLoodi andmebaas '%s'.\n", conf$table)) ## =================== 3. 'eesti' ======================= ## 3.1 Maakondadest Eesti maismaapiir # ruut::qgis_algorithm_search_by_word("dissolve") algorithm <- "native:dissolve" # ruut::qgis_show_help(algorithm = algorithm) conf$schema <- "maaamet" conf$table <- "maakond" input <- ruut::construct_to_gpkg_output_postgres_str( conf = conf, geometry_type = "MultiPolygon", srid = 3301, checkPrimaryKeyUnicity = TRUE, key = "id", geometry_field = "geom" ) output <- tmp_gpkg_file_output_1 str <- sprintf("{ 'FIELD' : [], 'INPUT' : '%s', 'OUTPUT' : '%s' }", input, output) cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm) system(cmd) ## 3.2 Kustutame ebaolulised väljad # ruut::qgis_algorithm_search_by_word("delete") algorithm <- "native:deletecolumn" # ruut::qgis_show_help(algorithm = algorithm) input <- tmp_gpkg_file_input_1 output <- tmp_gpkg_file_output_2 cmd <- sprintf("qgis_process run %s --COLUMN='mnimi' --COLUMN='mkood' --COLUMN='fid' --INPUT='%s' --OUTPUT='%s' ", algorithm, input, output) system(cmd) ## 3.3 Jaotame multipart polügooni singlepart polügoonideks. # ruut::qgis_algorithm_search_by_word("multipart") algorithm <- "native:multiparttosingleparts" # ruut::qgis_show_help(algorithm = algorithm) input <- tmp_gpkg_file_input_2 output <- tmp_gpkg_file_output_3 str <- sprintf("{ 'INPUT' : '%s', 'OUTPUT' : '%s' }", input, output) cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm) system(cmd) ## 3.4 Lisame pindade pindalad ja perimeetri # ruut::qgis_algorithm_search_by_word("columns") algorithm <- "qgis:exportaddgeometrycolumns" # ruut::qgis_show_help(algorithm = algorithm) input <- tmp_gpkg_file_input_3 output <- tmp_gpkg_file_output_1 str <- sprintf("{ 'CALC_METHOD' : 0, 'INPUT' : '%s', 'OUTPUT' : '%s' }", input, output) cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm) system(cmd) ## 3.5 Eemaldame objektid mille pindala on väiksem kui 10km2 (väikesaared) # ruut::qgis_algorithm_search_by_word("extract") algorithm <- "native:extractbyattribute" # ruut::qgis_show_help(algorithm = algorithm) input <- tmp_gpkg_file_input_1 output <- tmp_gpkg_file_output_2 # conf$schema <- "minu_teed" # conf$table <- "eesti" # output <- ruut::construct_to_gpkg_output_postgres_str( # conf = conf, key = "fid", geometry_field = "geom" # ) str <- sprintf("{ 'FIELD' : 'area', 'OPERATOR' : 2, 'VALUE' : '10000000', 'INPUT' : '%s', 'OUTPUT' : '%s' }", input, output) cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm) system(cmd) ## 3.6 Eemaldame augud, mis on suuremad kui 1ha. # ruut::qgis_algorithm_search_by_word("holes") algorithm <- "native:deleteholes" # ruut::qgis_show_help(algorithm = algorithm) input <- tmp_gpkg_file_input_2 conf$schema <- "minu_teed" conf$table <- "eesti" output <- ruut::construct_to_gpkg_output_postgres_str( conf = conf, geometry_field = "geom" ) str <- sprintf("{ 'MIN_AREA' : '10000', 'INPUT' : '%s', 'OUTPUT' : '%s' }", input, output) cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm) system(cmd) # Processing algorithm… # Algorithm 'Polygons to lines' starting… # Input parameters: # { 'INPUT' : 'postgres://dbname=\'data\' host=localhost port=6432 user=\'osm\' sslmode=disable authcfg=0i30k14 key=\'fid\' srid=3301 type=Polygon checkPrimaryKeyUnicity=\'1\' table=\"minu_teed\".\"eesti\" (geom)', 'OUTPUT' : 'TEMPORARY_OUTPUT' } ## 3.7 Salvestame eraldi eesti kontuurjooned. # ruut::qgis_algorithm_search_by_word("lines") algorithm <- "native:polygonstolines" # ruut::qgis_show_help(algorithm = algorithm) conf$schema <- "minu_teed" conf$table <- "eesti" input <- ruut::construct_to_gpkg_output_postgres_str( conf = conf, geometry_type = "Polygon", srid = 3301, checkPrimaryKeyUnicity = TRUE, key = "fid", geometry_field = "geom" ) conf$schema <- "minu_teed" conf$table <- "eesti_l" output <- ruut::construct_to_gpkg_output_postgres_str( conf = conf, geometry_field = "geom" ) str <- sprintf("{ 'INPUT' : '%s', 'OUTPUT' : '%s' }", input, output) cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm) system(cmd) ## =================== 4. 'riigiteedevork_a' ======================= ## 4.0 Enne polügoniseerimist ühendame eesti kontuuri ja teedevõrgu üheks. # ruut::qgis_algorithm_search_by_word("merge") algorithm <- "native:mergevectorlayers" # ruut::qgis_show_help(algorithm = algorithm) conf$schema <- "minu_teed" conf$table <- "eesti_l" layer_1 <- ruut::construct_to_gpkg_output_postgres_str( conf = conf, geometry_type = "MultiLineString", srid = 3301, checkPrimaryKeyUnicity = TRUE, key = "id_1", geometry_field = "geom" ) conf$schema <- "minu_teed" conf$table <- "riigiteedevork" layer_2 <- ruut::construct_to_gpkg_output_postgres_str( conf = conf, geometry_type = "MultiLineString", srid = 3301, checkPrimaryKeyUnicity = TRUE, key = "id_0", geometry_field = "geom" ) output <- tmp_gpkg_file_output_3 # conf$schema <- "minu_teed" # conf$table <- "riigiteedevork_piiriga" # output <- ruut::construct_to_gpkg_output_postgres_str(conf = conf, geometry_field = "geom") cmd <- sprintf( "qgis_process run native:mergevectorlayers --CRS='epsg:3301' --LAYERS='%s' --LAYERS='%s' --OUTPUT='%s' ", layer_1, layer_2, output ) system(cmd) cat(sprintf("\n-------------\nLoodi teedev\U00F5rgu andmebaas '%s'.\n",conf$table)) ## 4.1 Polügoniseerime riigiteedevõrgu. Tekib teedega piiratud polügoonid. # ruut::qgis_algorithm_search_by_word("Polygonize") algorithm <- "native:polygonize" # ruut::qgis_show_help(algorithm = algorithm) input <- tmp_gpkg_file_input_3 # conf$schema <- "minu_teed" # conf$table <- "riigiteedevork_piiriga" # input <- ruut::construct_to_gpkg_output_postgres_str( # conf = conf, geometry_type = "MultiLineString", srid = 3301, # checkPrimaryKeyUnicity = TRUE, key = "id_0", geometry_field = "geom" # ) conf$schema <- "minu_teed" conf$table <- "riigiteedevork_a" output <- ruut::construct_to_gpkg_output_postgres_str( conf = conf, geometry_field = "geom" ) str <- sprintf("{ 'INPUT' : '%s', 'KEEP_FIELDS' : False, 'OUTPUT' : '%s' }", input, output) cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm) system(cmd) ## 4.2 Lõikame Eesti kaardist polügoniseerime riigiteedevõrgu välja. ## Tekib ümber Eesti raam, mis tuleb jagada kuidagi transporditsoonideks. # ruut::qgis_algorithm_search_by_word("Difference") algorithm <- "native:difference" # ruut::qgis_show_help(algorithm = algorithm) conf$schema <- "minu_teed" conf$table <- "eesti" input <- ruut::construct_to_gpkg_output_postgres_str( conf = conf, geometry_type = "Polygon", srid = 3301, checkPrimaryKeyUnicity = TRUE, key = "fid", geometry_field = "geom" ) conf$schema <- "minu_teed" conf$table <- "riigiteedevork_a" overlay <- ruut::construct_to_gpkg_output_postgres_str( conf = conf, geometry_type = "Polygon", srid = 3301, checkPrimaryKeyUnicity = TRUE, key = "fid", geometry_field = "geom" ) conf$schema <- "minu_teed" conf$table <- "riigiteedevork_xxx" output <- ruut::construct_to_gpkg_output_postgres_str( conf = conf, geometry_field = "geom", key = "fid" ) str <- sprintf("{ 'INPUT' : '%s', 'OUTPUT' : '%s', 'OVERLAY' : '%s' }", input, output, overlay) cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm) system(cmd) "Processing algorithm… Algorithm 'Intersection' starting… Input parameters: { 'INPUT' : 'postgres://dbname=\'data\' host=localhost port=6432 user=\'osm\' sslmode=disable authcfg=0i30k14 key=\'fid\' srid=3301 type=MultiLineString checkPrimaryKeyUnicity=\'1\' table=\"minu_teed\".\"riigiteedevork\" (geom)', 'INPUT_FIELDS' : [], 'OUTPUT' : 'TEMPORARY_OUTPUT', 'OVERLAY' : 'postgres://dbname=\'data\' host=localhost port=6432 user=\'osm\' sslmode=disable authcfg=0i30k14 key=\'id_0\' srid=3301 type=MultiPolygon checkPrimaryKeyUnicity=\'1\' table=\"minu_teed\".\"riigiteedevork_xxx\" (geom)', 'OVERLAY_FIELDS' : [], 'OVERLAY_FIELDS_PREFIX' : '' }" ## 4.3 Kuna polügoniseeritud pinnad ei olnud täiuslikud, siis teostame ## polügoniseeriist veelkorra. Selleks lõikame esmalt teedevõrgust ## tekkinud raamiga need teed mis jäävad raamis sisse. # ruut::qgis_algorithm_search_by_word("Intersection") algorithm <- "native:intersection" # ruut::qgis_show_help(algorithm = algorithm) conf$schema <- "minu_teed" conf$table <- "riigiteedevork" input <- ruut::construct_to_gpkg_output_postgres_str( conf = conf, geometry_type = "MultiLineString", srid = 3301, checkPrimaryKeyUnicity = TRUE, key = "fid", geometry_field = "geom" ) conf$schema <- "minu_teed" conf$table <- "riigiteedevork_xxx" overlay <- ruut::construct_to_gpkg_output_postgres_str( conf = conf, geometry_type = "MultiPolygon", srid = 3301, checkPrimaryKeyUnicity = TRUE, key = "fid", geometry_field = "geom" ) conf$schema <- "minu_teed" conf$table <- "riigiteedevork_xxx1" output <- ruut::construct_to_gpkg_output_postgres_str( conf = conf, geometry_field = "geom" ) str <- sprintf("{ 'INPUT' : '%s', 'INPUT_FIELDS' : [], 'OUTPUT' : '%s', 'OVERLAY' : '%s', 'OVERLAY_FIELDS' : [], 'OVERLAY_FIELDS_PREFIX' : '' }", input, output, overlay) cmd <- ruut::construct_qgis_output_result_to_better_format(str = str, algorithm = algorithm) system(cmd) }