2

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:

https://opendata.swiss/de/dataset/das-digitale-hohenmodell-der-schweiz-mit-einer-maschenweite-von-200-m1

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?
  • Did you test with geometries? Are they slow as well? – Pierre Racine Jan 23 '18 at 16:27
  • i was interested to test this but no download available. – Sam May 16 '18 at 10:53
  • Hi Sam. You can find it here: opendata.swiss/de/dataset/… – Li Ku May 17 '18 at 13:28

Your Answer

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

Browse other questions tagged or ask your own question.