postgresql - PostGIS and R - Get data faster and in smaller size -
i have downloaded corine 2012 data land coverage (available here), in order use creating online map via shiny , leaflet. have uploaded data in postgresql database , wish use queries of data parts shiny application. began trying getting data quite slow , main query leads 80 mb dataframe. how can approach differently in order speed getting data , reduce size? code snippet (getting data areas coniferous plants) is:
library(rpostgresql) library(postgistools) drv <- dbdriver("postgresql") # loads 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 mycorine code='312'",geom_name = "geom") thank in advance!
i've had quite lot of joy using rpostgis , sf when extracting large amounts of vector data postgis r. incorporate st_simplify speed geometry displays in leaflet:
# set connection conn <- dbconnect("postgresql",user="user",password="mypass",port=5432,dbname="postgis_name") # dummy query (obviously), including spatial subset , st_simplify simplify geometry qry <- "select st_simplify(geom,60) geom mytable geom && st_makeenvelope(xmin, ymin, xmax, ymax, epsg)" the.data = st_read_db(conn, query=qry, geom="geom") this return simplified sf objects, read in data frame , quick read r.
the above query against 600,000 polygons , subset bounding box read in 8,000 of them. took 0.4 seconds. done attribute instead of spatial bounding box (query times may differ though).
https://cran.r-project.org/web/packages/sf/sf.pdf
you should take account how data reasonable display , level of geometrical detail acceptable @ zoom level etc.
Comments
Post a Comment