I have quite a lot of raster files stored in a PostgreSQL DB. Each of them has around 200MB and is representing EU-wide data. However I only need the the rasters to cover the area of Germany. I'm working in R.

I'd use readGDAL() in a loop to import the rasters:

-- # get list of files/tables with the help of the RPostgreSQL-package
x <- dbGetQuery(con, "SELECT * FROM information_schema.tables WHERE table_schema = 'efsa_spatial' AND table_name LIKE 'efsa_capri_%'")
crop <- x[, 3] -- # list of table names to iterate
raster_list <- vector(mode="list", length=length(crop))
for (i in seq(crop)) {
  -- # using readGDAL to import the rasters
  ras <- readGDAL(sprintf("PG:dbname='' host='' port='' user='' schema='efsa_spatial' table='%s' mode=2", crop[i]))
  raster_list[[substring(crop[i], 12)]] <- raster(ras, 1) 

Is it possible to crop the rasters simultaneously when performing readGDAL()? The best solution would be to use a shapefile / SpatialPolygonsDataFrame of Germany as a gauge, defining which raster cells are loaded. Is this possible? If not by using readGDAL(), are there other ways to do this?

I'm aware that I could do this easily after having loaded the files but as they are so big my intention is to reduce their size already in the course of importing.

I came up with such an idea because for all the vector data in this DB I used the RPostgreSQL-package to load the data into R. This allowed me to apply limitations to the data when importing (e.g "SELECT * FROM table WHERE column_1 <= 100;") - But apparently the RPostgreSQL-package doesn't work with raster data.

  • readGDAL has arguments to read a window by offset and count – mdsumner Jun 29 '16 at 7:50
  • which ones do you mean? the argument offset defines the number of rows and columns to begin - so I can't use a SpatialPolygonsDataFrame to outline my favored area. Also, regions.dim and output.dim don't seem to accept such input: inside-r.org/packages/cran/rgdal/docs/asSGDF_GROD – andschar Jun 29 '16 at 9:02
  • You are supposed to figure it out, I know it's annoying but it's possible from the dims and scale. – mdsumner Jun 29 '16 at 9:08
  • But this window will be rectangular and not fitting to the area of polygon? – andschar Jun 29 '16 at 9:18
  • Not sure that you will be able to achieve this using readGDAL(), but you might consider doing this preprocessing step outside of R with gdalwarp and specifically the -cutline option – dmci Jun 29 '16 at 9:26

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.