def write(cursor, out, station):
out.write("""# Number of days exceeding given temperature thresholds
# -20, -10, 0, 32 are days with low temperature at or below value
# 50, 70, 80, 93, 100 are days with high temperature at or above value
""")
out.write(("%s %4s %4s %4s %4s %4s %4s %4s %4s %4s\n"
"") % ('YEAR', -20, -10, 0, 32, 50, 70, 80, 93, 100))
cursor.execute("""SELECT year,
sum(case when low <= -20 THEN 1 ELSE 0 END) as m20,
sum(case when low <= -10 THEN 1 ELSE 0 END) as m10,
sum(case when low <= 0 THEN 1 ELSE 0 END) as m0,
sum(case when low <= 32 THEN 1 ELSE 0 END) as m32,
sum(case when high >= 50 THEN 1 ELSE 0 END) as e50,
sum(case when high >= 70 THEN 1 ELSE 0 END) as e70,
sum(case when high >= 80 THEN 1 ELSE 0 END) as e80,
sum(case when high >= 93 THEN 1 ELSE 0 END) as e93,
sum(case when high >= 100 THEN 1 ELSE 0 END) as e100
from %s WHERE station = '%s' and day >= '%s-01-01'
GROUP by year ORDER by year ASC
""" % (constants.get_table(station), station,
constants.startyear(station)))
for row in cursor:
out.write(("%(year)4i %(m20)4i %(m10)4i %(m0)4i %(m32)4i %(e50)4i "
"%(e70)4i %(e80)4i %(e93)4i %(e100)4i\n") % row)
def process(id, csv):
# Fetch Yearly Totals
sql = """SELECT year, round(avg(high)::numeric,1) as avg_high,
round(avg(low)::numeric,1) as avg_low,
round(sum(precip)::numeric,2) as rain from %s
WHERE station = '%s' and year >= %s
GROUP by year ORDER by year ASC""" % (constants.get_table(id),
id, constants.startyear(id) )
rs = constants.mydb.query(sql).dictresult()
data = {}
for i in range(len(rs)):
year = int(rs[i]["year"])
data[year] = {'oHigh': rs[i]["avg_high"], 'oLow': rs[i]["avg_low"],
'oRain': rs[i]["rain"]}
for i in range(1893, constants._ENDYEAR):
if (not data.has_key(i)):
data[i] = {'oHigh': "M", 'oLow': "M", 'oRain': "M"}
csv.write("%s,%s,%s,"%(data[i]['oLow'],data[i]['oHigh'],data[i]['oRain']))
# Need to do climate stuff
# Then climate
sql = """SELECT round(avg(high)::numeric,1) as avg_high,
round(avg(low)::numeric,1) as avg_low,
round(sum(precip)::numeric,2) as rain from %s WHERE station = '%s' """ % (
constants.climatetable(id), id)
rs = constants.mydb.query(sql).dictresult()
aHigh = rs[0]["avg_high"]
aLow = rs[0]["avg_low"]
aRain = rs[0]["rain"]
csv.write("%s,%s,%s," % (aLow,aHigh,aRain) )
csv.write("\n")
csv.flush()
def go(mydb, stationID,updateAll=False):
"""
Generate the monthly averages, but only do as much as necessary
"""
if updateAll:
s = constants.startts(stationID)
else:
s = constants._ENDTS - mx.DateTime.RelativeDateTime(years=1)
e = constants._ENDTS
interval = mx.DateTime.RelativeDateTime(months=+1)
now = s
db = {}
while (now < e):
db[now] = {}
now += interval
rs = mydb.query("""SELECT year, month, avg(high) as avg_high,
avg(low) as avg_low, sum(precip) as rain,
sum( CASE WHEN precip >= 0.01 THEN 1 ELSE 0 END ) as rcount,
sum( CASE WHEN snow >= 0.01 THEN 1 ELSE 0 END ) as scount from %s
WHERE station = '%s' and day >= '%s-01-01' GROUP by year, month""" % (
constants.get_table(stationID), stationID, s.year ) ).dictresult()
for i in range(len(rs)):
ts = mx.DateTime.DateTime( int(rs[i]["year"]), int(rs[i]["month"]), 1)
sql = """UPDATE r_monthly SET avg_high = %s, avg_low = %s,
rain = %s, rain_days = %s, snow_days = %s
WHERE station = '%s' and monthdate = '%s' """ % (rs[i]["avg_high"],
rs[i]["avg_low"], rs[i]["rain"], rs[i]["rcount"],
rs[i]['scount'], stationID, ts.strftime("%Y-%m-%d") )
mydb.query(sql)
def process(sid, csv, yr):
""" Actually process a station for a csv file and year """
ah = []
al = []
ap = []
oh = []
ol = []
op = []
for i in range(1, 13):
sql = """
WITH yearly as (
SELECT year, avg(high) as ah, avg(low) as al,
sum(precip) as sp from %s WHERE station = '%s' and month = %s
GROUP by year)
SELECT
avg(ah) as avg_high,
avg(al) as avg_low,
avg(sp) as avg_rain,
max(case when year = %s then ah else null end) as ob_high,
max(case when year = %s then al else null end) as ob_low,
max(case when year = %s then sp else null end) as ob_rain
from yearly
""" % (constants.get_table(sid), sid, i, yr, yr, yr)
rs = constants.mydb.query(sql).dictresult()
avgHigh = rs[0]["avg_high"]
avgLow = rs[0]["avg_low"]
avgRain = rs[0]["avg_rain"]
ah.append(float(avgHigh))
al.append(float(avgLow))
ap.append(float(avgRain))
obHigh = rs[0]["ob_high"]
obLow = rs[0]["ob_low"]
obRain = rs[0]["ob_rain"]
if obHigh is not None:
oh.append(float(obHigh))
ol.append(float(obLow))
op.append(float(obRain))
csv.write("%s,%s,%s,%s,%s,%s," % (obLow or 'M', avgLow,
obHigh or 'M', avgHigh,
obRain or 'M', avgRain))
low = np.average(ol)
high = np.average(oh)
rain = np.sum(op)
avg_low = np.average(al)
avg_high = np.average(ah)
avg_rain = np.sum(ap)
csv.write("%s,%s,%s,%s,%s,%s," % (low, avg_low, high, avg_high, rain,
avg_rain))
csv.write("\n")
csv.flush()
def process(sid, csv, yr):
""" Actually process a station for a csv file and year """
ah = []
al = []
ap = []
oh = []
ol = []
op = []
for i in range(1, 13):
sql = """
WITH yearly as (
SELECT year, avg(high) as ah, avg(low) as al,
sum(precip) as sp from %s WHERE station = '%s' and month = %s
GROUP by year)
SELECT
avg(ah) as avg_high,
avg(al) as avg_low,
avg(sp) as avg_rain,
max(case when year = %s then ah else null end) as ob_high,
max(case when year = %s then al else null end) as ob_low,
max(case when year = %s then sp else null end) as ob_rain
from yearly
""" % (constants.get_table(sid), sid, i, yr, yr, yr)
rs = constants.mydb.query(sql).dictresult()
avgHigh = rs[0]["avg_high"]
avgLow = rs[0]["avg_low"]
avgRain = rs[0]["avg_rain"]
ah.append(float(avgHigh))
al.append(float(avgLow))
ap.append(float(avgRain))
obHigh = rs[0]["ob_high"]
obLow = rs[0]["ob_low"]
obRain = rs[0]["ob_rain"]
if obHigh is not None:
oh.append(float(obHigh))
ol.append(float(obLow))
op.append(float(obRain))
csv.write("%s,%s,%s,%s,%s,%s," % (obLow or 'M', avgLow, obHigh or 'M',
avgHigh, obRain or 'M', avgRain))
low = np.average(ol)
high = np.average(oh)
rain = np.sum(op)
avg_low = np.average(al)
avg_high = np.average(ah)
avg_rain = np.sum(ap)
csv.write("%s,%s,%s,%s,%s,%s," %
(low, avg_low, high, avg_high, rain, avg_rain))
csv.write("\n")
csv.flush()
def write(mydb, out, station):
out.write("""# Top 30 single day rainfalls
MONTH DAY YEAR AMOUNT
""")
rs = mydb.query("""SELECT precip, day from %s WHERE station = '%s'
and day >= '%s-01-01' ORDER by precip DESC LIMIT 30""" % (
constants.get_table(station), station,
constants.startyear(station) ) ).dictresult()
for i in range(len(rs)):
ts = mx.DateTime.strptime(rs[i]["day"], "%Y-%m-%d")
out.write("%4i%7i%6i%9.2f\n" % (ts.month, ts.day, ts.year, rs[i]["precip"]) )
def process(id, csv,yr):
for i in range(1,13):
# Compute Climate
sql = """SELECT round(avg(high)::numeric,1) as avg_high,
round(avg(low)::numeric,1) as avg_low,
round(sum(precip)::numeric,2) as rain from %s WHERE station = '%s' and
extract(month from valid) = %s""" % (constants.climatetable(id), id, i)
rs = constants.mydb.query(sql).dictresult()
aHigh = rs[0]["avg_high"]
aLow = rs[0]["avg_low"]
aRain = rs[0]["rain"]
# Fetch Obs
sql = """SELECT round(avg_high::numeric,1) as avg_high,
round(avg_low::numeric,1) as avg_low,
round(rain::numeric,2) as rain from r_monthly WHERE station = '%s'
and monthdate = '%s-%02i-01'""" % (id, yr, i)
rs = constants.mydb.query(sql).dictresult()
oHigh = rs[0]["avg_high"]
oLow = rs[0]["avg_low"]
oRain = rs[0]["rain"]
csv.write("%s,%s,%s,%s,%s,%s," % (oLow,aLow,oHigh,aHigh,oRain,aRain) )
# Need to do yearly stuff
# First, get our obs
sql = """SELECT round(avg(high)::numeric,1) as avg_high,
round(avg(low)::numeric,1) as avg_low,
round(sum(precip)::numeric,2) as rain from %s WHERE
station = '%s' and year = %s """ % (constants.get_table(id), id, yr)
rs = constants.mydb.query(sql).dictresult()
oHigh = rs[0]["avg_high"]
oLow = rs[0]["avg_low"]
oRain = rs[0]["rain"]
# Then climate
sql = """SELECT round(avg(high)::numeric,1) as avg_high,
round(avg(low)::numeric,1) as avg_low,
round(sum(precip)::numeric,2) as rain from %s WHERE station = '%s' """ % (
constants.climatetable(id.lower()), id,)
rs = constants.mydb.query(sql).dictresult()
aHigh = rs[0]["avg_high"]
aLow = rs[0]["avg_low"]
aRain = rs[0]["rain"]
csv.write("%s,%s,%s,%s,%s,%s," % (oLow,aLow,oHigh,aHigh,oRain,aRain) )
csv.write("\n")
csv.flush()
def write(mydb, out, station):
# Load up dict of dates..
cnt = {}
for day in range(210, 367):
cnt[day] = {32: 0.0, 28: 0.0, 26: 0.0, 22: 0.0}
cnt_years = {32: 0.0, 28: 0.0, 26: 0.0, 22: 0.0}
for base in (32, 28, 26, 22):
# Query Last doy for each year in archive
sql = """select year, min(extract(doy from day)) as doy from %s
WHERE month > 7 and low <= %s and low > -40 and station = '%s'
GROUP by year ORDER by doy ASC""" % (
constants.get_table(station),
base,
station,
)
rs = mydb.query(sql).dictresult()
cnt_years[base] = len(rs)
for i in range(len(rs)):
cnt[int(rs[i]["doy"])][base] += 1.0
sts = mx.DateTime.DateTime(2000, 1, 1)
running = {32: 0.0, 28: 0.0, 26: 0.0, 22: 0.0}
out.write(
"""# Low Temperature exceedence probabilities
# (On a certain date, what is the chance a temperature below a certain
# threshold would have been observed once already during the fall of that year)
DOY Date <33 <29 <27 <23
"""
)
for day in range(230, 367):
ts = sts + mx.DateTime.RelativeDateTime(days=day - 1)
for base in (32, 28, 26, 22):
running[base] += cnt[day][base]
if day % 2 == 0:
out.write(
" %3s %s %3i %3i %3i %3i\n"
% (
ts.strftime("%-j"),
ts.strftime("%b %d"),
running[32] / cnt_years[base] * 100.0,
running[28] / cnt_years[base] * 100.0,
running[26] / cnt_years[base] * 100.0,
running[22] / cnt_years[base] * 100.0,
)
)
def write(cursor, out, station):
# Load up dict of dates..
cnt = {}
for day in range(30, 183):
cnt[day] = {32: 0.0, 28: 0.0, 26: 0.0, 22: 0.0}
cnt_years = {32: 0.0, 28: 0.0, 26: 0.0, 22: 0.0}
for base in (32, 28, 26, 22):
# Query Last doy for each year in archive
sql = """
select year, max(extract(doy from day)) as doy from %s
WHERE month < 7 and low <= %s and low > -40 and station = '%s'
and year >= %s and year < %s and month > 1
GROUP by year ORDER by doy ASC
""" % (constants.get_table(station),
base, station, constants.startyear(station), constants._ENDYEAR)
cursor.execute(sql)
cnt_years[base] = cursor.rowcount
if cursor.rowcount == 0:
return
for row in cursor:
cnt[row['doy']][base] += 1.0
sts = mx.DateTime.DateTime(2000, 1, 1)
running = {32: 0.0, 28: 0.0, 26: 0.0, 22: 0.0}
out.write("""# Low Temperature exceedence probabilities
# (On a certain date, what is the chance a temperature below a certain
# threshold will be observed again that spring)
DOY Date <33 <29 <27 <23
""")
ar = []
for day in range(181, 29, -1):
ts = sts + mx.DateTime.RelativeDateTime(days=day-1)
for base in (32, 28, 26, 22):
running[base] += cnt[day][base]
if day % 2 == 0:
ar.append((" %3s %s %3i %3i %3i %3i"
"") % (ts.strftime("%-j"),
ts.strftime("%b %d"),
running[32] / cnt_years[32] * 100.0,
running[28] / cnt_years[28] * 100.0,
running[26] / cnt_years[26] * 100.0,
running[22] / cnt_years[22] * 100.0))
ar.reverse()
out.write("\n".join(ar))
def process(sid, csv):
# Fetch Yearly Totals
cursor.execute("""
SELECT year, round(avg(high)::numeric,1) as avg_high,
round(avg(low)::numeric,1) as avg_low,
round(sum(precip)::numeric,2) as rain from %s
WHERE station = '%s' and year >= %s GROUP by year ORDER by year ASC
""" % (constants.get_table(sid), sid, constants.startyear(sid)))
data = {}
for row in cursor:
year = row["year"]
data[year] = {
'oHigh': row["avg_high"],
'oLow': row["avg_low"],
'oRain': row["rain"]
}
for i in range(1893, constants._ENDYEAR):
if i not in data:
data[i] = {'oHigh': "M", 'oLow': "M", 'oRain': "M"}
csv.write("%s,%s,%s," %
(data[i]['oLow'], data[i]['oHigh'], data[i]['oRain']))
# Need to do climate stuff
# Then climate
cursor.execute("""
SELECT round(avg(high)::numeric,1) as avg_high,
round(avg(low)::numeric,1) as avg_low,
round(sum(precip)::numeric,2) as rain from %s WHERE station = '%s'
""" % (constants.climatetable(sid), sid))
row = cursor.fetchone()
aHigh = row["avg_high"]
aLow = row["avg_low"]
aRain = row["rain"]
csv.write("%s,%s,%s," % (aLow, aHigh, aRain))
csv.write("\n")
csv.flush()
def main():
"""Go Main Go"""
nt = NetworkTable("IACLIMATE")
pgconn = get_dbconn('coop', user='******')
ccursor = pgconn.cursor(cursor_factory=psycopg2.extras.DictCursor)
for sid in nt.sts:
fn = "%s/%s.csv" % (BASEDIR, nt.sts[sid]['name'].replace(" ", "_"))
out = open(fn, 'w')
out.write("station,station_name,lat,lon,day,high,low,precip,snow,\n")
sql = """
SELECT * from %s WHERE station = '%s' ORDER by day ASC
""" % (constants.get_table(sid), sid)
ccursor.execute(sql)
for row in ccursor:
out.write(("%s,%s,%s,%s,%s,%s,%s,%s,%s,\n") %
(sid.lower(), nt.sts[sid]['name'], nt.sts[sid]['lat'],
nt.sts[sid]['lon'], row['day'], row['high'], row['low'],
row['precip'], row['snow']))
out.close()
def write(cursor, out, station):
out.write("""# OF DAYS EACH YEAR WHERE MIN >=32 F\n""")
cursor.execute(
"""
SELECT year, count(low) from %s WHERE
station = '%s' and low >= 32 and day >= '%s-01-01'
and year < %s GROUP by year
"""
% (constants.get_table(station), station, constants.startyear(station), constants._THISYEAR)
)
tot = 0
d = {}
for row in cursor:
tot += row["count"]
d[row["year"]] = row["count"]
mean = tot / float(cursor.rowcount)
for year in range(constants.startyear(station), constants._THISYEAR):
out.write("%s %3i\n" % (year, d.get(year, 0)))
out.write("MEAN %3i\n" % (mean,))
def process(sid, csv):
# Fetch Yearly Totals
cursor.execute("""
SELECT year, round(avg(high)::numeric,1) as avg_high,
round(avg(low)::numeric,1) as avg_low,
round(sum(precip)::numeric,2) as rain from %s
WHERE station = '%s' and year >= %s GROUP by year ORDER by year ASC
""" % (constants.get_table(sid), sid, constants.startyear(sid)))
data = {}
for row in cursor:
year = row["year"]
data[year] = {'oHigh': row["avg_high"], 'oLow': row["avg_low"],
'oRain': row["rain"]}
for i in range(1893, constants._ENDYEAR):
if i not in data:
data[i] = {'oHigh': "M", 'oLow': "M", 'oRain': "M"}
csv.write("%s,%s,%s," % (data[i]['oLow'], data[i]['oHigh'],
data[i]['oRain']))
# Need to do climate stuff
# Then climate
cursor.execute("""
SELECT round(avg(high)::numeric,1) as avg_high,
round(avg(low)::numeric,1) as avg_low,
round(sum(precip)::numeric,2) as rain from %s WHERE station = '%s'
""" % (constants.climatetable(sid), sid))
row = cursor.fetchone()
aHigh = row["avg_high"]
aLow = row["avg_low"]
aRain = row["rain"]
csv.write("%s,%s,%s," % (aLow, aHigh, aRain))
csv.write("\n")
csv.flush()
def write(mydb, out, station):
out.write("""# OF DAYS EACH YEAR WHERE MIN >=32 F\n""")
rs = mydb.query("""SELECT year, count(low) from %s WHERE
station = '%s' and low >= 32 and day >= '%s-01-01'
and year < %s GROUP by year""" % (constants.get_table(station),
station,
constants.startyear(station),
constants._THISYEAR) ).dictresult()
tot = 0
d = {}
for yr in range(constants.startyear(station), constants._THISYEAR):
d[yr] = 0
for i in range(len(rs)):
tot += int(rs[i]["count"])
d[ int(rs[i]["year"]) ] = int(rs[i]["count"])
mean = tot / len(rs)
for yr in range(constants.startyear(station), constants._THISYEAR):
out.write("%s %3i\n" % (yr, d[yr]))
out.write("MEAN %3i\n" % (mean,) )
def write(cursor, out, station):
out.write(("# THESE ARE THE HEAT STRESS VARIABLES FOR STATION # %s\n"
) % (station,))
s = constants.startts(station)
e = constants._ENDTS
interval = mx.DateTime.RelativeDateTime(months=+1)
monthlyCount = {}
monthlyIndex = {}
now = s
while now < e:
monthlyCount[now] = 0
monthlyIndex[now] = 0
now += interval
cursor.execute("""
SELECT year, month, high from %s WHERE
station = '%s' and high > 86 and day >= '%s-01-01
'""" % (constants.get_table(station), station,
constants.startyear(station)))
for row in cursor:
ts = mx.DateTime.DateTime(row["year"], row["month"], 1)
monthlyCount[ts] += 1
monthlyIndex[ts] += int(row["high"]) - 86
monthlyAveCnt = {}
monthlyAveIndex = {}
for mo in range(5, 10):
monthlyAveCnt[mo] = 0
monthlyAveIndex[mo] = 0
out.write(""" # OF DAYS MAXT >86 ACCUMULATED (MAXT - 86 )
YEAR MAY JUNE JULY AUG SEPT TOTAL MAY JUNE JULY AUG SEPT TOTAL\n""")
yrCnt = 0
for yr in range(constants.startyear(station), constants._ENDYEAR):
yrCnt += 1
out.write("%5s" % (yr,))
totCnt = 0
for mo in range(5, 10):
ts = mx.DateTime.DateTime(yr, mo, 1)
if (ts >= constants._ARCHIVEENDTS):
out.write("%6s" % ("M",))
continue
totCnt += monthlyCount[ts]
monthlyAveCnt[mo] += monthlyCount[ts]
out.write("%6i" % (monthlyCount[ts], ))
out.write("%6i " % (totCnt,))
totInd = 0
for mo in range(5, 10):
ts = mx.DateTime.DateTime(yr, mo, 1)
if (ts >= constants._ARCHIVEENDTS):
out.write("%6s" % ("M",))
continue
totInd += monthlyIndex[ts]
monthlyAveIndex[mo] += monthlyIndex[ts]
out.write("%6i" % (monthlyIndex[ts], ))
out.write("%6i\n" % (totInd,))
out.write(" **************************************************************************************\n")
out.write("MEANS")
tot = 0
for mo in range(5, 10):
val = float(monthlyAveCnt[mo]) / float(yrCnt)
tot += val
out.write("%6.1f" % (val, ))
out.write("%6.1f " % (tot,))
tot = 0
for mo in range(5, 10):
val = float(monthlyAveIndex[mo]) / float(yrCnt)
tot += val
out.write("%6.1f" % (val, ))
out.write("%6.1f\n" % (tot, ))
# This will drive the modules
import pg, string, constants
import network
nt = network.Table("IACLIMATE")
mydb = pg.connect("coop", 'iemdb',user='******')
import genPrecipEvents, genGDD, genDailyRecords
import genDailyRecordsRain, genDailyRange, genDailyMeans, genCountLows32
import genSpringFall, genMonthly, genHDD, genCDD, genHeatStress
import genCountRain, genFrostProbabilities, genSpringProbabilities, genCycles
import genTempThresholds, genRecordPeriods, gen_precip_cats
updateAll = True
#for id in st.ids:
for id in ['IA0200',]:
print "processing [%s] %s" % (id, nt.sts[id]["name"])
dbid = string.upper(id)
rs = mydb.query("""SELECT * from %s WHERE station = '%s' and
day >= '%s-01-01' ORDER by day ASC""" % (
constants.get_table(dbid), dbid, constants.startyear(dbid) ) ).dictresult()
#genSpringFall.write(mydb, rs, dbid, 32, "09")
genHDD.go(mydb, rs, dbid, updateAll)
genHDD.write(mydb, dbid)
"""Dump!"""
import constants
from pyiem.network import Table as NetworkTable
import psycopg2.extras
nt = NetworkTable("IACLIMATE")
COOP = psycopg2.connect(database='coop', host='iemdb', user='******')
ccursor = COOP.cursor(cursor_factory=psycopg2.extras.DictCursor)
BASEDIR = "/mesonet/share/pickup/coop_data"
for sid in nt.sts.keys():
fn = "%s/%s.csv" % (BASEDIR, nt.sts[sid]['name'].replace(" ", "_"))
out = open(fn, 'w')
out.write("station,station_name,lat,lon,day,high,low,precip,snow,\n")
sql = """
SELECT * from %s WHERE station = '%s' ORDER by day ASC
""" % (constants.get_table(sid), sid)
ccursor.execute(sql)
for row in ccursor:
out.write(("%s,%s,%s,%s,%s,%s,%s,%s,%s,\n"
"") % (sid.lower(), nt.sts[sid]['name'], nt.sts[sid]['lat'],
nt.sts[sid]['lon'], row['day'], row['high'],
row['low'], row['precip'], row['snow']))
out.close()
"""Dump!"""
import constants
from pyiem.network import Table as NetworkTable
nt = NetworkTable("IACLIMATE")
import psycopg2.extras
COOP = psycopg2.connect(database='coop', host='iemdb', user='******')
ccursor = COOP.cursor(cursor_factory=psycopg2.extras.DictCursor)
BASEDIR = "/mesonet/share/pickup/coop_data"
for sid in nt.sts.keys():
fn = "%s/%s.csv" % (BASEDIR, nt.sts[sid]['name'].replace(" ", "_"))
out = open(fn, 'w')
out.write("station,station_name,lat,lon,day,high,low,precip,snow,\n")
sql = """
SELECT * from %s WHERE station = '%s' ORDER by day ASC
""" % (constants.get_table(sid), sid)
ccursor.execute(sql)
for row in ccursor:
out.write(("%s,%s,%s,%s,%s,%s,%s,%s,%s,\n"
"") % (sid.lower(), nt.sts[sid]['name'], nt.sts[sid]['lat'],
nt.sts[sid]['lon'], row['day'], row['high'],
row['low'], row['precip'], row['snow']))
out.close()
import psycopg2.extras
nt = NetworkTable("IACLIMATE")
COOP = psycopg2.connect(database="coop", host="iemdb", user="******")
ccursor = COOP.cursor(cursor_factory=psycopg2.extras.DictCursor)
BASEDIR = "/mesonet/share/pickup/coop_data"
for sid in nt.sts.keys():
fn = "%s/%s.csv" % (BASEDIR, nt.sts[sid]["name"].replace(" ", "_"))
out = open(fn, "w")
out.write("station,station_name,lat,lon,day,high,low,precip,snow,\n")
sql = """
SELECT * from %s WHERE station = '%s' ORDER by day ASC
""" % (
constants.get_table(sid),
sid,
)
ccursor.execute(sql)
for row in ccursor:
out.write(
("%s,%s,%s,%s,%s,%s,%s,%s,%s,\n" "")
% (
sid.lower(),
nt.sts[sid]["name"],
nt.sts[sid]["lat"],
nt.sts[sid]["lon"],
row["day"],
row["high"],
row["low"],
"""
Dump!
"""
import constants
import network
nt = network.Table("IACLIMATE")
import iemdb
import psycopg2.extras
COOP = iemdb.connect('coop', bypass=True)
ccursor = COOP.cursor(cursor_factory=psycopg2.extras.DictCursor)
for id in nt.sts.keys():
fn = "/mesonet/share/climodat/coop_data/%s.csv" % (nt.sts[id]['name'].replace(" ", "_"), )
out = open(fn, 'w')
out.write("station,station_name,lat,lon,day,high,low,precip,snow,\n")
sql = "SELECT * from %s WHERE station = '%s' ORDER by day ASC" % (
constants.get_table(id), id)
ccursor.execute( sql )
for row in ccursor:
out.write("%s,%s,%s,%s,%s,%s,%s,%s,%s,\n" % (id.lower(),
nt.sts[id]['name'], nt.sts[id]['lat'], nt.sts[id]['lon'],
row['day'], row['high'], row['low'], row['precip'],
row['snow']) )
out.close()
#stdlib
import datetime
def caller(func, *args):
#start = datetime.datetime.now()
ret = func(*args)
#end = datetime.datetime.now()
#print "%s %s took %s" % (func.__name__, args[-1], (end-start))
return ret
updateAll= False
for dbid in nt.sts.keys():
#print "processing [%s] %s" % (dbid, nt.sts[dbid]["name"])
sql = """SELECT d.*, c.climoweek from %s d, climoweek c
WHERE station = '%s' and day >= '%s-01-01' and d.sday = c.sday
ORDER by day ASC""" % (constants.get_table(dbid),
dbid, constants.startyear(dbid) )
rs = caller(mydb.query, sql).dictresult()
caller(genPrecipEvents.go, mydb, rs, dbid)
out = caller(constants.make_output, nt, dbid, "01")
caller(genPrecipEvents.write, mydb, out, dbid)
out.close()
out = caller(constants.make_output, nt, dbid, "02")
caller(gen30rains.write, mydb, out, dbid)
out.close()
caller(genGDD.go, mydb, rs, dbid, updateAll)
out = caller(constants.make_output, nt, dbid, "03")
def write(cursor, out, station):
""" Do our business """
table = constants.get_table(station)
cursor.execute("""
with events as (
SELECT c.climoweek, a.precip, a.year from """+table+""" a
JOIN climoweek c on (c.sday = a.sday) WHERE a.station = %s
and precip >= 0.01),
ranks as (
SELECT climoweek, year,
rank() OVER (PARTITION by climoweek ORDER by precip DESC)
from events),
stats as (
SELECT climoweek, max(precip), avg(precip),
sum(case when precip >= 0.01 and precip < 0.26 then 1 else 0 end) as cat1,
sum(case when precip >= 0.26 and precip < 0.51 then 1 else 0 end) as cat2,
sum(case when precip >= 0.51 and precip < 1.01 then 1 else 0 end) as cat3,
sum(case when precip >= 1.01 and precip < 2.01 then 1 else 0 end) as cat4,
sum(case when precip >= 2.01 then 1 else 0 end) as cat5,
count(*) from events GROUP by climoweek)
SELECT e.climoweek, e.max, r.year, e.avg, e.cat1, e.cat2, e.cat3, e.cat4,
e.cat5 from
stats e JOIN ranks r on (r.climoweek = e.climoweek) WHERE r.rank = 1
ORDER by e.climoweek ASC
""", (station,))
out.write("""\
# Based on climoweek periods, this report summarizes liquid precipitation.
# Number of precip events - (% of total)
CL MAX MEAN 0.01- 0.26- 0.51- 1.01- TOTAL
WK TIME PERIOD VAL YR RAIN 0.25 0.50 1.00 2.00 >2.01 DAYS
""")
annEvents = 0
cat1t = 0
cat2t = 0
cat3t = 0
cat4t = 0
cat5t = 0
maxRain = 0
totRain = 0
lastcw = 0
for row in cursor:
cw = int(row["climoweek"])
# Skip ties
if cw == lastcw:
continue
lastcw = cw
cat1 = row["cat1"]
cat2 = row["cat2"]
cat3 = row["cat3"]
cat4 = row["cat4"]
cat5 = row["cat5"]
cat1t += cat1
cat2t += cat2
cat3t += cat3
cat4t += cat4
cat5t += cat5
maxval = row["max"]
if maxval > maxRain:
maxRain = maxval
meanval = row["avg"]
totEvents = cat1 + cat2 + cat3 + cat4 + cat5
annEvents += totEvents
totRain += ( totEvents * meanval)
out.write(("%3s %-13s %5.2f %i %4.2f %4i(%2i) %4i(%2i) "
+"%4i(%2i) %4i(%2i) %4i(%2i) %4i\n") % (
cw, cweek[cw],
maxval, row['year'], meanval,
cat1, round((float(cat1) / float(totEvents)) * 100.0),
cat2, round((float(cat2) / float(totEvents)) * 100.0),
cat3, round((float(cat3) / float(totEvents)) * 100.0),
cat4, round((float(cat4) / float(totEvents)) * 100.0),
cat5, round((float(cat5) / float(totEvents)) * 100.0), totEvents) )
out.write("%-17s %5.2f %4.2f %4i(%2i) %4i(%2i) %4i(%2i) %4i(%2i) %4i(%2i) %5i\n" % (
"ANNUAL TOTALS", maxRain, totRain / annEvents,
cat1t, (float(cat1t) / float(annEvents)) * 100,
cat2t, (float(cat2t) / float(annEvents)) * 100,
cat3t, (float(cat3t) / float(annEvents)) * 100,
cat4t, (float(cat4t) / float(annEvents)) * 100,
cat5t, (float(cat5t) / float(annEvents)) * 100, annEvents) )
def run_station(dbid):
"""Actually run for the given station"""
table = constants.get_table(dbid)
# print "processing [%s] %s" % (dbid, constants.nt.sts[dbid]["name"])
sql = """
SELECT d.*, c.climoweek from %s d, climoweek c
WHERE station = '%s' and day >= '%s-01-01' and d.sday = c.sday
and precip is not null
ORDER by day ASC
""" % (table, dbid, constants.startyear(dbid))
rs = caller(mydb.query, sql).dictresult()
# Compute monthly
cursor.execute("""
SELECT year, month, sum(precip) as sum_precip,
avg(high) as avg_high,
avg(low) as avg_low,
sum(cdd(high,low,60)) as cdd60,
sum(cdd(high,low,65)) as cdd65,
sum(hdd(high,low,60)) as hdd60,
sum(hdd(high,low,65)) as hdd65,
sum(case when precip >= 0.01 then 1 else 0 end) as rain_days,
sum(case when snow >= 0.1 then 1 else 0 end) as snow_days,
sum(gddxx(40,86,high,low)) as gdd40,
sum(gddxx(48,86,high,low)) as gdd48,
sum(gddxx(50,86,high,low)) as gdd50,
sum(gddxx(52,86,high,low)) as gdd52
from """+table+""" WHERE station = %s GROUP by year, month
""", (dbid, ))
monthly_rows = cursor.fetchall()
out = constants.make_output(constants.nt, dbid, "01")
caller(genPrecipEvents.write, cursor, out, dbid)
out.close()
out = constants.make_output(constants.nt, dbid, "02")
caller(gen30rains.write, mydb, out, dbid)
out.close()
out = constants.make_output(constants.nt, dbid, "03")
caller(genGDD.write, monthly_rows, out, dbid)
out.close()
out = constants.make_output(constants.nt, dbid, "04")
caller(genDailyRecords.write, mydb, out, dbid)
out.close()
out = constants.make_output(constants.nt, dbid, "05")
caller(genDailyRecordsRain.write, mydb, out, dbid)
out.close()
out = constants.make_output(constants.nt, dbid, "06")
caller(genDailyRange.write, mydb, out, dbid)
out.close()
out = constants.make_output(constants.nt, dbid, "07")
caller(genDailyMeans.write, mydb, out, dbid)
out.close()
out = constants.make_output(constants.nt, dbid, "08")
caller(genCountLows32.write, cursor, out, dbid)
out.close()
out = constants.make_output(constants.nt, dbid, "09")
caller(genSpringFall.write, out, rs, dbid, 32)
out.close()
out = constants.make_output(constants.nt, dbid, "10")
caller(genSpringFall.write, out, rs, dbid, 30)
out.close()
out = constants.make_output(constants.nt, dbid, "11")
caller(genSpringFall.write, out, rs, dbid, 28)
out.close()
out = constants.make_output(constants.nt, dbid, "12")
caller(genSpringFall.write, out, rs, dbid, 26)
out.close()
out = constants.make_output(constants.nt, dbid, "13")
caller(genSpringFall.write, out, rs, dbid, 24)
out.close()
out = constants.make_output(constants.nt, dbid, "14")
out2 = constants.make_output(constants.nt, dbid, "15")
out3 = constants.make_output(constants.nt, dbid, "16")
out4 = constants.make_output(constants.nt, dbid, "17")
caller(genMonthly.write, monthly_rows, out, out2, out3, out4, dbid)
out.close()
out2.close()
out3.close()
out4.close()
out = constants.make_output(constants.nt, dbid, "18")
caller(genHDD.write, monthly_rows, out, dbid)
out.close()
out = constants.make_output(constants.nt, dbid, "19")
caller(genCDD.write, monthly_rows, out, dbid)
out.close()
out = constants.make_output(constants.nt, dbid, "20")
caller(genHeatStress.write, cursor, out, dbid)
out.close()
out = constants.make_output(constants.nt, dbid, "21")
caller(genCountRain.write, monthly_rows, out, dbid)
out.close()
out = constants.make_output(constants.nt, dbid, "25")
caller(genCountSnow.write, monthly_rows, out, dbid)
out.close()
out = constants.make_output(constants.nt, dbid, "22")
caller(genFrostProbabilities.write, mydb, out, dbid)
out.close()
out = constants.make_output(constants.nt, dbid, "23")
caller(genSpringProbabilities.write, cursor, out, dbid)
out.close()
out = constants.make_output(constants.nt, dbid, "24")
caller(genCycles.write, out, rs, dbid)
out.close()
out = constants.make_output(constants.nt, dbid, "26")
caller(genTempThresholds.write, cursor, out, dbid)
out.close()
out = constants.make_output(constants.nt, dbid, "27")
caller(genRecordPeriods.write, mydb, out, rs, dbid)
out.close()
out = constants.make_output(constants.nt, dbid, "28")
caller(gen_precip_cats.write, mydb, out, rs, dbid)
out.close()
