[GIS] Access Shapefiles from PostgreSQL/Postgis in R

gdalodbcpostgispostgresqlr

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?

Best Answer

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.

Related Question