PostgreSQL access with R

## Fetches table with selected entries.

library(RPostgreSQL)

## load the PostgreSQL driver
drv <- dbDriver("PostgreSQL")

## Open a connection
con <- dbConnect(drv, dbname="ICES", host="postgresx03.infra.xtr.deltares.nl", user="dbices", password="vectors")

## Submits a statement and declare variable result set (rs)
#rs <- dbSendQuery(con, "select the_point, st_x(st_transform(the_point,28992)), st_y(st_transform(the_point,28992)) from ocean limit 10")

rs <- dbSendQuery(con, "select avg(doxy) from ocean o, icessquares i where st_contains(i.the_geom, o.the_point) and year = 2005 and i.statsq = '32F3'")

## fetch all elements from the result set into a data.frame
## n = number of records, n=-1 means all records
df <- fetch(rs, n = -1)

## Check number of records
dim(df)

##show first lines
head(df)

## write comma-separated data to file
write.table(df, file = "Filename.txt", sep = ",")

## Closes the connection
dbDisconnect(con)

## Frees all the resources on the driver
dbUnloadDriver(drv)