Querying Postgis data in R using dplyr package

Question

Does anyone have a workflow for querying postgis data in R using the dplyr package?

R doesn't like the spatial columns in postgis data so can't read them in like a postgres table. I get the error message:

Warning message:
In postgresqlExecStatement(conn, statement, ...) :
  RS-DBI driver warning: (unrecognized PostgreSQL field type geometry (id:17846) in column 1)

I know there are other ways of reading in spatial data as all I want to do is join some data that have in R to an existing postgis table using R. I'm trying to avoid exporting it to a shapefile first.

Answer 1

You can load data from postgis with the rgdal package into R.

library(rgdal)
library(sp)

dbname = "yourdatabase"
host = "yourhost"
user = "AUser"
pass = "ThisUsersPassword"
name = "ASpatialTable" # Postgis table

dsn = paste0("PG:dbname='",dbname,"' host='",host,"' user='",user,"' password='",pass,"'")
res = readOGR(dsn,name)

plot(res)

Write yourself some function around it and you can use that in your dplyr pipes.