Connecting to a Postgresql data base from R (Ubuntu Natty)

Just three quick steps in Ubuntu.

1. Install unixodbc and odbc-postgresql

sudo apt-get install  unixodbc

sudo apt-get install odbc-postgresql

2. Edit the blank odbc.ini file. You can just paste in the text below. The first entry is for my own local database. Change the details to match your own if you have one.  If you want access to the online Biotree.net database you need to substitute the real password for the ***** entry.

sudo gedit /etc/odbc.ini

[ODBC Data Sources]
mydb = Database description

[mydb]
Driver = /usr/lib/odbc/psqlodbcw.so
Database = gisdb
Servername = localhost
Username = postgres
Password = postgres
Protocol = 8.2.5
ReadOnly = 0

[ODBC Data Sources]
biotree = Database description

[biotree]
Driver = /usr/lib/odbc/psqlodbcw.so
Database = biotree_development
Servername = apps.iecolab.es
Username = biotree_adv
Password = *******
Protocol = 8.2.5
ReadOnly = 0

[ODBC]
InstallDir = /usr/lib

Step 3. Then in R, assuming that the RODBC library is installed

library(RODBC)

con<-odbcConnect(“biotree”)

You now design a query and run it to get your data.

d<-sqlQuery(con, “select * from gis.biotree_db where genus like ‘Quercus'”)
fix(d)

Note that if you want spatial layers from a PostGIS data base in R you can use rgdal to connect to the data base. The problem is that you can’t run queries from within R using rgdal. You can only import the whole layer. The work around is to use system (ogr2ogr …) with a temp file, but for most purposes it is easier to visualise and edit the layer in Qgis and save as shapefile for importing into R.

What is the equivalent in Windows?

It is a similar process but not quite as simple. You first need to install the odbc driver from http://postgresql.org . The easiest way is to download the msi file that installs itself.

 

Once the driver is installed you just need to set up the connector from the control panel, administrative tools,

You can use ODBC with R in the dame way, and you can also  load Postgresql queries into Excel if you find any reason to do so.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s