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.
Code Block |
---|
# 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
Code Block |
---|
## 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.