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!!:


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!!.

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? Commented Jan 23, 2018 at 16:27
  • i was interested to test this but no download available.
    – Sam
    Commented May 16, 2018 at 10:53
  • Hi Sam. You can find it here: opendata.swiss/de/dataset/…
    – Li Ku
    Commented May 17, 2018 at 13:28

1 Answer 1


Got here from a random google search. Got curious/worried.
Takes about 2 seconds for me.

user system elapsed
0.699 0.077 2.119

PostgreSQL 11.10 (Ubuntu 11.10-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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