[GRASSLIST:1711] Re: Rdbi.PgSQL

[Markus Neteler]

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