db_weather.py
'''\
Weather Database Table classes.
classes:
dbWeatherTable (dbTable)
dbWeatherStationTable (dbWeatherTable)
dbLocalClimateDataTable (dbWeatherTable)
dbWeatherStationTable methods:
create_table ()
destroy_table ()
list_to_file ()
dbLocalClimateDataTable methods:
create_table ()
destroy_table ()
load_records (tab_filename)
list_to_file ()
list_report_types ()
chart_month_temps (month, year=2017)
chart_month_precip (month, year=2017)
chart_year_temps (year)
chart_year_precip (year)
chart_decade_temps (year0)
chart_decade_precip (year0)
chart_months_temps (month, year_0, year_1)
chart_months_temps_hi (month, year_0, year_1)
chart_months_temps_lo (month, year_0, year_1)
chart_temp_min_max ()
chart_pressure ()
chart_rel_hum ()
Developer@Sonnack.com
June 2017
'''
from __future__ import print_function
from sys import argv, stdout, stderr
from os import path
from datetime import date, timedelta
import numpy as np
import matplotlib.pyplot as plt
from matplotlib.ticker import MultipleLocator, FormatStrFormatter
from matplotlib.dates import DayLocator, DateFormatter
from files import get_file_csv
from dbsql import dbTable
BasePath = r'C:\CJS\prj\Python\weather'
DataPath = r'C:\CJS\prj\Python\weather\data'
ImgsPath = r'C:\CJS\prj\Python\weather\imgs'
class dbWeatherTable (dbTable):
'''Base class for Weather tables.'''
dbName = 'weather.sqlite'
dataPath = DataPath
imgsPath = ImgsPath
def __init__(self, tab_name, tab_cols):
dbTable.__init__(self, path.join(self.dataPath,self.dbName), tab_name, tab_cols)
self.col_headers = [h[1:-1] if h.startswith('"') else h for h in self.col_names]
class dbWeatherStationTable (dbWeatherTable):
tabName = 'stations'
tabCols = ['"station" TEXT NOT NULL UNIQUE'
,'"name" TEXT NOT NULL UNIQUE'
,'"elevation" REAL DEFAULT 0.0'
,'"latitude" REAL DEFAULT 0.0'
,'"longitude" REAL DEFAULT 0.0'
]
tabData = []
tabFilename = 'lcd_2010-2017.csv'
def __init__(self):
dbWeatherTable.__init__(self, self.tabName, self.tabCols)
def create_table (self):
self.Log.info('Create Stations Dataset')
self.db_create()
data = get_file_csv(path.join(self.dataPath,self.tabFilename))
data = [d[0:5] for d in data[1]]
stations = {}
for d in data: stations[d[0]] = d
self.tabData = list(stations.values())
self.db_insert_many(self.tabData)
for r in self.tabData:
self.Log.trace(r)
def destroy_table (self):
dbWeatherTable.destroy_table(self, 'Drop Stations Dataset')
def list_to_file (self, fname='stations.out'):
self.Log.debug('db[%s] ListToFile' % self.name)
h1 = 'Station-Id Station-Name Elev Lat. Long.\n'
h2 = '============ ================================================== ======= = ======= = =======\n'
fmt = '%-12s %-50s %+7.2f : %+7.2f x %+7.2f\n'
sql = 'SELECT * FROM "%s" ORDER BY name'
rs = self.db_query(sql % self.name)
fn = path.join(BasePath, fname)
fp = open(fn, 'w')
try:
fp.write(h1)
fp.write(h2)
for r in rs: fp.write(fmt % r)
except:
raise
finally:
fp.close()
self.Log.info('wrote: %s' % fn)
class dbLocalClimateDataTable (dbWeatherTable):
tabName = 'local_climate_data'
tabCols = ['"station" TEXT DEFAULT NULL'
,'"date" TEXT DEFAULT NULL'
,'"time" TEXT DEFAULT NULL'
,'"report_type" TEXT DEFAULT NULL'
,'"hr_skycondits" TEXT DEFAULT NULL'
,'"hr_visibility" REAL DEFAULT NULL'
,'"hr_curr_weather_type" TEXT DEFAULT NULL'
,'"hr_drybulb_tempf" INTEGER DEFAULT NULL'
,'"hr_drybulb_tempc" REAL DEFAULT NULL'
,'"hr_wetbulb_tempf" INTEGER DEFAULT NULL'
,'"hr_wetbulb_tempc" REAL DEFAULT NULL'
,'"hr_dewpoint_tempf" INTEGER DEFAULT NULL'
,'"hr_dewpoint_tempc" REAL DEFAULT NULL'
,'"hr_rel_hum" INTEGER DEFAULT NULL'
,'"hr_wind_spd" REAL DEFAULT NULL'
,'"hr_wind_dir" REAL DEFAULT NULL'
,'"hr_wind_gust_spd" REAL DEFAULT NULL'
,'"hr_station_press" REAL DEFAULT NULL'
,'"hr_press_tendency" INTEGER DEFAULT NULL'
,'"hr_press_change" REAL DEFAULT NULL'
,'"hr_sealevel_press" REAL DEFAULT NULL'
,'"hr_precip" REAL DEFAULT NULL'
,'"hr_altimeter_setting" REAL DEFAULT NULL'
,'"day_max_drybulb_temp" INTEGER DEFAULT NULL'
,'"day_min_drybulb_temp" INTEGER DEFAULT NULL'
,'"day_avg_drybulb_temp" INTEGER DEFAULT NULL'
,'"day_dept_from_norm_avg_temp" REAL DEFAULT NULL'
,'"day_avg_rel_hum" REAL DEFAULT NULL'
,'"day_avg_dewpoint_temp" REAL DEFAULT NULL'
,'"day_avg_wetbulb_temp" REAL DEFAULT NULL'
,'"day_heat_deg_days" INTEGER DEFAULT NULL'
,'"day_cool_deg_days" INTEGER DEFAULT NULL'
,'"day_sunrise" TEXT DEFAULT NULL'
,'"day_sunset" TEXT DEFAULT NULL'
,'"day_weather" TEXT DEFAULT NULL'
,'"day_precip" REAL DEFAULT NULL'
,'"day_snowfall" REAL DEFAULT NULL'
,'"day_snowdepth" TEXT DEFAULT NULL'
,'"day_avg_station_press" REAL DEFAULT NULL'
,'"day_avg_sealevel_press" REAL DEFAULT NULL'
,'"day_avg_wind_spd" REAL DEFAULT NULL'
,'"day_peak_wind_spd" REAL DEFAULT NULL'
,'"peak_wind_dir" REAL DEFAULT NULL'
,'"day_sus_wind_spd" REAL DEFAULT NULL'
,'"day_sus_wind_dir" REAL DEFAULT NULL'
,'"mon_max_temp" REAL DEFAULT NULL'
,'"mon_min_temp" REAL DEFAULT NULL'
,'"mon_mean_temp" REAL DEFAULT NULL'
,'"mon_avg_rh" TEXT DEFAULT NULL'
,'"mon_dewpoint_temp" TEXT DEFAULT NULL'
,'"mon_wetbulb_temp" TEXT DEFAULT NULL'
,'"mon_avg_heat_deg_days" INTEGER DEFAULT NULL'
,'"mon_avg_cool_deg_days" INTEGER DEFAULT NULL'
,'"mon_station_press" REAL DEFAULT NULL'
,'"mon_sealevel_press" REAL DEFAULT NULL'
,'"mon_avg_wind_spd" REAL DEFAULT NULL'
,'"mon_tot_snowfall" REAL DEFAULT NULL'
,'"mon_dept_from_norm_max_temp" REAL DEFAULT NULL'
,'"mon_dept_from_norm_min_temp" REAL DEFAULT NULL'
,'"mon_dept_from_norm_avg_temp" REAL DEFAULT NULL'
,'"mon_dept_from_norm_precip" REAL DEFAULT NULL'
,'"mon_tot_liquid_precip" REAL DEFAULT NULL'
,'"mon_greatest_precip" REAL DEFAULT NULL'
,'"mon_greatest_precipdate" REAL DEFAULT NULL'
,'"mon_greatest_snowfall" REAL DEFAULT NULL'
,'"mon_greatest_snowfalldate" TEXT DEFAULT NULL'
,'"mon_greatest_snowdepth" REAL DEFAULT NULL'
,'"mon_greatest_snowdepthdate" TEXT DEFAULT NULL'
,'"mon_dayswith_gt90_temp" REAL DEFAULT NULL'
,'"mon_dayswith_lt32_temp" REAL DEFAULT NULL'
,'"mon_dayswith_gt32_temp" REAL DEFAULT NULL'
,'"mon_dayswith_lt0_temp" REAL DEFAULT NULL'
,'"mon_dayswith_gt001_precip" REAL DEFAULT NULL'
,'"mon_dayswith_gt010_precip" REAL DEFAULT NULL'
,'"mon_dayswith_gt1_snow" REAL DEFAULT NULL'
,'"mon_max_sealevel_pressvalue" REAL DEFAULT NULL'
,'"mon_max_sealevel_pressdate" TEXT DEFAULT NULL'
,'"mon_max_sealevel_presstime" TEXT DEFAULT NULL'
,'"mon_min_sealevel_pressvalue" REAL DEFAULT NULL'
,'"mon_min_sealevel_pressdate" TEXT DEFAULT NULL'
,'"mon_min_sealevel_presstime" TEXT DEFAULT NULL'
,'"mon_tot_heat_deg_days" INTEGER DEFAULT NULL'
,'"mon_tot_cool_deg_days" INTEGER DEFAULT NULL'
,'"mon_dept_from_norm_heat_dd" TEXT DEFAULT NULL'
,'"mon_dept_from_norm_cool_dd" TEXT DEFAULT NULL'
,'"mon_tot_sea_todate_heat_dd" TEXT DEFAULT NULL'
,'"mon_tot_sea_todate_cool_dd" TEXT DEFAULT NULL'
]
tabData = []
tabFilename = 'lcd_2000-2017.csv'
def __init__(self):
dbWeatherTable.__init__(self, self.tabName, self.tabCols)
self.snow_fall_factor = 4.0
def create_table (self):
self.Log.info('Create LCD Dataset')
self.db_create()
self.load_records(self.tabFilename)
def destroy_table (self):
dbWeatherTable.destroy_table(self, 'Drop LCD Dataset')
def load_records (self, tab_filename):
data = get_file_csv(path.join(self.dataPath, tab_filename))
self.tabData = [d[0:1]+[d[5][0:10],d[5][11:16]]+d[6:] for d in data[1]]
self.db_insert_many(self.tabData)
def list_to_file (self, fname='lcd-data.out'):
self.Log.debug('db[%s] ListToFile' % self.name)
h1 = 'Date #Rs Max/Low Rel.H W.Spd Press\n'
h2 = '==========-===- ===-=== ===== ===== =====\n'
fmt = '%s[%3d] %+3d/%+3d %5.2f %4.1f %5.2f\n'
sql = Queries['list']
rs = self.db_query(sql % self.name)
fn = path.join(BasePath, fname)
fp = open(fn, 'w')
try:
fp.write(h1)
fp.write(h2)
for r in rs: fp.write(fmt % r)
except: raise
finally: fp.close()
self.Log.info('wrote: %s' % fn)
def list_report_types (self, fname='report-types.out'):
self.Log.debug('db[%s] List(report_type)' % self.name)
h1 = 'Report Type Rcds\n'
h2 = '=========== ====\n'
fmt = '%-11s %4d\n'
sql = Queries['report-types']
rs = self.db_query(sql % self.name)
fn = path.join(BasePath, fname)
fp = open(fn, 'w')
try:
fp.write(h1)
fp.write(h2)
for r in rs: fp.write(fmt % r)
except: raise
finally: fp.close()
self.Log.info('wrote: %s' % fn)
def chart_month_temps (self, month, year=2017, fname='temps_%04d-%02d.png'):
self.Log.debug('db[%s] Chart(month.temps)' % self.name)
fn = path.join(self.imgsPath,fname % (year,month))
ps = ['SOD', '%04d'%year, '%02d'%month]
sql = Queries['month.temps.c']
rs = self.db_query(sql % self.name, ps)
rs = [r for r in rs if isinstance(r[3],int) and isinstance(r[4],int)]
if not rs: rs = [(year,month,15,+1,-1)]
dtYs,dtMs,dtDs,tmin,tmax = zip(*rs)
dts = [date(dy,dm,dd) for dy,dm,dd in zip(dtYs,dtMs,dtDs)]
tmax_max = max(tmax)
tmax_min = min(tmax)
tmin_max= max(tmin)
tmin_min= min(tmin)
ttl = 'Hi/Lo Temps %s'
ylab = 'Temperature'
fig, ax = self.__chart_month('temps', month,year,0, title=ttl, ylab=ylab)
ax.axhspan(tmax_min,tmax_max, color='#ff0000' , lw=1.0, alpha=0.10, zorder=7)
ax.axhspan(tmin_min,tmin_max, color='#0000ff' , lw=1.0, alpha=0.10, zorder=6)
ax.fill_between(dts,tmin,tmax, color='#000000', alpha=0.10, zorder=8)
ax.plot(dts,tmax, color='#bf3f00', lw=1.5, alpha=1.00, zorder=9, marker='*',ms=5)
ax.plot(dts,tmin, color='#003fbf', lw=1.5, alpha=1.00, zorder=9, marker='*',ms=5)
self.__chart_month_save(fig, fn)
return rs
def chart_month_precip (self, month, year=2017, fname='precip_%04d-%02d.png'):
self.Log.debug('db[%s] Chart(month.precip)' % self.name)
fn = path.join(self.imgsPath,fname % (year,month))
ps = ['SOD', '%04d'%year, '%02d'%month]
sql = Queries['month.precip.c']
rs = self.db_query(sql % self.name, ps)
rain = [(date(r[0],r[1],r[2]),r[3]) for r in rs if isinstance(r[3],float)]
snow = [(date(r[0],r[1],r[2]),r[4]) for r in rs if isinstance(r[4],float)]
if rain: rain_dts, rain_vals = zip(*rain)
if snow: snow_dts, snow_vals = zip(*snow)
if snow: snow_vals = [v/self.snow_fall_factor for v in snow_vals]
ttl = 'Rain & Snow %s'
ylab = 'Precip (inches)'
fig, ax = self.__chart_month('precip', month,year,0, title=ttl, ylab=ylab)
if rain: ax.fill_between(rain_dts,rain_vals,0, color='#007fff', alpha=0.95, zorder=9)
if snow: ax.fill_between(snow_dts,snow_vals,0, color='#3f3f3f', alpha=0.75, zorder=8)
self.__chart_month_save(fig, fn)
return rs
def chart_months_temps (self, month, year_0, year_1, fname='temps_%02d-%04d-%04d.png'):
self.Log.debug('db[%s] Chart(months.temps)' % self.name)
fn = path.join(self.imgsPath,fname % (month,year_0,year_1))
ps = ['SOD', '%02d'%month, '%4d'%year_0, '%4d'%year_1]
sql = Queries['months.temps.c']
rs = self.db_query(sql % self.name, ps)
rs = [r for r in rs if isinstance(r[3],int) and isinstance(r[4],int)]
if not rs: self.Log.warn('No Data!'); return []
years = {}
for dtY,dtM,dtD,tmin,tmax in rs:
if dtY not in years: years[dtY] = []
years[dtY].append((dtM,dtD,tmin,tmax))
ttl = 'Hi-Lo Temps %s-%s'
ylab = 'Temperature'
fig, ax = self.__chart_month('temps', month,year_0,year_1, title=ttl, ylab=ylab)
for ix,yr in enumerate(reversed(years.keys())):
series = years[yr]
dts = [date(year_0,d[0],d[1]) for d in series]
tmin = [d[2] for d in series]
tmax = [d[3] for d in series]
ax.fill_between(dts,tmax,tmin, label=str(yr), color=(0.05*ix, 0.05*ix, 1.0), alpha=0.50, zorder=9+ix)
self.__chart_month_save(fig, fn)
return rs
def chart_months_temps_hi (self, month, year_0, year_1, fname='temps_hi_%02d-%04d-%04d.png'):
self.Log.debug('db[%s] Chart(months.temps.hi)' % self.name)
fn = path.join(self.imgsPath,fname % (month,year_0,year_1))
ps = ['SOD', '%02d'%month, '%4d'%year_0, '%4d'%year_1]
sql = Queries['months.temps.hi.c']
rs = self.db_query(sql % self.name, ps)
rs = [r for r in rs if isinstance(r[3],int)]
if not rs: self.Log.warn('No Data!'); return []
years = {}
for dtY,dtM,dtD,tmax in rs:
if dtY not in years: years[dtY] = []
years[dtY].append((dtM,dtD,tmax))
ttl = 'Hi Temps %s-%s'
ylab = 'Temperature'
fig, ax = self.__chart_month('temps', month,year_0,year_1, title=ttl, ylab=ylab)
for ix,yr in enumerate(reversed(years.keys())):
series = years[yr]
dts = [date(year_0,d[0],d[1]) for d in series]
tmax = [d[2] for d in series]
ax.plot(dts,tmax, label=str(yr), color=(1.0, 0.05*ix, 0.05*ix), alpha=0.75, zorder=9)
self.__chart_month_save(fig, fn)
return rs
def chart_months_temps_lo (self, month, year_0, year_1, fname='temps_lo_%02d-%04d-%04d.png'):
self.Log.debug('db[%s] Chart(months.temps.lo)' % self.name)
fn = path.join(self.imgsPath,fname % (month,year_0,year_1))
ps = ['SOD', '%02d'%month, '%4d'%year_0, '%4d'%year_1]
sql = Queries['months.temps.lo.c']
rs = self.db_query(sql % self.name, ps)
rs = [r for r in rs if isinstance(r[3],int)]
if not rs: self.Log.warn('No Data!'); return []
years = {}
for dtY,dtM,dtD,tmin in rs:
if dtY not in years: years[dtY] = []
years[dtY].append((dtM,dtD,tmin))
ttl = 'Lo Temps %s-%s'
ylab = 'Temperature'
fig, ax = self.__chart_month('temps', month,year_0,year_1, title=ttl, ylab=ylab)
for ix,yr in enumerate(reversed(years.keys())):
series = years[yr]
dts = [date(year_0,d[0],d[1]) for d in series]
tmin = [d[2] for d in series]
ax.plot(dts,tmin, label=str(yr), color=(0.05*ix, 0.05*ix, 1.0), alpha=0.75, zorder=9)
self.__chart_month_save(fig, fn)
return rs
def chart_year_temps (self, year, fname='temps_%04d.png'):
self.Log.debug('db[%s] Chart(year.temps)' % self.name)
fn = path.join(self.imgsPath,fname % year)
ps = ['SOD', '%4d'%year]
sql = Queries['year.temps.c']
rs = self.db_query(sql % self.name, ps)
rs = [r for r in rs if isinstance(r[3],int) and isinstance(r[4],int)]
if not rs: rs = [(year,1,1,+1,-1)]
dts = [date(r[0],r[1],r[2]) for r in rs]
tmin = [r[3] for r in rs]
tmax = [r[4] for r in rs]
ttl = 'Hi/Lo Temps %04d'
ylab = 'Temperature'
fig, ax = self.__chart_year('temps', year, title=ttl, ylab=ylab)
ax.fill_between(dts,tmax,tmin, color='#007fbf', alpha=0.70, zorder=9)
self.__chart_year_save(fig, fn)
return rs
def chart_year_precip (self, year, fname='precip_%04d.png'):
self.Log.debug('db[%s] Chart(year.precip)' % self.name)
fn = path.join(self.imgsPath,fname % year)
ps = ['SOD', '%4d'%year]
sql = Queries['year.precip.c']
rs = self.db_query(sql % self.name, ps)
rain = [(date(r[0],r[1],r[2]),r[3]) for r in rs if isinstance(r[3],float)]
snow = [(date(r[0],r[1],r[2]),r[4]) for r in rs if isinstance(r[4],float)]
if rain: rain_dts, rain_vals = zip(*rain)
if snow: snow_dts, snow_vals = zip(*snow)
if snow: snow_vals = [v/self.snow_fall_factor for v in snow_vals]
ttl = 'Rain & Snow %04d'
ylab = 'Precip (inches)'
fig, ax = self.__chart_year('precip', year, title=ttl, ylab=ylab)
if rain: ax.fill_between(rain_dts,rain_vals,0, color='#007fff', alpha=0.95, zorder=9)
if snow: ax.fill_between(snow_dts,snow_vals,0, color='#3f3f3f', alpha=0.75, zorder=8)
self.__chart_year_save(fig, fn)
return rs
def chart_decade_temps (self, year0, fname='temps_%04d-%04d.png'):
self.Log.debug('db[%s] Chart(decade.temps)' % self.name)
fn = path.join(self.imgsPath,fname % (year0,year0+9))
ps = ['SOD', '%4d'%year0, '%4d'%(year0+10)]
sql = Queries['decade.temps.c']
rs = self.db_query(sql % self.name, ps)
rs = [r for r in rs if isinstance(r[3],int) and isinstance(r[4],int)]
if not rs: self.Log.warn('No Data!'); return []
dts = [date(r[0],r[1],r[2]) for r in rs]
tmin = [r[3] for r in rs]
tmax = [r[4] for r in rs]
ttl = 'Hi/Lo Temps %04d-%04d'
ylab = 'Temperature'
fig, ax = self.__chart_decade('temps', year0, title=ttl, ylab=ylab)
ax.plot(dts,tmax, color='#ff0000', alpha=0.50, zorder=9)
ax.plot(dts,tmin, color='#0000ff', alpha=0.50, zorder=9)
self.__chart_decade_save(fig, fn)
return rs
def chart_decade_precip (self, year0, fname='precip_%04d-%04d.png'):
self.Log.debug('db[%s] Chart(decade.precip)' % self.name)
fn = path.join(self.imgsPath,fname % (year0,year0+9))
ps = ['SOD', '%4d'%year0, '%4d'%(year0+10)]
sql = Queries['decade.precip.c']
rs = self.db_query(sql % self.name, ps)
rain = [(date(r[0],r[1],r[2]),r[3]) for r in rs if isinstance(r[3],float)]
snow = [(date(r[0],r[1],r[2]),r[4]) for r in rs if isinstance(r[4],float)]
if rain: rain_dts, rain_vals = zip(*rain)
if snow: snow_dts, snow_vals = zip(*snow)
if snow: snow_vals = [v/self.snow_fall_factor for v in snow_vals]
ttl = 'Rain & Snow %d-%d'
ylab = 'Precip (inches)'
fig, ax = self.__chart_decade('precip', year0, title=ttl, ylab=ylab)
if rain: ax.fill_between(rain_dts,rain_vals,0, color='#007fff', alpha=0.95, zorder=9)
if snow: ax.fill_between(snow_dts,snow_vals,0, color='#3f3f3f', alpha=0.75, zorder=8)
self.__chart_decade_save(fig, fn)
return rs
def __chart_month (self, ctype, month,year0,year1, title=None,axtitle=None, xlab=None,ylab=None, ytop=+3.0):
fig, ax = plt.subplots()
if title:
dt = date(year0,month,1)
ttl = (title % (dt.strftime('%B %Y'),year1)) if year1 else (title % dt.strftime('%B %Y'))
fig.suptitle(ttl, fontsize=13)
if axtitle:
ax.set_title(axtitle, fontsize=12)
ax.minorticks_on()
ax.tick_params(which='major', axis='both', direction='out')
ax.tick_params(which='minor', axis='both', direction='out')
self.__chart_month_x_axis(ax, month,year0, xlab=xlab)
if ctype == 'temps': self.__chart_y_axis_temperature(ax, ylab=ylab)
if ctype == 'precip': self.__chart_y_axis_precip(ax, ylab=ylab, ytop=ytop)
return (fig, ax)
def __chart_year (self, ctype, year, title=None, axtitle=None, xlab=None, ylab=None, ytop=+5.0):
fig, ax = plt.subplots()
if title:
ttl = title % year
fig.suptitle(ttl, fontsize=13)
if axtitle:
ax.set_title(axtitle, fontsize=12)
ax.minorticks_on()
ax.tick_params(which='major', axis='both', direction='out')
ax.tick_params(which='minor', axis='both', direction='out')
self.__chart_year_x_axis(ax, year, xlab=xlab)
if ctype == 'temps': self.__chart_y_axis_temperature(ax, ylab=ylab)
if ctype == 'precip': self.__chart_y_axis_precip(ax, ylab=ylab, ytop=ytop)
return (fig, ax)
def __chart_decade (self, ctype, year0, title=None, axtitle=None, xlab=None, ylab=None, ytop=+3.0):
fig, ax = plt.subplots()
if title:
ttl = title % (year0, year0+9)
fig.suptitle(ttl, fontsize=13)
if axtitle:
ax.set_title(axtitle, fontsize=12)
ax.minorticks_on()
ax.tick_params(which='major', axis='both', direction='out')
ax.tick_params(which='minor', axis='both', direction='out')
self.__chart_decade_x_axis(ax, year0, xlab=xlab)
if ctype == 'temps': self.__chart_y_axis_temperature(ax, ylab=ylab)
if ctype == 'precip': self.__chart_y_axis_precip(ax, ylab=ylab, ytop=ytop)
return (fig, ax)
def __chart_month_x_axis (self, ax, month,year, xlab=None):
xmin = date(year,month,1)
xmax = (date(year,month+1,1) if month < 12 else date(year+1,1,1)) - timedelta(1)
if xlab: ax.set_xlabel(xlab, fontsize=11)
ax.xaxis_date()
ax.set_xlim(xmin, xmax)
ax.xaxis.set_major_locator(DayLocator(bymonthday=range(1,32,7) ))
ax.xaxis.set_minor_locator(DayLocator(interval=1))
ax.xaxis.set_major_formatter(DateFormatter('%b %d'))
for tick in ax.xaxis.get_major_ticks():
tick.label1.set_rotation('vertical')
tick.label1.set_size(11)
ax.grid(which='major', axis='x', color='#7f7fbf', lw=1.0,ls='-', alpha=1.0, zorder=4)
ax.grid(which='minor', axis='x', color='#e7e7ff', lw=0.8,ls='-', alpha=1.0, zorder=2)
def __chart_year_x_axis (self, ax, year, xlab=None):
xmin = date(year,1,1)
xmax = (date(year+1,1,1) - timedelta(1))
xt0 = [date(year, m+1, 1) for m in range(12)]+[date(year,12,31)]
xt1 = [date(year, (n//4)+1, 7*((n%4)+1)) for n in range(0, 12*4) ]
if xlab: ax.set_xlabel(xlab, fontsize=11)
ax.xaxis_date()
ax.set_xlim(xmin, xmax)
ax.set_xticks(xt0)
ax.set_xticks(xt1, minor=True)
ax.xaxis.set_major_formatter(DateFormatter('%b %d'))
for tick in ax.xaxis.get_major_ticks():
tick.label1.set_rotation('vertical')
tick.label1.set_size(11)
ax.grid(which='major', axis='x', color='#3f3f3f', lw=1.3,ls='-', alpha=1.0, zorder=4)
ax.grid(which='minor', axis='x', color='#afafaf', lw=0.8,ls='-', alpha=1.0, zorder=2)
def __chart_decade_x_axis(self, ax, year, xlab=None):
xmin = date(year,1,1)
xmax = (date(year+10,1,1) - timedelta(1))
xt0 = [date(year+y, 1, 1) for y in range(10)]
xt1 = [date(year+(n//12), (n%12)+1, 1) for n in range(0, 10*12)]
if xlab: ax.set_xlabel(xlab, fontsize=11)
ax.xaxis_date()
ax.set_xlim(xmin, xmax)
ax.set_xticks(xt0)
ax.set_xticks(xt1, minor=True)
ax.xaxis.set_major_formatter(DateFormatter('%Y'))
for tick in ax.xaxis.get_major_ticks():
tick.label1.set_rotation('vertical')
tick.label1.set_size(11)
ax.grid(which='major', axis='x', color='#3f3f3f', lw=1.3,ls='-', alpha=1.0, zorder=4)
ax.grid(which='minor', axis='x', color='#afafaf', lw=0.8,ls='-', alpha=1.0, zorder=2)
def __chart_y_axis_temperature (self, ax, ylab=None, ybot=-40.0, ytop=+120.0):
if ylab: ax.set_ylabel(ylab, fontsize=11)
ax.set_ylim(ybot, ytop)
ax.yaxis.set_major_locator(MultipleLocator(10.0))
ax.yaxis.set_minor_locator(MultipleLocator( 5.0))
ax.grid(which='major', axis='y', color='#7f7f7f', lw=0.9,ls='-', alpha=1.0, zorder=3)
ax.grid(which='minor', axis='y', color='#cfcfcf', lw=0.7,ls='-', alpha=1.0, zorder=1)
ax.axhline(0.0, color='black', lw=2.0, alpha=1.00, zorder=6)
def __chart_y_axis_precip (self, ax, ylab=None, ytop=+5.0):
if ylab: ax.set_ylabel(ylab, fontsize=11)
ax.set_ylim(0.0, ytop)
ax.yaxis.set_major_locator(MultipleLocator(1.0))
ax.yaxis.set_minor_locator(MultipleLocator(0.25))
ax.grid(which='major', axis='y', color='#7f7f7f', lw=0.9,ls='-', alpha=1.0, zorder=3)
ax.grid(which='minor', axis='y', color='#cfcfcf', lw=0.7,ls='-', alpha=1.0, zorder=1)
def __chart_month_save (self, fig, filename):
fig.set_figwidth(6.4)
fig.set_figheight(4.8)
fig.subplots_adjust(left=0.12, bottom=0.16, right=0.97, top=0.92)
fig.savefig(filename)
def __chart_year_save (self, fig, filename):
fig.set_figwidth(9.8)
fig.set_figheight(4.8)
fig.subplots_adjust(left=0.10, bottom=0.18, right=0.97, top=0.92)
fig.savefig(filename)
def __chart_decade_save (self, fig, filename):
fig.set_figwidth(10.8)
fig.set_figheight(4.8)
fig.subplots_adjust(left=0.10, bottom=0.15, right=0.97, top=0.92)
fig.savefig(filename)
def chart_temp_min_max (self, fname='temp_min_max.png'):
self.Log.debug('db[%s] Chart(temp.min/max)' % self.name)
fn = path.join(self.imgsPath,fname)
sql = Queries['temp.min-max.c']
rs = self.db_query(sql % self.name)
if not rs: self.Log.warn('No data!'); return []
dts,series1,series2 = zip(*rs)
props = {
'ttl':'Temperature (hi/lo) MSP 2010-2017',
'xlab':'Days', 'ylab':'Temperature',
'xlim':(0, max(len(series1),len(series2))),
'ylim':(min(series2), max(series1)),
'xLoc':120, 'xloc':30,
'yLoc':10.0, 'yloc':5.0, 'yFmt':'%+d',
'Grid':'both', 'grid':'both', 'minor':True
}
fig, ax = self.__chart_init(props)
ax.axhline(0.0, lw=2.0, color='k', zorder=5)
ax.plot(series1, label='Hi', color='#df3f00', alpha=0.75, zorder=9)
ax.plot(series2, label='Lo', color='#003fdf', alpha=0.75, zorder=8)
ax.legend(loc='lower left', shadow=True)
fig.set_figwidth(12.0)
fig.set_figheight(4.0)
fig.subplots_adjust(left=0.07, bottom=0.13, right=0.98, top=0.90)
fig.savefig(fn)
return rs
def chart_pressure (self, fname='pressure.png'):
self.Log.debug('db[%s] Chart(pressure)' % self.name)
fn = path.join(self.imgsPath,fname)
sql = Queries['pressure.c']
rs = self.db_query(sql % self.name)
if not rs: self.Log.warn('No data!'); return []
dts,series = zip(*rs)
series = [x for x in series if isinstance(x,float)]
props = {
'ttl':'Pressure (avg) MSP 2010-2017',
'xlab':'Days', 'ylab':'Air Pressure',
'xlim':(0, len(series)),
}
fig, ax = self.__chart_init(props)
ax.plot(series, label='Pressure', color='#003fdf', alpha=1.00, zorder=9)
fig.set_figwidth(12.0)
fig.set_figheight(4.0)
fig.subplots_adjust(left=0.05, bottom=0.13, right=0.98, top=0.90)
fig.savefig(fn)
return rs
def chart_rel_hum (self, fname='rel_hum.png'):
self.Log.debug('db[%s] Chart(rel_humid)' % self.name)
fn = path.join(self.imgsPath,fname)
sql = Queries['rel_hum.c']
rs = self.db_query(sql % self.name)
if not rs: self.Log.warn('No data!'); return []
dts,series = zip(*rs)
props = {
'ttl':'Relative Humidity (avg) MSP 2010-2017',
'xlab':'Days', 'ylab':'Relative Humidity',
'xlim':(0, len(series)),
'ylim':(0.0, 100.0),
'xLoc':360, 'xloc':120,
'yLoc':10.0, 'yloc':2.0,
'Grid':'both', 'grid':'both', 'minor':True
}
fig, ax = self.__chart_init(props)
ax.plot(series, label='Rel.Humidity', color='#003fdf', alpha=1.00, zorder=9)
fig.set_figwidth(12.0)
fig.set_figheight(4.0)
fig.subplots_adjust(left=0.05, bottom=0.13, right=0.98, top=0.90)
fig.savefig(fn)
return rs
def __chart_init (self, props):
fig, ax = plt.subplots()
if 'ttl' in props: fig.suptitle(props['ttl'])
if 'xlab' in props: ax.set_xlabel(props['xlab'])
if 'ylab' in props: ax.set_ylabel(props['ylab'])
if 'xlim' in props: ax.set_xlim(props['xlim'][0],props['xlim'][1])
if 'ylim' in props: ax.set_ylim(props['ylim'][0],props['ylim'][1])
if 'xLoc' in props: ax.xaxis.set_major_locator(MultipleLocator(props['xLoc']))
if 'yLoc' in props: ax.yaxis.set_major_locator(MultipleLocator(props['yLoc']))
if 'xFmt' in props: ax.xaxis.set_major_formatter(FormatStrFormatter(props['xFmt']))
if 'yFmt' in props: ax.yaxis.set_major_formatter(FormatStrFormatter(props['yFmt']))
if 'Grid' in props: ax.grid(which='major', axis=props['Grid'], color='#9f9f9f', lw=1.2,ls='-', alpha=1.0,zorder=1)
ax.tick_params(which='major', axis='both', direction='out')
if 'minor' in props:
ax.minorticks_on()
if 'xloc' in props: ax.xaxis.set_minor_locator(MultipleLocator(props['xloc']))
if 'yloc' in props: ax.yaxis.set_minor_locator(MultipleLocator(props['yloc']))
if 'grid' in props: ax.grid(which='minor', axis=props['grid'], color='#e7e7e7', lw=0.6,ls='-', alpha=1.0,zorder=0)
ax.tick_params(which='minor', axis='both', direction='out')
return fig, ax
Queries = {
'list':'''
SELECT date, COUNT(station), MIN(hr_drybulb_tempf), MAX(hr_drybulb_tempf),
AVG(hr_rel_hum), AVG(hr_wind_spd), AVG(hr_station_press)
FROM "%s"
WHERE (substr(report_type,1,2)='FM')
GROUP BY date
ORDER BY date,time DESC
'''
,
'report-types':'''
SELECT report_type, COUNT(station)
FROM "%s"
GROUP BY report_type
ORDER BY report_type
'''
,
'month.temps.c':'''
SELECT
cast(substr(date,1,4) as INTEGER) AS "dtY",
cast(substr(date,6,2) as INTEGER) AS "dtM",
cast(substr(date,9,2) as INTEGER) AS "dtD",
day_min_drybulb_temp AS "Lo",
day_max_drybulb_temp AS "Hi"
FROM "%s"
WHERE (report_type=?) AND (substr(date,1,4)=?) AND (substr(date,6,2)=?)
ORDER BY date
'''
,
'month.precip.c':'''
SELECT
cast(substr(date,1,4) as INTEGER) AS "dtY",
cast(substr(date,6,2) as INTEGER) AS "dtM",
cast(substr(date,9,2) as INTEGER) AS "dtD",
day_precip, day_snowfall
FROM "%s"
WHERE (report_type=?) AND (substr(date,1,4)=?) AND (substr(date,6,2)=?)
ORDER BY date
'''
,
'year.temps.c':'''
SELECT
cast(substr(date,1,4) as INTEGER) AS "dtY",
cast(substr(date,6,2) as INTEGER) AS "dtM",
cast(substr(date,9,2) as INTEGER) AS "dtD",
day_min_drybulb_temp AS "Lo",
day_max_drybulb_temp AS "Hi"
FROM "%s"
WHERE (report_type=?) AND (substr(date,1,4)=?)
ORDER BY date
'''
,
'year.precip.c':'''
SELECT
cast(substr(date,1,4) as INTEGER) AS "dtY",
cast(substr(date,6,2) as INTEGER) AS "dtM",
cast(substr(date,9,2) as INTEGER) AS "dtD",
day_precip, day_snowfall
FROM "%s"
WHERE (report_type=?) AND (substr(date,1,4)=?)
ORDER BY date
'''
,
'decade.temps.c':'''
SELECT
cast(substr(date,1,4) as INTEGER) AS "dtY",
cast(substr(date,6,2) as INTEGER) AS "dtM",
cast(substr(date,9,2) as INTEGER) AS "dtD",
day_min_drybulb_temp AS "Lo",
day_max_drybulb_temp AS "Hi"
FROM "%s"
WHERE (report_type=?) AND (substr(date,1,4) BETWEEN ? AND ?)
ORDER BY date
'''
,
'decade.precip.c':'''
SELECT
cast(substr(date,1,4) as INTEGER) AS "dtY",
cast(substr(date,6,2) as INTEGER) AS "dtM",
cast(substr(date,9,2) as INTEGER) AS "dtD",
day_precip, day_snowfall
FROM "%s"
WHERE (report_type=?) AND (substr(date,1,4) BETWEEN ? AND ?)
ORDER BY date
'''
,
'months.temps.c':'''
SELECT
cast(substr(date,1,4) as INTEGER) AS "dtY",
cast(substr(date,6,2) as INTEGER) AS "dtM",
cast(substr(date,9,2) as INTEGER) AS "dtD",
day_max_drybulb_temp AS "Hi",
day_min_drybulb_temp AS "Lo"
FROM "%s"
WHERE (report_type=?)
AND (substr(date,6,2)=?)
AND (substr(date,1,4) BETWEEN ? AND ?)
ORDER BY date
'''
,
'months.temps.hi.c':'''
SELECT
cast(substr(date,1,4) as INTEGER) AS "dtY",
cast(substr(date,6,2) as INTEGER) AS "dtM",
cast(substr(date,9,2) as INTEGER) AS "dtD",
day_max_drybulb_temp AS "Hi"
FROM "%s"
WHERE (report_type=?)
AND (substr(date,6,2)=?)
AND (substr(date,1,4) BETWEEN ? AND ?)
ORDER BY date
'''
,
'months.temps.lo.c':'''
SELECT
cast(substr(date,1,4) as INTEGER) AS "dtY",
cast(substr(date,6,2) as INTEGER) AS "dtM",
cast(substr(date,9,2) as INTEGER) AS "dtD",
day_min_drybulb_temp AS "Lo"
FROM "%s"
WHERE (report_type=?)
AND (substr(date,6,2)=?)
AND (substr(date,1,4) BETWEEN ? AND ?)
ORDER BY date
'''
,
'temp.min-max.c':'''
SELECT date, day_max_drybulb_temp, day_min_drybulb_temp
FROM "%s"
WHERE (report_type='SOD') AND ('2010-01-01' <= date)
ORDER BY date
'''
,
'pressure.c':'''
SELECT date, day_avg_station_press
FROM "%s"
WHERE (report_type='SOD')
ORDER BY date
'''
,
'rel_hum.c':'''
SELECT date, AVG(hr_rel_hum)
FROM "%s"
WHERE (report_type='FM-12')
GROUP BY date
ORDER BY date
'''
,
'_sql':'''
SELECT * from %s order by date,time
'''
}
'''eof'''