I have downloaded the Corine 2012 data about land coverage (available here), in order to use it for creating online a map via Shiny and Leaflet. I have uploaded the data in my PostgreSQL database and wish to use queries of data parts for my Shiny application. I began trying getting the data but it is quite slow and my main query leads to a 80 MB dataframe. How can I approach this differently in order to speed up getting the data and reduce its size? My code snippet (getting data for areas with coniferous plants) is:
library(RPostgreSQL)
library(postGIStools)
drv <- dbDriver("PostgreSQL") # loads the PostgreSQL driver
con <- dbConnect(drv, dbname = mydbname, host = myhost, port = myport,
user = myuser, password = mypassword)
# Getting data
coniferous <- get_postgis_query(con, "SELECT id, geom from mycorine WHERE code='312'",geom_name = "geom")
i've had quite a lot of joy using rpostgis and sf when extracting large amounts of vector data from postgis into R. Also incorporate ST_Simplify to speed up geometry displays in Leaflet:
# set up connection
conn <- dbConnect("PostgreSQL",user="user",password="mypass",port=5432,dbname="postgis_name")
# dummy query (obviously), including a spatial subset and ST_Simplify to simplify geometry
qry <- "SELECT ST_Simplify(geom,60) AS geom FROM mytable WHERE geom && ST_MakeEnvelope(xmin, ymin, xmax, ymax, EPSG)"
the.data = st_read_db(conn, query=qry, geom="geom")
this will return simplified sf objects, which are read in as a data frame and are very quick to read into R.
The above query was against 600,000 polygons and subset by a bounding box that read in about 8,000 of them. It took 0.4 seconds. Obviously this could be done by attribute instead of spatial bounding box (query times may differ though).
https://cran.r-project.org/web/packages/sf/sf.pdf
You should always take into account how much data is reasonable to display and what level of geometrical detail is acceptable at your zoom level etc.