This page is dedicated to the VECTORS workshop in Portorož, Slovenia. The method provided creates a subset of data (points) for a specific ICES square and plots a sequence of values (chlorophyll).
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
# ---------------------------------------------------------------------------
# import modules
import psycopg2
import matplotlib.pyplot as plt
# 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 year,month,to_number(day, '9999'),cphl
from ocean
where (select st_within(the_point,the_geom) from icessquares where statsq = '31F2')
and cphl IS NOT NULL and sdepth < 10 and year = 2003
order by year,month,day,cphl
"""
# execute the query
cur.execute(strSql)
# store the result of the query into Tuple c
c = cur.fetchall()
# closes the connection
conn.close()
# now store day and avg(cphl) in two separate arrays
cphl = []
days = []
for i in range(len(c)):
days.append((c[i])[2])
cphl.append((c[i])[3])
# plot the
plt.xlabel('days')
plt.plot(cphl)
plt.show()
|
The entire snippet can be downloaded as python code. Please try out and adjust to your needs and of course share it with the OpenEarth community. You are challenged to share your modifications.