0

I have a PostgreSQL / PostGIS table with 30 rows (only 3 are shown) and 3 columns as follows (raster is a PostGIS data type) - It's the EFSA CAPRI data set btw, if somebody's fimilar with it:

enter image description here

// Can I import the raster data type from PostGIS into R with the help of the RPostgreSQL-package (see the code below) OR do I have to use the rgdal-package inevitably as described by @Jot eN?

require(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname = "")
dbGetquery(con, "SELECT rid, rast, filename FROM schema.capri")

Importing it without transformation and St_AsText(rast) (which works for the geometry data type of PostGIS) don't work.

andschar
  • 3,504
  • 2
  • 27
  • 35

2 Answers2

3

If this is still relevant, at the University of Florida, David Bucklin and I have released a rpostgis package that provides bi-directional transfer between PostGIS and R for vector and raster data. The package does not rely on GDAL (and rgdal), and should be platform independent.

Assuming that you already have a functional connection con established through RPostgreSQL, you can import PostGIS raster data type into R using the function pgGetRast, for instance:

library(rpostgis)
my_raster <- pgGetRast(con, c("schema", "raster_table"))

The function assumes that the raster tiles are stored in the column "rast" by default (as is the case for you), but you can change that with the argument rast. Now, depending on the size and other considerations, this may be significantly slower (but a lot more flexible) than using rgdal. We are still working on it, but this is the cost of providing a "pure R" solution. You can also use the boundary argument if you are only interested in a subset of the entire raster (which will significantly increase the loading time).

Note also that there is pgGetGeom for points/lines/polygons, instead of using St_AsText.

Mathieu Basille
  • 171
  • 8
1

You have an answer on gis.stackexchange page - https://gis.stackexchange.com/a/118401/20955:

library('raster')
library('rgdal')

dsn="PG:dbname='plots' host=localhost user='test' password='test' port=5432 schema='gisdata' table='map' mode=2"

ras <- readGDAL(dsn) # Get your file as SpatialGridDataFrame
ras2 <- raster(ras,1) # Convert the first Band to Raster
plot(ras2)

Additional info could be found here https://rpubs.com/dgolicher/6373

Community
  • 1
  • 1
Jot eN
  • 6,120
  • 4
  • 40
  • 59
  • Thanks. It's not working with the RPostgreSQL-package? Your second link leads to a blank page. – andschar Jun 21 '16 at 14:23
  • Strange - the second link works for me. Maybe try again using different browser? – Jot eN Jun 21 '16 at 14:50
  • No second browser installed. I'm using Firefox 46.0.1 on Linux Mint. Without '/6373' I get to the website. Strange indeed - All articles there lead to blank pages for me.. – andschar Jun 21 '16 at 14:59
  • 1
    My Add-block was the reason why I wasn't able to get to the website. – andschar Jun 27 '16 at 09:29