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:
./configure
make
sudo make install - Download, compile and install psqlODBC. I received a few warnings, but both libraries installed fine in my
/usr/local/lib
. - 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 editodbc.ini
manually. - 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 theodbcConnect
wrapper function, which then in my case looked like this:
[r]
library(RODBC)
con <- odbcDriverConnect("DNS=pg;UID=postgres;DRIVER=PostgreSQL;DATABASE=drupal") [/r] where DRIVER needs to match description set inodbc.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, likeDRIVER=/usr/local/lib/psqlodbcw.so
).
....and only a couple of hours later I was able to issue my query.
I use RPostgreSQL or Rgdal without problem
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.