Access Shapefiles from PostgreSQL/Postgis in R

Question

I successfully setup a connection to my PostgreSQL/PostGIS database on Ubuntu Server from my Laptop (Ubuntu 12.04).

Therefore I installed RODBC in R to setup a connection and I made a correct entry in the /etc/odbc.ini file to setup the DSN as described for example here. Now I'm able to connect to my database with

ch<-odbcConnect("database")

After connecting, I'm am able query all the tables inside the database with SQL queries and even use the spatial functionalities of PostGIS inside the db.

Now I would like to work with the spatial data inside R so I downloaded the RGDAL package. The driver for PostGIS should be automatically included in Unixbased systems so I execute

ogrDrivers()

to check this. It gives me back that PostgreSQL = TRUE so I assume this includes the PostGIS driver?! Otherwise it isn't listed.

Now, in theory I should be able to read-in a layer like this:

test<-readOGR(ch,"state")

but unfortunately I gives back:

Error in ogrInfo(dsn = dsn, layer = layer, encoding = encoding, use_iconv = use_iconv) : Cannot open file

So did I miss anything? What can I do to make it work?

• HeyOverThere: In addition to Evil Genius' answer below, double check to make sure the PostGIS extension was installed in your database. Unless it has been added to the default template PostGIS won't be in a newly created Postgres database.

Answer 1

You don't need odbc in this case.
The dsn argument of readOGR and ogrInfo is a text string, just like you would use with OGR in any other case. So, in your case, something like "PG:dbname=database".

RGDAL uses the OGR libraries, which uses the Postgres libraries directly to communicate with the database.

See the OGR PostgreSQL Format Docs for more info.

PS. The drivers listed with = TRUE in the driver listing are those that have write access. The others are assumed to have read-only access.

• HeyOverThere: I agree, going through unixODBC is extra, unnecessary step that adds a middleman between you and your data. Other than that the example that Joschi was working from looks really good. I'd check out PL/R as the example mentions down towards the bottom, it brings R directly into your Postgres database.
• Joschi: @HeyOverThere: true. Still I use the SQL inside R (some kind of the other way round). What would be the advantage of using R inside the db? I imagine speed issues?
• HeyOverThere: It can be faster, especially if most of your work is being done on the database side. Another benefit is you can create a function that you can call from queries. You can use that function in QGIS to display your data, or as part of a web application.
• Joschi: @HeyOverThere so if I understand you right, in stead of creating a whole bunch of data I just save the R function to create this data as a Script and than call it if needed through an SQL Querry from QGIS...?
• HeyOverThere: You can save the R script within a Postgresql function. Here's a quick tutorial: joeconway.com/plr/doc/plr-funcs.html