Introduction
This page is a result from a question from one of the VECTORS members.
The method provided creates a list of averaged temperatures, averaged salinity for each icessquare, for each year, for each month and for each depth class.
Datasource:
- postgresx03.xtr.infra.deltares.nl (database name = ICES, username = dbices, password = vectors) (!!privileges are read-only!!)
Necessary modules:
For Python psycopg2 (http://www.stickpeople.com/projects/python/win-psycopg/psycopg2-2.4.4.win32-py2.7-pg9.1.2-release.exe)
For R the library RPostgreSQL is used.
Below the code and the scripts of the 2 possibilities to query the database:
- #The snippet for Python
- #The snippet for R
The snippet for Python
Please note that if you are using the code, the indents are very important in Python.
# Description: Use of psycopg2 module to query database ICES
# ---------------------------------------------------------------------------
# Created by Gerrit Hendriksen (gerrit.hendriksen@deltares.nl)
# v1.0 created on 21-05-2012 (ddmmyyyy)
#
# Description: retrieving data per icessquare form the ICES database
# ---------------------------------------------------------------------------
# import modules
import psycopg2
# create connection to ices database
conn = psycopg2.connect("dbname=ICES host=postgresx03.infra.xtr.deltares.nl user=dbices password=vectors")
# create a cursor object called cur
cur = conn.cursor()
# construct a query string
strSql = """
SELECT i.statsq, year, month, avg(temperature) as avg_T, avg(salinity) as avg_Sal,sdepth_bin
FROM ocean o,
icessquares i
WHERE st_within(the_point,the_geom) AND
sea_region LIKE 'North Sea%'
AND year > 1980
AND temperature IS NOT NULL and salinity IS NOT NULL
GROUP BY i.statsq, year, month, sdepth_bin
ORDER BY statsq,year, month
"""
# execute the query
cur.execute(strSql)
# store the result of the query into Tuple c
c = cur.fetchall()
print (c)
# closes the connection
cur.close()
conn.close()
# do something with the query result
# ---------------------------------------------------------------------------
The entire code can be downloaded as Python Code.
The snippet for R
## Opens connection to ICES oceanographic database (online PostGreSQL database, copy of original <2010)
## hosted by Deltares, The Netherlands
## Fetches table with selected entries. In this case table nox (nitrate+nitrite average per ICES rectangle for the North Sea
##
library(RPostgreSQL)
## loads 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
rs <- dbSendQuery(con, "SELECT i.statsq, year, month, avg(temperature) as avg_T, avg(salinity) as avg_Sal,sdepth_bin
FROM ocean o,
icessquares i
WHERE st_within(the_point,the_geom) AND
sea_region LIKE 'North Sea%'
AND year > 1980
AND temperature IS NOT NULL and salinity IS NOT NULL
GROUP BY i.statsq, year, month, sdepth_bin
ORDER BY statsq,year, month")
## fetch all elements from the resultSet into a data.frame
df <- fetch(rs, n = -1)
## Check number of records
dim(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)
Please try out and adjust to your needs and of course share it with the OpenEarth community. You are challenged to share your modifications.
