How to access a Postgres Database from R on Mac

I was interested in re-purposing this example on using PostGIS from within R. It assumes that "an ODBC connector has been set up". It did take me a while to set up the connection from R on a Mac (OS 10.5.8), and I did not find a coherent documentation about this.

So for the record, below is how I did it.

The process included installing the Postgres driver, setting the configuration file for the ODBC connection, and then using odbcDriverConnect from within R to open the connection.

  1. Download, compile and install unixODBC This is required for the driver, and I did not have it. Use the standard procedure:
    ./configure
    make
    sudo make install
  2. Download, compile and install psqlODBC. I received a few warnings, but both libraries installed fine in my /usr/local/lib.
  3. Launch the ODBC administrator (under Applications > Utilities). Go to Drivers > Add, then add a description (I used pg) add the path to the driver: /usr/local/lib/psqlodbcw.so

    Go to System DSN and enter a Data Source Name of your choosing (I used PostgreSQL).

    ODBC administrator does nothing else in this case than write a system wide odbc.ini file under your /Library/ODBC/ directory. With knowledge of the syntax it is certainly possible to edit odbc.ini manually.

  4. Now launch R.Since I have several databases on the server, I needed to provide the name of the database. In addition, the connection only worked for me if I also explicitly provided the driver. Adding those parameters required that I use odbcDriverConnect directly instead of the odbcConnect wrapper function, which then in my case looked like this:
    R:
    1. library(RODBC)
    2. con <- odbcDriverConnect("DNS=pg;UID=postgres;DRIVER=PostgreSQL;DATABASE=drupal")

    where DRIVER needs to match description set in odbc.ini via the ODBC administrator. (This is really just a pointer, as it is also possible to put the path to the driver here directly, like DRIVER=/usr/local/lib/psqlodbcw.so).

....and only a couple of hours later I was able to issue my query.

No TweetBacks yet. (Be the first to Tweet this post)

3 thoughts on “How to access a Postgres Database from R on Mac”

  1. I don't think unixODBC is required since MacOSX comes with iODBC. Try doing a "./configure --with-iodbc" when compiling psqlODBC to use it instead.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>