3

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?
3
  • 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
0

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

Version:
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, privacy policy and cookie policy

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