Hi,
In the tutorial on the GRASS/R interface there is also some note on connecting R to PostgreSQL. I know this is not the most apropriate place to ask this questions but since Markus wrote the tutorial... I did successfully manage to to import table data into R with the procedure described in that document.
The reverse procedure however remained a mistery. I tried both to export the just a single column and the whole table. The importing on the other hand worked in both.
Once I also wrote to R mailnig list about the library Rdbi.PgSQL and they replied me that at the monment the only official (CRAN) way to connect to PostgreSQL is through ODBC. I do not know how wold that be done either.
Thanks, Miha Staut
_________________________________________________________________
Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail
On Sat, Nov 08, 2003 at 10:10:04PM +0000, Miha STAUT wrote:
Hi,
In the tutorial on the GRASS/R interface there is also some note on
connecting R to PostgreSQL. I know this is not the most apropriate place to
ask this questions but since Markus wrote the tutorial... I did successfully
manage to to import table data into R with the procedure described in that
document.
The reverse procedure however remained a mistery. I tried both to export the
just a single column and the whole table. The importing on the other hand
worked in both.
It doesn't work due to a bug in the interface.
Once I also wrote to R mailnig list about the library Rdbi.PgSQL and they
replied me that at the monment the only official (CRAN) way to connect to
PostgreSQL is through ODBC. I do not know how wold that be done either.
You are lucky: I fixed it (with help of Steno Fontanari and Dirk
Eddelbuettel)). There is a "new" Rdbi on BioConductor.org, but they didn't
use our fixes and introduced two new bugs 
Here are temporarily the new packages containing all fixes:
http://mpa.itc.it/markus/tmp/rdbi/
- fixes new bugs in BioConductor.org version (rename of package)
- fixes old bugs in Sourceforge version (export from R to PG)
- adds make.names() to polish SQL column names to R style
For me it works fine now (maybe more fixes are needed).
Here a speed comparision vs RODBC:
http://grass.itc.it/statsgrass/r_and_dbms.html
A table was read into R with 7512 obs. (rows) of 151 variables (columns).
Method: I was copy-pasting all commands at the same time, so the
second Sys.time() was issued immediately.
Access from my machine via firewall to another machine outside the
firewall.
RODBC query: 4:30 minutes
Rdbi query: 5 seconds.
Hope that they will pick up the fixes this times...
Cheers
Markus Neteler