def loadqc(): """ See which sites have flags against them """ qdict = {} portfolio = iemdb.connect('portfolio', dbhost='meteor.geol.iastate.edu', bypass=True) pcursor = portfolio.cursor() pcursor.execute(""" select s_mid, sensor, status from tt_base WHERE sensor is not null and status != 'CLOSED' and portfolio in ('kccisnet','kelosnet','kimtsnet') """) for row in pcursor: if not qdict.has_key(row[0]): qdict[row[0]] = {} if row[1].find("precip") > -1: qdict[row[0]]['precip'] = True if row[1].find("tmpf") > -1: qdict[row[0]]['tmpf'] = True if row[1].find("drct") > -1 or row[1].find("sknt") > -1 or row[1].find("wind") > -1: qdict[row[0]]['wind'] = True pcursor.close() portfolio.close() return qdict
def get_warnings(sts, ets, wfo, wtypes): """ Retreive an array of warnings for this time period and WFO """ tokens = wtypes.split(",") tokens.append("ZZZ") phenomenas = str(tuple(tokens)) POSTGIS = iemdb.connect('postgis', bypass=True) pcursor = POSTGIS.cursor(cursor_factory=psycopg2.extras.DictCursor) sql = """ SELECT phenomena, eventid, min(issue) at time zone 'UTC' as issue, max(expire) at time zone 'UTC' as expire, sum(case when gtype = 'P' then ST_Area(ST_Transform(geom,2163))/1000000.0 else 0 end) as polyarea, sum(case when gtype = 'C' then ST_Area(ST_Transform(geom,2163))/1000000.0 else 0 end) as countyarea from warnings_%s WHERE wfo = '%s' and phenomena in %s and significance = 'W' and issue BETWEEN '%s+00' and '%s+00' GROUP by phenomena, eventid ORDER by issue ASC """ % (sts.year, wfo, phenomenas, sts.strftime("%Y-%m-%d %H:%M"), ets.strftime("%Y-%m-%d %H:%M")) pcursor.execute(sql) res = [] for row in pcursor: res.append( row ) POSTGIS.close() return res
def process(tv): IEM = iemdb.connect('iem', bypass=True) icursor = IEM.cursor() icursor2 = IEM.cursor() icursor.execute("""SELECT pday, t.id, t.iemid from current c, stations t WHERE t.network = '%s' and c.iemid = t.iemid""" % (tv,)) for row in icursor: sid = row[1] pday = row[0] db[sid]["pday"] = [pday] + db[sid]["pday"][0:-1] oldTMPF = db[sid]["pday"][14] if ( oldTMPF < pday ): accum = pday - float(db[sid]["pday"][14]) #fileRef = "%s.%s.%s" % (sid, "P+", "1min") #o = open("/mesonet/share/"+tv+"_events/"+ fileRef, "w") #o.write( str(accum) ) #o.close() sql = "DELETE from events WHERE station = '%s' and network = '%s'\ and event = 'P+'" % (sid, tv) icursor2.execute(sql) sql = "INSERT into events (station, network, valid, event, magnitude) VALUES ('%s','%s',now(), 'P+', %s)" % (sid, tv, accum) icursor2.execute(sql) d = pday - float(db[sid]["pday"][59]) if (d < 0): d = pday sql = "UPDATE current SET phour = %s WHERE iemid = %s " % (d, row[2]) #print sql icursor2.execute(sql) icursor2.close() IEM.commit() IEM.close()
def preload(): IEM = iemdb.connect('iem', bypass=True) icursor = IEM.cursor() icursor.execute("""SELECT pday, t.id from current c, stations t WHERE t.network in ('KCCI','KELO','KIMT') and c.iemid = t.iemid""") for row in icursor: sid = row[1] db[sid]["pday"] = [row[0]]*60 IEM.close()
def sync(dbname): """ Actually do the syncing, please """ # connect to synced database dbconn = iemdb.connect( dbname ) dbcursor = dbconn.cursor() # Figure out our latest revision dbcursor.execute(""" SELECT max(modified), max(iemid) from stations """) row = dbcursor.fetchone() maxTS = (row[0] or datetime.datetime(1980,1,1)) maxID = (row[1] or -1) # figure out what has changed! cur = MESOSITE.cursor(cursor_factory=psycopg2.extras.DictCursor) cur.execute("""SELECT * from stations WHERE modified > %s or iemid > %s""", (maxTS, maxID) ) for row in cur: if row['iemid'] > maxID: dbcursor.execute("""INSERT into stations(iemid, network, id) VALUES(%s,%s,%s) """, (row['iemid'], row['network'], row['id'])) # insert queried stations dbcursor.execute("""UPDATE stations SET name = %(name)s, state = %(state)s, elevation = %(elevation)s, online = %(online)s, geom = %(geom)s, params = %(params)s, county = %(county)s, plot_name = %(plot_name)s, climate_site = %(climate_site)s, wfo = %(wfo)s, archive_begin = %(archive_begin)s, archive_end = %(archive_end)s, remote_id = %(remote_id)s, tzname = %(tzname)s, country = %(country)s, modified = %(modified)s, network = %(network)s, metasite = %(metasite)s, sigstage_low = %(sigstage_low)s, sigstage_action = %(sigstage_action)s, sigstage_bankfull = %(sigstage_bankfull)s, sigstage_flood = %(sigstage_flood)s, sigstage_moderate = %(sigstage_moderate)s, sigstage_major = %(sigstage_major)s, sigstage_record = %(sigstage_record)s, ugc_county = %(ugc_county)s, ugc_zone = %(ugc_zone)s WHERE iemid = %(iemid)s""", row) print 'Database: %s Modified %s rows TS: %s IEMID: %s' % (dbname, cur.rowcount, maxTS, maxID) # close connection dbcursor.close() dbconn.commit() dbconn.close()
def news(): """ Print the news that is fit to print """ mesosite = iemdb.connect('mesosite', bypass=True) mcursor = mesosite.cursor(cursor_factory=psycopg2.extras.DictCursor) # Last dailyb delivery lastts = mx.DateTime.now() + mx.DateTime.RelativeDateTime(hour=11, days=-1) mcursor.execute(""" SELECT *, to_char(entered, 'DD Mon HH:MI AM') as nicedate from news WHERE entered > '%s' ORDER by entered DESC""" % ( lastts.strftime("%Y-%m-%d %H:%M"),) ) textfmt = """ +---------------------------------------------- | Title : %(title)s | Date : %(nicedate)s | Author: %(author)s | URL : %(url)s +---------------------------------------------- %(body)s """ htmlfmt = """ <hr /> <br /><strong>Title:</strong> <a href="http://mesonet.agron.iastate.edu/onsite/news.phtml?id=%(id)s">%(title)s</a> <br /><strong>Date:</strong> %(nicedate)s <br /><strong>Author:</strong> %(author)s <br /><a href="%(url)s">link</a> <p>%(body)s """ txt = "> News\n" html = "<h3>News</h3>" for row in mcursor: txt += textfmt % row html += htmlfmt % row if mcursor.rowcount == 0: txt += "\n No news is good news\n\n" html += "<strong>No news is good news</strong>" return txt, html
def __init__(self, network): """ Construct with either a single network, or list of networks """ self.sts = {} dbconn = iemdb.connect('mesosite', bypass=True) cursor = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor) if type(network) == type("A"): network = [network,] for n in network: cursor.execute("""SELECT *, x(geom) as lon, y(geom) as lat from stations WHERE network = %s ORDER by name ASC""", (n,)) for row in cursor: self.sts[ row['id'] ] = {} for key in row.keys(): self.sts[ row['id'] ][key] = row[key] cursor.close() dbconn.close()
def check_for_work(): """ See if we have any requests to process! """ MESOSITE = iemdb.connect('mesosite', bypass=True) mcursor = MESOSITE.cursor(cursor_factory=psycopg2.extras.DictCursor) mcursor2 = MESOSITE.cursor() mcursor.execute("""SELECT jobid, wfo, radar, sts at time zone 'UTC' as sts, ets at time zone 'UTC' as ets, nexrad_product, wtype from racoon_jobs WHERE processed = False""") jobs = [] for row in mcursor: jobs.append(row) mcursor2.execute("""UPDATE racoon_jobs SET processed = True WHERE jobid = %s""", (row[0],)) MESOSITE.commit() MESOSITE.close() return jobs
def feature(): """ Print the feature for yesterday """ mesosite = iemdb.connect('mesosite', bypass=True) mcursor = mesosite.cursor(cursor_factory=psycopg2.extras.DictCursor) lastts = mx.DateTime.now() + mx.DateTime.RelativeDateTime(days=-1) # Query mcursor.execute(""" SELECT *, to_char(valid, 'DD Mon HH:MI AM') as nicedate from feature WHERE date(valid) = 'YESTERDAY'""") textfmt = """ +---------------------------------------------- %(link)s | Title : %(title)s | Date : %(nicedate)s | Votes : Good: %(good)s Bad: %(bad)s +---------------------------------------------- %(story)s """ htmlfmt = """ <p><a href="%(link)s">%(title)s</a> <br /><strong>Date:</strong> %(nicedate)s <br /><strong>Votes:</strong> Good: %(good)s Bad: %(bad)s <p>%(story)s """ txt = "> Daily Feature\n" html = "<h3>Daily Feature</h3>" for row in mcursor: row2 = row.copy() row2['link'] = "http://mesonet.agron.iastate.edu/onsite/features/cat.php?day=%s" % (lastts.strftime("%Y-%m-%d"),) txt += textfmt % row2 html += htmlfmt % row2 if mcursor.rowcount == 0: txt += "\n No feature posted\n\n" html += "<strong>No feature posted</strong>" return txt, html
def dailyc_plot(ax, cfg): """ Plot daily climate """ COOP = iemdb.connect('coop', bypass=True) ccursor = COOP.cursor() chighs = [] clows = [] ccursor.execute(""" SELECT high, low from climate51 WHERE station = 'IA0200' ORDER by valid ASC """) for row in ccursor: chighs.append( float(row[0]) ) clows.append( float(row[1]) ) chighs = numpy.array( chighs ) clows = numpy.array( clows ) ax.bar( numpy.arange(len(chighs)), chighs-clows, bottom=clows, width=1.0, ec='#ff0000', fc='#ff0000') COOP.close()
def doit(hr): ASOS = iemdb.connect('asos', bypass=True) acursor = ASOS.cursor() acursor.execute(""" SELECT extract(doy from d) as doy, count(*) from (SELECT date(valid) as d, sum( case when skyc1 in ('BKN','OVC') or skyc2 in ('BKN','OVC') or skyc3 in ('BKN','OVC') then 1 else 0 end ) as clouds, max(tmpf) as high, max(dwpf) as maxd, max(sknt) as wind from alldata where station = 'DSM' and tmpf > -50 and sknt >= 0 and valid > '1973-01-01' and valid < '2010-01-01' and extract(hour from valid) = %s GROUP by d ) as foo WHERE clouds > 0 GROUP by doy ORDER by doy ASC """, (hr,)) doy = [] for row in acursor: doy.append( row[1] ) return numpy.array( doy )
def available_radars(form): """ Return available RADAR sites for the given location and date! """ lat = form.getvalue('lat', None) lon = form.getvalue('lon', None) start_gts = parse_time( form.getvalue('start', '2012-01-27T00:00Z') ) MESOSITE = iemdb.connect('mesosite', bypass=True) mcursor = MESOSITE.cursor() root = {'radars': []} if lat is None or lon is None: sql = """ select id, name, x(geom) as lon, y(geom) as lat, network from stations where network in ('NEXRAD','ASR4','ASR11','TWDR') ORDER by id asc""" else: sql = """ select id, name, x(geom) as lon, y(geom) as lat, network, ST_Distance(geom, GeomFromEWKT('SRID=4326;POINT(%s %s)')) as dist from stations where network in ('NEXRAD','ASR4','ASR11','TWDR') and ST_Distance(geom, GeomFromEWKT('SRID=4326;POINT(%s %s)')) < 3 ORDER by dist asc """ % (lon, lat, lon, lat) mcursor.execute(sql) root['radars'].append({'id': 'USCOMP', 'name': 'National Composite', 'lat': 42.5, 'lon': -95, 'type': 'COMPOSITE'}) for row in mcursor: radar = row[0] if not os.path.isdir(start_gts.strftime("/mesonet/ARCHIVE/data/%Y/%m/%d/GIS/ridge/"+ radar)): continue root['radars'].append({'id': radar, 'name': row[1], 'lat': row[3], 'lon': row[2], 'type': row[4]}) mcursor.close() MESOSITE.close() return root
import iemdb import math import Ngl import numpy import random ASOS = iemdb.connect('asos', bypass=True) acursor = ASOS.cursor() POSTGIS = iemdb.connect('postgis', bypass=True) pcursor = POSTGIS.cursor() pcursor2 = POSTGIS.cursor() MESOSITE = iemdb.connect('mesosite', bypass=True) mcursor = MESOSITE.cursor() stations = {} def dir22(u,v): if (v == 0): v = 0.000000001 dd = math.atan(u / v) ddir = (dd * 180.00) / math.pi if (u > 0 and v > 0 ): # First Quad ddir = 180 + ddir elif (u > 0 and v < 0 ): # Second Quad ddir = 360 + ddir elif (u < 0 and v < 0 ): # Third Quad ddir = ddir elif (u < 0 and v > 0 ): # Fourth Quad ddir = 180 + ddir return math.fabs(ddir)
""" Accumulate PET for this year and previous ones """ import iemdb ISUAG = iemdb.connect('isuag', bypass=True) icursor = ISUAG.cursor() import matplotlib.pyplot as plt (fig, ax) = plt.subplots(1, 1) for yr in range(1987, 2013): icursor.execute("""SELECT extract(doy from valid), c70 from daily WHERE station = 'A130209' and extract(year from valid) = %s and extract(month from valid) > 4 ORDER by valid ASC""" % (yr, )) doy = [0] c70 = [0] for row in icursor: doy.append(row[0]) c70.append(c70[-1] + row[1]) if yr in [1988, 2012, 2009, 1993]: ax.plot(doy, c70, linewidth=2, label=yr, zorder=2) else: ax.plot(doy, c70, color='tan', zorder=1) ax.legend(loc='best') ax.set_title("Ames Accumulated Potential Evapo-Transpiration (alfalfa)") ax.set_ylabel("Potential Evapo-transpiration [inch]") ax.set_xticks((1, 32, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335, 365)) ax.set_xticklabels(('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')) ax.set_xlim(120, 306)
# 28 Aug 2004 port to iem40 import shapelib import dbflib import mx.DateTime import zipfile import os import sys import shutil import cgi import sys sys.path.insert(0, '/mesonet/www/apps/iemwebsite/scripts/lib') import wellknowntext import iemdb import psycopg2.extras POSTGIS = iemdb.connect('postgis', bypass=True) pcursor = POSTGIS.cursor(cursor_factory=psycopg2.extras.DictCursor) pcursor.execute("SET TIME ZONE 'GMT'") # Get CGI vars form = cgi.FormContent() year = int(form["year"][0]) etn = int(form["etn"][0]) fp = "watch_%s_%s" % (year, etn) os.chdir("/tmp/") shp = shapelib.create(fp, shapelib.SHPT_POLYGON) dbf = dbflib.create(fp)
import psycopg2.extras import sys import iemre import netCDF4 import mx.DateTime import numpy ts = mx.DateTime.now() + mx.DateTime.RelativeDateTime(days=-1,hour=0,minute=0,second=0) # Load up netcdf file nc = netCDF4.Dataset("/mnt/mesonet/data/iemre/%s_mw_daily.nc" % (ts.year,),'r') p01d = nc.variables['p01d'] offset = int((ts - (ts + mx.DateTime.RelativeDateTime(month=1,day=1))).days) iem = iemdb.connect("iem", bypass=True) icursor = iem.cursor(cursor_factory=psycopg2.extras.DictCursor) icursor.execute("""SELECT x(geom) as lon, y(geom) as lat, id, pday FROM summary_%s s JOIN stations t ON (t.iemid = s.iemid) where t.network in ('KCCI','KIMT','AWOS') and day = 'YESTERDAY'::date ORDER by pday DESC""" % (ts.year,)) obs = [] estimates = [] fmt = "%7s %5s OB: %.2f EST: %4.2f" for row in icursor: station = row['id'] lat = row['lat'] lon = row['lon'] # Lookup IEMRE data ix,jy = iemre.find_ij(lon, lat) estimate = p01d[offset,jy,ix] / 25.4 # If site is offline, we could care less
import iemdb import math import iemtz import numpy HADS = iemdb.connect('hads', bypass=True) hcursor = HADS.cursor() def uv(sped, drct2): dirr = drct2 * math.pi / 180.00 s = numpy.sin(dirr) c = numpy.cos(dirr) u = -sped * s v = -sped * c return u, v hcursor.execute(""" select valid, key, value from raw2012_12 where station = 'SAYI4' and key in ('HPIRGZ', 'UDIRGZ', 'USIRGZ') and valid > '2012-12-07 23:30' ORDER by valid ASC """) valid = {'HPIRGZ': [], 'UDIRGZ': [], 'USIRGZ': []} values = {'HPIRGZ': [], 'UDIRGZ': [], 'USIRGZ': []} for row in hcursor: valid[row[1]].append(row[0]) values[row[1]].append(row[2]) u, v = uv(numpy.array(values['USIRGZ']), numpy.array(values['UDIRGZ'])) hcursor.execute("""
import iemdb import numpy import datetime import numpy.ma OTHER = iemdb.connect('other', bypass=True) ocursor = OTHER.cursor() def getsite(station): ocursor.execute(""" SELECT valid, hs, le_wpl from flux2013 where station = %s and le_wpl BETWEEN -100 and 1000 and valid > '2013-08-01' ORDER by valid ASC """, (station,)) valid = [] soil = [] val2 = [] for row in ocursor: valid.append( row[0] ) soil.append( row[1] ) val2.append( row[2] ) return valid, soil, val2 v1, s1, s2 = getsite('nstl30ft') #v2, s2 = getsite('nstlnsp') s1 = numpy.array( s1 ) s2 = numpy.array( s2 ) bowen = s1 / s2 import matplotlib.pyplot as plt import matplotlib.dates as mdates (fig, ax) = plt.subplots(1,1)
import ephem import datetime import pytz import iemdb import numpy import matplotlib.patheffects as PathEffects COOP = iemdb.connect('coop', bypass=True) ccursor = COOP.cursor() ccursor.execute("""SELECT valid, high from climate71 where station = 'IA8706' """) climate51 = {} for row in ccursor: lts = row[0] climate51[ "%02i%02i" % ( lts.month, lts.day)] = row[1] ccursor.execute("""SELECT day, high from alldata_ia where station = 'IA8706' and month in (2,3,4)""") data = {} for row in ccursor: lts = row[0] data[ "%s%02i%02i" % ( lts.year, lts.month, lts.day)] = row[1] data['20130328'] = 48 data['20130329'] = 100 years = [] start = [] end = [] for yr in range(1900,2014): d1 = ephem.next_equinox(str(yr))
""" Copy RWIS data from iem database to its final resting home in 'rwis' """ import iemdb import psycopg2.extras import mx.DateTime, sys, traceback IEMDB = iemdb.connect("iem") RWISDB = iemdb.connect("rwis") # Figure out what date of data we are interested in if (len(sys.argv) > 1): ts = mx.DateTime.now() else: ts = mx.DateTime.now() - mx.DateTime.RelativeDateTime(days=1) # Delete any obs from yesterday rcursor = RWISDB.cursor() rcursor.execute("""DELETE from t%s WHERE date(valid) = '%s'""" % (ts.year, ts.strftime("%Y-%m-%d") )) rcursor.execute("""DELETE from t%s_soil WHERE date(valid) = '%s'""" % (ts.year, ts.strftime("%Y-%m-%d") )) rcursor.execute("""DELETE from t%s_traffic WHERE date(valid) = '%s'""" % ( ts.year, ts.strftime("%Y-%m-%d") )) rcursor.close() # Always delete stuff 3 or more days old from iemaccess icursor = IEMDB.cursor() icursor.execute("""DELETE from rwis_traffic_data_log WHERE valid < ('TODAY'::date - '3 days'::interval)""")
import matplotlib.pyplot as plt import iemdb import numpy dbconn = iemdb.connect("coop", bypass=True) a1993 = [] a2010 = [] climate = [] c = dbconn.cursor() c.execute("""SELECT valid, precip from climate51 where station = 'ia2203' and valid >= '2000-06-01' and valid < '2000-09-01' ORDER by valid ASC""") running = 0 for row in c: running += row[1] climate.append( running ) climate = numpy.array( climate ) c.execute("""SELECT day, precip from alldata where stationid = 'ia2203' and month in (6,7,8) and year = 2010 ORDER by day ASC""") running = 0 for row in c: running += row[1] a2010.append( running ) #a2010.append(running + 1.17) a2010 = numpy.array( a2010 ) c.execute("""SELECT day, precip from alldata where stationid = 'ia2203' and month in (6,7,8) and year = 1993 ORDER by day ASC""") running = 0 for row in c: running += row[1]
import iemdb POSTGIS = iemdb.connect('postgis', bypass=True) pcursor = POSTGIS.cursor() pcursor.execute("""SET TIME ZONE 'GMT'""") def getdata(year): pcursor.execute( """ select s.valid, extract(doy from s.valid), ST_Area( ST_Transform( ST_Intersection(s.geom, t.the_geom),26915) ) / 1000000. / 145693.8 from spc_outlooks s, states t WHERE ST_Intersects(s.geom, t.the_geom) and t.state_name = 'Iowa' and category = 'SLGT' and s.valid > %s and s.valid < %s and day = 1 and extract(hour from s.valid) = 13 ORDER by valid ASC """, ("%s-01-01" % (year, ), "%s-01-01" % (year + 1, ))) doy = [] ratio = [] for row in pcursor: doy.append(row[1]) ratio.append(row[2] * 100.0) return doy, ratio doy2009, ratio2009 = getdata(2009) doy2010, ratio2010 = getdata(2010)
""" one langley is 41840.00 J/m2(or joules per square metre). one Langley is 11.622 watt-hours per square metre 400 W/m2 1440 000 J/hr """ import iemdb ISUAG = iemdb.connect('isuag', bypass=True) icursor = ISUAG.cursor() icursor.execute(""" select valid, c80 from daily WHERE station = 'A130209' and valid > '2012-11-01' ORDER by valid ASC """) obs = [] days = [] for row in icursor: days.append( row[0] ) obs.append( row[1] ) import matplotlib.pyplot as plt import matplotlib.dates as mdates (fig, ax) = plt.subplots(1,1) ax.bar(days, obs, fc='pink', ec='pink') ax.grid(True)
import iemdb import numpy RWIS = iemdb.connect('rwis', bypass=True) rcursor = RWIS.cursor() rcursor.execute(""" select extract(doy from valid) as d, avg(tfs1), stddev(tfs1), sum(case when tfs1 < 32 then 1 else 0 end), count(*) from alldata where station = 'RMTI4' and extract(hour from valid) between 12 and 16 and tfs1 between -30 and 160 GROUP by d ORDER by d ASC """) doy = [] avg = [] stddev = [] freq = [] for row in rcursor: doy.append( row[0] ) avg.append( row[1] ) stddev.append( row[2] ) freq.append( row[3] / float(row[4]) * 100.0 ) def movingaverage(interval, window_size): window = numpy.ones(int(window_size))/float(window_size) return numpy.convolve(interval, window, 'same') import matplotlib.pyplot as plt (fig, ax) = plt.subplots(1,1)
import shutil import traceback import logging import smtplib, StringIO from email.MIMEText import MIMEText # 3rd Party import mx.DateTime # Local stuff import nwnOB import secret import iemdb import psycopg2.extras MESOSITE = iemdb.connect('mesosite', bypass=True) mcursor = MESOSITE.cursor(cursor_factory=psycopg2.extras.DictCursor) logging.basicConfig(filename='/mesonet/data/logs/nwn.log',filemode='a') logger=logging.getLogger() logger.addHandler(logging.StreamHandler()) logger.setLevel(logging.INFO) # Write a PID File o = open("ingest.pid",'w') o.write("%s" % ( os.getpid(),) ) o.close() # Load up locs locs = {} mcursor.execute("""SELECT nwn_id, id, x(geom) as lon, y(geom) as lat, name,
# Output the 12z morning low temperature import os, random import iemdb import iemplot import mx.DateTime now = mx.DateTime.now() IEM = iemdb.connect('iem', bypass=True) icursor = IEM.cursor() sql = """ select c.station, x(s.geom) as lon, y(s.geom) as lat, min_tmpf as low, s.network from summary_%s c, stations s WHERE day = 'TODAY' and min_tmpf < 90 and (c.network ~* 'ASOS' or c.network ~* 'AWOS' or c.network = 'IA_COOP') and s.country = 'US' and s.network = c.network and s.id = c.station """ % (now.year, ) lats = [] lons = [] vals = [] valmask = [] labels = [] icursor.execute(sql) for row in icursor: lats.append( row[2] ) lons.append( row[1] )
import iemdb, sys, mx.DateTime IEM = iemdb.connect('iem', bypass=True) icursor = IEM.cursor() MESOSITE = iemdb.connect('mesosite') mcursor = MESOSITE.cursor() year = mx.DateTime.now().year icursor.execute(""" DELETE from current where iemid = (select iemid from stations where id = '%s' and network = '%s') """ % (sys.argv[2],sys.argv[1])) icursor.execute(""" DELETE from summary_%s where iemid = (select iemid from stations where id = '%s' and network = '%s') and day in ('TODAY','TOMORROW') """ % (year, sys.argv[2],sys.argv[1])) icursor.close() IEM.commit() mcursor.execute(""" update stations SET online = false where id = '%s' and network = '%s' """ % (sys.argv[2],sys.argv[1])) mcursor.close() MESOSITE.commit()
import iemdb import numpy import numpy.ma OTHER = iemdb.connect('other', bypass=True) ocursor = OTHER.cursor() def getsite(station): ocursor.execute( """ SELECT valid, outgoing_sw, incoming_sw from flux2014 where station = %s and extract(hour from valid) = 12 and extract(minute from valid) = 0 and incoming_sw > 0 and outgoing_sw >= 0 ORDER by valid ASC """, (station, )) valid = [] soil = [] val2 = [] for row in ocursor: valid.append(row[0]) soil.append(row[1]) val2.append(row[2]) return valid, soil, val2 v1, s1, s2 = getsite('nstl11') #v2, s2 = getsite('nstlnsp') s1 = numpy.array(s1) s2 = numpy.array(s2) import matplotlib.pyplot as plt import matplotlib.dates as mdates
a = zip(x, y) p = Polygon(a, fc=fill_colors.get(lookup, 'None'), ec=edge_colors.get(lookup, 'k'), zorder=zorder.get(lookup, 2), lw=linewidth.get(lookup, .1)) if gtype == 'P': patches2.append(p) else: patches.append(p) ax.add_collection(PatchCollection(patches, match_original=True)) ax.add_collection(PatchCollection(patches2, match_original=True)) import iemdb IEM = iemdb.connect('iem', bypass=True) icursor = IEM.cursor() icursor.execute(""" select id, avg(tmpf), x(geom), y(geom) from current_log c JOIn stations s on (s.iemid = c.iemid) WHERE s.network in ('AWOS','IA_ASOS') and valid between '2013-04-09 17:50' and '2013-04-09 18:00' and id not in ('MPZ') GROUP by id, x, y """) for row in icursor: x, y = m(row[2], row[3]) txt = ax.text(x, y, '%.0f' % (row[1], ), color='white', size=18) #txt.set_path_effects([PathEffects.withStroke(linewidth=2, # foreground="black")]) txt = ax.text(0.25, 0.05,
import matplotlib.pyplot as plt import numpy import mesonet import math from scipy import stats import matplotlib.font_manager prop = matplotlib.font_manager.FontProperties(size=12) import iemdb ASOS = iemdb.connect('asos', bypass=True) acursor = ASOS.cursor() SYEAR = 1970 def get_data(station): data = [] for yr in range(SYEAR, 2013): acursor.execute(""" SELECT valid + '10 minutes'::interval, tmpf, dwpf from t%s WHERE station = '%s' and extract(year from valid + '0 month'::interval) = %s and extract(month from valid) in (6,7,8) and dwpf > -30 and tmpf > -30 """ % (yr, station, yr)) tot = 0 for row in acursor: dwpc = mesonet.f2c(row[2]) e = 6.112 * math.exp((17.67 * dwpc) / (dwpc + 243.5)) mixr = 0.62197 * e / (1000.0 - e) if mixr > 0: tot += mixr
import iemdb import re import numpy import numpy.ma import scipy.stats import mx.DateTime postgis = iemdb.connect('postgis', bypass=True) pcursor = postgis.cursor() pcursor.execute(""" SELECT issue, report, x(ST_Centroid(geom)), y(ST_Centroid(geom)) from warnings where wfo in ('ARX','DVN','DMX','OAX','FSD') and significance = 'W' and gtype = 'P' and phenomena = 'SV' and issue > '2008-01-01' """) SVRs = [] for row in pcursor: tokens = re.findall( r'TIME...MOT...LOC [0-9]{4}Z ([0-9]{1,3})DEG ([0-9]{1,3})KT', row[1]) if len(tokens) == 0: print row[1] continue (dir, sknt) = tokens[0] if int(sknt) > 80: continue data = { 'sknt': int(sknt), 'drct': int(dir), 'ts':
import iemdb import numpy.ma MOS = iemdb.connect('mos', bypass=True) mcursor = MOS.cursor() COOP = iemdb.connect('coop', bypass=True) ccursor = COOP.cursor() # Get rain obs obs = {} ccursor.execute(""" SELECT day, precip from alldata_ia where year > 2003 and month = 5 and station = 'IA2203' and precip > 0.001 """) for row in ccursor: obs[ row[0].strftime("%Y%m%d") ] = row[1] # Get pwater mcursor.execute(""" SELECT runtime, pwater from model_gridpoint where station = 'KDSM' and model = 'NAM' and runtime = ftime and extract(month from runtime) = 5 and extract(hour from runtime) = 7 """) total = numpy.ma.zeros( (20,), 'f') # 4 mm to 100 hits = numpy.ma.zeros( (20,), 'f') pwater = [] precip = [] for row in mcursor: bin = int(row[1] / 4) print row[1], bin if obs.has_key( row[0].strftime("%Y%m%d")): hits[bin] += 1
Provide IDEP output on a per township basis """ import sys sys.path.insert(0, '/mesonet/www/apps/iemwebsite/scripts/lib') import iemdb import wellknowntext import shapelib import dbflib import os import cgi import mx.DateTime import sys import zipfile import shutil import psycopg2.extras WEPP = iemdb.connect('wepp', bypass=True) wcursor = WEPP.cursor(cursor_factory=psycopg2.extras.DictCursor) os.chdir('/tmp/') # Figure out what date we want form = cgi.FormContent() year = int(form["year"][0]) month = int(form["month"][0]) day = int(form["day"][0]) ts = mx.DateTime.DateTime(year, month, day) fp = "%s_idep" % (ts.strftime("%Y%m%d"), ) interval = None if form.has_key("interval"): interval = int(form["interval"][0]) fp = "%s_%s_idep" % (ts.strftime("%Y%m%d"), interval)
""" Accumulate PET for this year and previous ones """ import iemdb ISUAG = iemdb.connect('squaw', bypass=True) icursor = ISUAG.cursor() import numpy.ma import datetime import matplotlib.pyplot as plt import matplotlib.dates as mdates (fig, ax) = plt.subplots(1, 1) icursor.execute("""SELECT date(valid) as d, max(cfs) from real_flow WHERE valid > '2010-01-01' GROUP by d ORDER by d ASC""") vals = [] days = [] for row in icursor: days.append(row[0]) vals.append(row[1]) ax.plot(days, vals, lw=2) ax.set_title("Ames Squaw Creek at Lincoln Way") ax.set_xlabel("1 June 2010 thru 11 March 2013") ax.set_ylabel("Water Flow [cfs], log scale") #ax.set_xticks( (1,32,60,91,121,152,182,213,244,274,305,335,365) ) #ax.set_xticklabels( ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') ) ax.set_xlim(datetime.date(2010, 6, 1), datetime.date(2013, 4, 1)) ax.set_yscale('log') ax.xaxis.set_major_formatter(mdates.DateFormatter('%b\n%Y')) ax.grid(True)
""" Create a simple prinout of observation quanity in the database """ import datetime now = datetime.datetime.utcnow() import numpy counts = numpy.zeros((120,12)) mslp = numpy.zeros((120,12)) metar = numpy.zeros((120,12)) import iemdb ASOS = iemdb.connect('asos', bypass=True) acursor = ASOS.cursor() import sys stid = sys.argv[1] class bcolors: HEADER = '\033[95m' OKBLUE = '\033[94m' OKGREEN = '\033[92m' WARNING = '\033[93m' FAIL = '\033[91m' ENDC = '\033[0m' acursor.execute("""SELECT extract(year from valid) as yr, extract(month from valid) as mo, count(*), sum(case when mslp is null or mslp < 1 then 1 else 0 end), sum(case when metar is null or metar = '' then 1 else 0 end) from alldata WHERE station = %s GROUP by yr, mo ORDER by yr ASC, mo ASC""", (stid,)) for row in acursor:
import iemdb ASOS = iemdb.connect('asos') acursor = ASOS.cursor() MESOSITE = iemdb.connect('mesosite') mcursor = MESOSITE.cursor() mcursor2 = MESOSITE.cursor() IEM = iemdb.connect('iem') icursor = IEM.cursor() # Find blank start stations! mcursor.execute(""" select id, network from stations where archive_begin is null and network ~* 'ASOS' and online ORDER by network """) for row in mcursor: id = row[0] network = row[1] # Look in current for valid icursor.execute(""" SELECT valid from current where station = %s and network = %s """, (id, network) ) row = icursor.fetchone() if row: valid = row[0] if valid.year == 1980: print 'No current data for %s %s' % (id, network) else: mcursor2.execute(""" UPDATE stations SET online = 'f' where id = %s and network = %s """, (id, network) ) print 'Setting %s %s to offline' % (id, network) continue acursor.execute("""
# Process Soil Data import csv import iemdb import psycopg2.extras DBCONN = iemdb.connect('iem', bypass=True) #DBCONN = psycopg2.connect(database="iem") def load_metadata(): """ Load up what we know about these traffic sites """ meta = {} cur = DBCONN.cursor(cursor_factory=psycopg2.extras.DictCursor) cur.execute("""SELECT * from rwis_soil_data""") rows = cur.fetchall() cur.close() for row in rows: key = "%s_%s" % (row["location_id"], row["sensor_id"]) meta[ key ] = True return meta def create_sensor( row ): """ Create a sensor in the database please """ print "Adding RWIS Soil Probe: %s Probe Level: %s" % ( row["site_id"], row["sensor_id"]) cursor = DBCONN.cursor() cursor.execute("""INSERT into rwis_soil_data(location_id, sensor_id) VALUES (%s, %s)""", (row["site_id"],
import iemdb import re import numpy import numpy.ma import scipy.stats import mx.DateTime postgis = iemdb.connect('postgis', bypass=True) pcursor = postgis.cursor() pcursor.execute(""" SELECT issue, report, x(ST_Centroid(geom)), y(ST_Centroid(geom)) from warnings where wfo in ('ARX','DVN','DMX','OAX','FSD') and significance = 'W' and gtype = 'P' and phenomena = 'SV' and issue > '2008-01-01' """) SVRs = [] for row in pcursor: tokens = re.findall(r'TIME...MOT...LOC [0-9]{4}Z ([0-9]{1,3})DEG ([0-9]{1,3})KT', row[1]) if len(tokens) == 0: print row[1] continue (dir, sknt) = tokens[0] if int(sknt) > 80: continue data = {'sknt': int(sknt), 'drct': int(dir), 'ts': mx.DateTime.strptime(row[0].strftime("%Y-%m-%d %H:%M"), "%Y-%m-%d %H:%M"), 'x': row[2], 'y': row[3] } SVRs.append(data) SVR_DRCT = [] TOR_DRCT = [] SVR_SKNT = []
sites.append( site ) print `sites` sys.exit() """ import sys import os import smtplib from email import encoders from email.mime.base import MIMEBase from email.mime.multipart import MIMEMultipart import mx.DateTime import iemdb from xlwt import Workbook MESOSITE = iemdb.connect('mesosite', bypass=True) mcursor = MESOSITE.cursor() COOP = iemdb.connect('coop', bypass=True) ccursor = COOP.cursor() IEM = iemdb.connect('iem', bypass=True) icursor = IEM.cursor() DATA = """IA0112,ALBI4,A IA0133,ALGI4,B IA0157,ASNI4,C IA0200,AMSI4,F IA0203,AESI4,G IA0214,AMOI4,H IA0241,AKYI4,I IA0364,ATLI4,J IA0385,AUDI4,K
""" Drive the reprocessing of the MWcomp plot. We are doing this since our data archives have more data than the stuff I previously got from NSSL """ import mx.DateTime import subprocess import iemdb import os ASOS = iemdb.connect("asos", bypass=True) acursor = ASOS.cursor() min10 = mx.DateTime.RelativeDateTime(minutes=10) def metar_extract(now): """ Giveme a METAR file with the data we have in the coffers """ acursor.execute( """ SELECT metar from t%s WHERE valid BETWEEN '%s+00' and '%s+00' and metar is not null """ % (now.year, (now - min10).strftime("%Y-%m-%d %H:%M"), (now + min10).strftime("%Y-%m-%d %H:%M")) ) output = open("metar.txt", "w") output.write("\x01\r\r\n") output.write("000 \r\r\n") output.write("SAUS99 KISU %s\r\r\n" % (now.strftime("%d%H%M"),)) output.write("METAR\r\r\n")
1 langley is 41840.00 J m-2 is 41840.00 W s m-2 is 11.622 W hr m-2 So 1000 W m-2 x 3600 is 3,600,000 W s m-2 is 86 langleys Dr Arritt wants MJ m-2 dy-1 """ import netCDF4 import datetime import pyproj import numpy import iemdb import sys import os COOP = iemdb.connect('coop', bypass=True) ccursor = COOP.cursor() ccursor2 = COOP.cursor() def get_gp(xc, yc, x, y): """ Return the grid point closest to this point """ distance = [] xidx = (numpy.abs(xc-x)).argmin() yidx = (numpy.abs(yc-y)).argmin() dx = x - xc[xidx] dy = y - yc[yidx] movex = -1 if dx >= 0: movex = 1 movey = -1 if dy >= 0:
import datetime events = [ [0, "?", datetime.datetime(2013,2,21)], [0.27, 6.8, datetime.datetime(2013,1,30)], [0.37, 4.5, datetime.datetime(2012,2,4)], [0.59, 7.1, datetime.datetime(2011,1,10)], [0.28, 6.4, datetime.datetime(2010,1,25)], [0.97, 15.5, datetime.datetime(2009,12,8)], [0.27, 7.6, datetime.datetime(2009,2,13)], ] import numpy as np import numpy.ma as npma import iemdb MOS = iemdb.connect('mos', bypass=True) mcursor = MOS.cursor() import pytz import matplotlib.pyplot as plt (fig, ax) = plt.subplots(1,1) ys = 1.0 ylabels = [] for (precip,snow, sts) in events: sts = sts.replace(tzinfo=pytz.timezone("UTC")) mcursor.execute(""" select runtime, sum(precip) from model_gridpoint_%s where station = 'KDSM' and model = 'GFS' and ftime BETWEEN '%s 00:00+00' and '%s 00:00+00' and runtime < '%s 00:00+00' GROUP by runtime ORDER by runtime """ % (sts.year, sts.strftime("%Y-%m-%d"), (sts + datetime.timedelta(days=2)).strftime("%Y-%m-%d"), sts.strftime("%Y-%m-%d"))) x = []