?dbDriver
??dbDriver
library(RPostgreSQL)
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
year, month, sdepth, ST_X(the_point),
temperature
FROM ocean,
#     seas s
WHERE #sea_region LIKE 'North Sea%' AND
year = 1970
AND name = 'North Sea'
#      AND name = 'Baltic Sea'
#      AND name = 'Mediterranean Sea'
AND temperature IS NOT NULL
#      AND ST_Contains(s.the_geom)
")
library(RPostgreSQL)
## load the PostgreSQL driver
drv <- dbDriver("PostgreSQL")
## Open a connection using VECTORS project credentials
con <- dbConnect(drv, dbname="ICES", host="postgresx03.infra.xtr.deltares.nl", user="dbices", password="vectors")
## Submit an SQL statement that selects all points where nitrate concentration >0
rs <- dbSendQuery(con, "select
latitude,longitude,year,month,
(ntra+COALESCE(ntri,0)) as "NOx"
from
ocean
where
year > 2002
AND year < 2009
AND ntra IS NOT NULL
")
library(RPostgreSQL)
## load the PostgreSQL driver
drv <- dbDriver("PostgreSQL")
## Open a connection using VECTORS project credentials
con <- dbConnect(drv, dbname="ICES", host="postgresx03.infra.xtr.deltares.nl", user="dbices", password="vectors")
## Submit an SQL statement that selects all points where nitrate concentration >0
rs <- dbSendQuery(con, "select
latitude,longitude,year,month,
(ntra+COALESCE(ntri,0)) as "NOx"
from
ocean
where
year > 2002
AND year < 2009
AND ntra IS NOT NULL
")
rs <- dbSendQuery(con, "select
latitude,longitude,year,month,
(ntra+COALESCE(ntri,0)) as "NOx"
from
ocean
where
year > 2002
AND year < 2009
AND ntra IS NOT NULL
")
rs <- dbSendQuery(con, "select
latitude,longitude,year,month,
(ntra+COALESCE(ntri,0)) as "NOx"
from
ocean
where
year > 2002
AND year < 2009
AND ntra IS NOT NULL
")
rs <- dbSendQuery(con,"select
latitude,longitude,year,month,
(ntra+COALESCE(ntri,0)) as "NOx"
from
ocean
where
year > 2002
AND year < 2009
AND ntra IS NOT NULL
")
library(RPostgreSQL)
library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname="ICES", host="postgresx03.infra.xtr.deltares.nl", user="dbices", password="vectors")
rs <- dbSendQuery(con,"select
latitude,longitude,year,month,
(ntra+COALESCE(ntri,0)) as "NOx"
from
ocean
where
year > 2002
AND year < 2009
AND ntra IS NOT NULL
")
)
)
rs <- dbSendQuery(con,"select
latitude,longitude,year,month,
(ntra+COALESCE(ntri,0)) as "NOx",
from
ocean
where
year > 2002
AND year < 2009
AND ntra IS NOT NULL
")
rs <- dbSendQuery(con,"select
latitude,longitude,year,month,
#(ntra+COALESCE(ntri,0)) as "NOx"
from
ocean
where
year > 2002
AND year < 2009
AND ntra IS NOT NULL
")
rs <- dbSendQuery(con,"select
latitude,longitude,year,month,
#(ntra+COALESCE(ntri,0)) as "NOx"
from
ocean
where
year > 2002
AND year < 2009
AND ntra IS NOT NULL
")
rs <- dbSendQuery(con,"select latitude,longitude,year,month,(ntra+COALESCE(ntri,0)) as "NOx"
from
ocean
where
year > 2002
AND year < 2009
AND ntra IS NOT NULL
")
library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname="ICES", host="postgresx03.infra.xtr.deltares.nl", user="dbices", password="vectors")
strSql <-
res <- dbSendQuery(con, "select
icesname as icesname,
st_x(st_centroid(the_geom)) as x,
st_y(st_centroid(the_geom)) as y,
year,month,
0 as mindepth,30 as maxdepth,
avg(temperature) as avg_temp,
min(temperature) as min_temp,
max(temperature) as max_temp,
stddev_pop(temperature) as stdev_temp,
count(temperature) as count
from
icesraster_ns
,ocean
where
st_contains(the_geom,the_point)
AND year = 1970
AND sdepth < 30
AND temperature > 0
GROUP BY icesname,x,y,year,month
UNION
select
icesname as icesname,
st_x(st_centroid(the_geom)) as x,
st_y(st_centroid(the_geom)) as y,
year,month,
30 as mindepth,2000 as maxdepth,
avg(temperature) as avg_temp,
min(temperature) as min_temp,
max(temperature) as max_temp,
stddev_pop(temperature) as stdev_temp,
count(temperature) as count
from
icesraster_ns
,ocean
where
st_contains(the_geom,the_point)
AND year = 1970
AND sdepth >= 30
AND temperature > 0
group by icesname,x,y,year,month")
df <- fetch(res, n = 10)
## check dimensions
dim(df)
dbDisconnect(con)
dbUnloadDriver(drv)
View(df)
View(df)
library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname="ICES", host="postgresx03.infra.xtr.deltares.nl", user="dbices", password="vectors")
rs <- dbSendQuery(con,"select
latitude,longitude,year,month,
#  (ntra+COALESCE(ntri,0)) as "NOx"
ntra
from
ocean
where
year > 2002
AND year < 2009
AND ntra IS NOT NULL
")
rs <- dbSendQuery(con,"select
latitude,longitude,year,month,
#  (ntra+COALESCE(ntri,0)) as "NOx"
ntra
from
ocean
where
year > 2002
AND year < 2009
AND ntra IS NOT NULL
")
rs <- dbSendQuery(con,"
select
latitude,
longitude,
year,
month,
(ntra+COALESCE(ntri,0)) as "NOx"
from
ocean
where
year > 2002
AND year < 2009
AND ntra IS NOT NULL
")
rs <- dbSendQuery(con,"
select
latitude,
longitude,
year,
month,
(ntra+COALESCE(ntri,0)) as "NOx"
from
ocean
where
year > 2002
AND year < 2009
AND ntra IS NOT NULL
")
rs <- dbSendQuery(con,"
select
latitude,
longitude,
year,
month
#,
#  (ntra+COALESCE(ntri,0)) as "NOx"
from
ocean
where
year > 2002
AND year < 2009
AND ntra IS NOT NULL
")
rs <- dbSendQuery(con,"
select
latitude,
longitude,
year,
month
#,
#  (ntra+COALESCE(ntri,0)) as "NOx"
from
ocean
where
year > 2002
AND year < 2009
AND ntra IS NOT NULL
")
rs <- dbSendQuery(con,"
select
latitude
,longitude
,year
,month
,(ntra+COALESCE(ntri,0)) as "NOx"
from
ocean
where
year > 2002
AND year < 2009
AND ntra IS NOT NULL
")
rs <- dbSendQuery(con,"select
latitude,longitude,year,month,(ntra+COALESCE(ntri,0)) as "NOx"
from
ocean
where
year > 2002 AND year < 2009 AND ntra IS NOT NULL"
rs <- dbSendQuery(con,"select
latitude,longitude,year,month,(ntra+COALESCE(ntri,0)) as "NOx"
rs <- dbSendQuery(con,"select
latitude,longitude,year,month,(ntra+COALESCE(ntri,0)) as 'NOx'
from
ocean
where
year > 2002 AND year < 2009 AND ntra IS NOT NULL"
)
rs <- dbSendQuery(con,"select
latitude,longitude,year,month,(ntra+COALESCE(ntri,0)) as NOx
from
ocean
where
year > 2002 AND year < 2009 AND ntra IS NOT NULL"
)
df <- fetch(rs, n = 100)
dim(df)
View(df)
View(df)
rs <- dbSendQuery(con, "SELECT year, month, sdepth, ST_X(the_point), temperature
FROM ocean, seas
WHERE #sea_region LIKE 'North Sea%' AND
year = 1970
AND name = 'North Sea'
#      AND name = 'Baltic Sea'
#      AND name = 'Mediterranean Sea'
AND temperature IS NOT NULL
#      AND ST_Contains(s.the_geom)
")
dbDisconnect(con)
dbUnloadDriver(drv)
library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname="ICES", host="postgresx03.infra.xtr.deltares.nl", user="dbices", password="vectors")
rs <- dbSendQuery(con, "SELECT year, month, sdepth, ST_X(the_point), temperature
FROM ocean, seas
WHERE #sea_region LIKE 'North Sea%' AND
year = 1970
AND name = 'North Sea'
#      AND name = 'Baltic Sea'
#      AND name = 'Mediterranean Sea'
AND temperature IS NOT NULL
#      AND ST_Contains(s.the_geom)
")
rs <- dbSendQuery(con, "SELECT year, month, sdepth, ST_X(the_point), temperature
FROM ocean, seas
WHERE year = 1970
AND name = 'North Sea'
#      AND name = 'Baltic Sea'
#      AND name = 'Mediterranean Sea'
AND temperature IS NOT NULL
#      AND ST_Contains(s.the_geom)
")
rs <- dbSendQuery(con, "SELECT year, month, sdepth, ST_X(the_point), temperature
FROM ocean, seas
WHERE year = 1970
AND name = 'North Sea'
AND temperature IS NOT NULL
")
df <- fetch(rs, n = 100)
dim(df)
View(df)
View(df)
df <- fetch(rs, n = -1)
View(df)
View(df)