I'm creating a ShinyApp in which I'm trying to display on a leaflet map some spatial data (about 100 MB) fetched from a database.
According to the documentation I've found here the code sample below is the best (only ?) way to import spatial data from a database into R :
library(rgdal) dsn="PG:dbname='my_database' host='localhost' port='5432' user='admin' password='admin'" ogrListLayers(dsn) data=readOGR(dsn,"dataset_test")
It works well but it takes forever (about 4 minutes) to fully load the file in the R environment.
Thus I tried to fetch my data by mean of a query in postgre:
library(RPostgreSQL) library(pool) pool <- dbPool( drv = dbDriver("PostgreSQL", max.con = 100), dbname = "my_database", host = "host", user = "admin", password = "admin", port = "5432", idleTimeout = 3600000 ) data <- st_read(pool, query = "SELECT * FROM dataset_test") data <- st_transform(data, CRS("+proj=longlat +datum=WGS84")) leaflet(st_zm(data)) %>% addTiles() %>% addProviderTiles(providers$Esri.WorldImagery, group = "Esri World Imagery") %>% addPolygons(fillColor = "#7FFFD4", weight = 3, fillOpacity = 0.5)
This method is way faster than the first one but the drawback is that it obviously generates a non-spatial dataframe.
It's the first time I'm fetching data in R from a database and I'm a bit confused about the best way to it...
Could someone help me out please ? :)
My two mains questions are :
Why does it take so long to load a SpatialPolygonsDataFrame of 80 MB from a database to R ? (the same process is done in 5 seconds in QGIS)
If I have to give up on the idea to display my data in Shiny as a SpatialPolygonsDataFrame, what would be the best way to save a dataframe with a geom column (containing coordinates like this:
list(list(c(771256.092575953, 771289.308419454, 771291.892710547, 771256.465456019, 771256.092575953, 6577795.59430757, 6577796.39474202, 6577790.68324772, 6577791.36728974, 6577795.59430757, 0, 0, 0, 0, 0))))to a shapefile ? Thus I could incorporate a download button to my shinyApp for the user to get those data as a shapefile.