I wrote R code in R Studio to load a raster (stored in PostgreSQL) to R. The database is stored here: C:\Program Files\PostgreSQL\
The following versions are installed on the computer:
- R version 3.4.0 (2017-04-21) Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows >= 8 x64 (build 9200)
- PostgreSQL 10.0, compiled by Visual C++ build 1800, 64-bit
- GDAL 2.2.1, released 2017/06/23
- POSTGIS="2.4.1 r16012" PGSQL="100" GEOS="3.6.2-CAPI-1.10.2 4d2925d" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.2, released 2017/09/15" LIBXML="2.7.8" LIBJSON="0.12" LIBPROTOBUF="1.2.1" RASTER
The raster can be downloaded from this page:
Raster into PostgreSQL
I used this code to store it in the PostgreSQL database:
raster2pgsql -s 21781 -I -C -M "F:\GoogleDrive\DHM_200\DHM200.asc" -F public.dhm200_LV03 | psql -U postgres -d db_name
From PostgreSQL to R
With the following script I access the database and load the raster. Loading the raster takes more than 3 minutes!!:
library(rpostgis)
library(RODBC)
library(rgdal)
con <- dbConnect(drv="PostgreSQL", dbname="db_name",host="localhost",port=5432,user="postgres",password="pw" )
dhm200 <- pgGetRast(con, c("public","dhm200_lv03"), rast = "rast")
Load .asc directely
Loading the raster original with the following code takes less than 1 second!!.
library(raster)
dhm200<- raster("F:\\GoogleDrive\\DHM_200\\DHM200.asc")
My questions:
- Is this normal?
- How can I make it faster?
