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.
- Download, compile and install unixODBC This is required for the driver, and I did not have it. Use the standard procedure:
sudo make install
- Download, compile and install psqlODBC. I received a few warnings, but both libraries installed fine in my
- Launch the ODBC administrator (under Applications > Utilities). Go to Drivers > Add, then add a description (I used pg) add the path to the driver:
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.inifile under your
/Library/ODBC/directory. With knowledge of the syntax it is certainly possible to edit
- 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
odbcDriverConnectdirectly instead of the
odbcConnectwrapper function, which then in my case looked like this:
con <- odbcDriverConnect("DNS=pg;UID=postgres;DRIVER=PostgreSQL;DATABASE=drupal") [/r] where DRIVER needs to match description set in
odbc.inivia the ODBC administrator. (This is really just a pointer, as it is also possible to put the path to the driver here directly, like
....and only a couple of hours later I was able to issue my query.