R and postgres

On Monday 06 November 2006 04:58 pm, Reece Hart wrote:

I'd like to get R to talk to postgresql, but my head's spinning among a
web of broken links, way outdated web pages, project deprecation
announcements and a terrible signal:link ratio.

Rdbi and RdbiPgSQL seem to be the answer, despite both being apparently
defunct projects.

What is the Right Thing for a guy who wants R to talk to postgresql?

Thanks, and send aspirin,
Reece

You might want to take a look at PL/R a procedural language for R in Postgres.
http://www.joeconway.com/plr/
--
Adrian Klaver
aklaver@comcast.net

On Mon, 2006-11-06 at 19:13 -0800, Adrian Klaver wrote:

You might want to take a look at PL/R a procedural language for R in
Postgres.
http://www.joeconway.com/plr/

PL/R is indeed neat, but I want to go the other way: within R, fetch
data from postgres and manipulate it (plot, histogram, etc). I
appreciate that I could expose this through postgres, but that's a
little awkward for prototyping and I'm not keen to add load to my
postgres box.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

On Monday 06 November 2006 07:38 pm, Reece Hart wrote:

On Mon, 2006-11-06 at 19:13 -0800, Adrian Klaver wrote:

You might want to take a look at PL/R a procedural language for R in
Postgres.
http://www.joeconway.com/plr/

PL/R is indeed neat, but I want to go the other way: within R, fetch
data from postgres and manipulate it (plot, histogram, etc). I
appreciate that I could expose this through postgres, but that's a
little awkward for prototyping and I'm not keen to add load to my
postgres box.

-Reece

Sorry, I did not read the message closely enough. A quick perusal of the R
docs helped me understand the issue a little better. The only solution I can
see at this point is to have an intermediate step. There are two ways I can
see to do this. The first is to use the copy command to create a csv file.
The read.table() function would then be used in R to import the data. The
second is use the program pg2xbase
http://www.klaban.torun.pl/prog/pg2xbase/
to create a DBF file and use read.dbf() to input the file.
--
Adrian Klaver
aklaver@comcast.net

I assume you've seen pl/R?

http://www.joeconway.com/plr/

On Mon, 6 Nov 2006, Reece Hart wrote:

Reece Hart wrote:

I'd like to get R to talk to postgresql, but my head's spinning among a
web of broken links, way outdated web pages, project deprecation
announcements and a terrible signal:link ratio.

Rdbi and RdbiPgSQL seem to be the answer, despite both being apparently
defunct projects.

What is the Right Thing for a guy who wants R to talk to postgresql?

I think you want this:
http://bioconductor.org/packages/1.9/bioc/html/RdbiPgSQL.html

The bioconductor project is now maintaining RdbiPgSQL. I think many
people also use RODBC to connect R with Postgres.

Joe

I use RODBC which is available from http://cran.r-project.org/
I'm not sure if this will do what you want, or whether it has
the features of Rdbi you need, but it gets the job done for me.
I can open a "channel", execute a SQL statement (typically a Select)
and read the results back into a R dataframe.
There is a nice way to dump a dataframe back into a table which
is created for you, with columns and datatypes as appropriate.

Hope this helps.

TJ O'Donnell
http://www.gnova.com/

On Tue, 2006-11-07 at 00:22 -0800, Joe Conway wrote:

I think you want this:
http://bioconductor.org/packages/1.9/bioc/html/RdbiPgSQL.html

The bioconductor project is now maintaining RdbiPgSQL. I think many
people also use RODBC to connect R with Postgres.

Joe-

I almost sent you an off-list email before I posted because I just knew
you'd know the answer.

Thank you.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Reece Hart wrote:

On Tue, 2006-11-07 at 00:22 -0800, Joe Conway wrote:

I think you want this:
http://bioconductor.org/packages/1.9/bioc/html/RdbiPgSQL.html

The bioconductor project is now maintaining RdbiPgSQL. I think many
people also use RODBC to connect R with Postgres.

I almost sent you an off-list email before I posted because I just knew
you'd know the answer.

Wouldn't be a problem with me, but better to post so the answer gets
into the archives for others to find later.

Glad I could help.

Joe