3

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?

Improve this question
1
  • 1
    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.
    – HeyOverThere
    Commented Jan 15, 2014 at 16:13

1 Answer 1

Reset to default
3

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.

Improve this answer
6
  • 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.
    – HeyOverThere
    Commented Jan 15, 2014 at 16:08
  • @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?
    – Joschi
    Commented Jan 16, 2014 at 10:02
  • 1
    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.
    – HeyOverThere
    Commented Jan 16, 2014 at 16:32
  • @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...?
    – Joschi
    Commented Feb 26, 2014 at 19:49
  • 1
    You can save the R script within a Postgresql function. Here's a quick tutorial: joeconway.com/plr/doc/plr-funcs.html
    – HeyOverThere
    Commented Feb 26, 2014 at 20:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.