2

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 ? :)

EDIT:

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.

9
  • 1
    Where's the shapefile here?
    – Spacedman
    Jul 9 at 14:30
  • 1
    That's not a shapefile. You are reading from a database, no shapefiles are involved. en.wikipedia.org/wiki/Shapefile
    – Spacedman
    Jul 9 at 15:10
  • 3
    Can you edit your Q and take out mentions of "shapefile"? Because that is incorrect and doesn't help people find your question.
    – Spacedman
    Jul 9 at 15:16
  • 1
    How dynamic is your data? Is it updated often so that when a user runs the app they need the absolute latest data? Because otherwise you could generate a native R .rds file every so often and use loadRDS. Also have you tried using sf spatial objects instead of sp?
    – Spacedman
    Jul 9 at 15:18
  • 1
    Is your data complex polygons and lines? Or points? Because reading the table as non-spatial is bound to be much faster when there's no geometries to construct. Do you need to do one query of the whole table or could you rewrite your code to only query selected parts when needed using an additional SQL clause when you read it (with st_read if using sf, which is recommended...)
    – Spacedman
    Jul 9 at 15:27
0

If you are using a recent version of Postgis then you can output the whole query as geojson and maybe read that using st_read? Here is a query you can try: SELECT ST_AsGeoJSON(sub.*) AS geojson FROM dataset_test AS sub. Paul Ramsey's excellent blog explains this: http://blog.cleverelephant.ca/2019/08/postgis-3-geojson.html

-1

I would try using SF with RPostgreSQL or rpostgis. My understanding of Rgdal, as described here is that is uses SP, which as mentioned here, is usually, but not always, slower than SF. Using ST_Simplify may also speed up your query.

In answer to your second question, you could query the geom column with st_astext.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.