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 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()