title='1+ TOR warn for 100 most active TOR warn days 1986-2015', subtitle=('A day is defined as 12 to 12 UTC period, did the ' 'county get 1+ warning during those 100 events?'), cwas=True) bins = np.arange(0, 101, 10) bins[0] = 1 norm = mpcolors.BoundaryNorm(bins, cmap.N) pcursor.execute(""" WITH data as ( SELECT ugc, date(issue at time zone 'UTC' + '12 hours'::interval) from warnings where phenomena in ('TO') and significance = 'W' ), maxdays as ( SELECT date, count(*) from data GROUP by date ORDER by count DESC LIMIT 100 ), events as ( SELECT distinct ugc, d.date from data d JOIN maxdays m on (m.date = d.date) ) SELECT ugc, count(*) from events GROUP by ugc """) data = {} for row in pcursor: data[row[0]] = float(row[1]) m.fill_ugc_counties(data, bins, cmap=cmap, units='Count') m.postprocess(filename='test.png')
GROUP by ugc), u as (SELECT ugc, ST_Area(ST_Transform(geom, 2163)) / 1000000. as area from ugcs where substr(ugc,3,1) = 'C' and end_ts is null) SELECT data.ugc, data.data, data.data / u.area from data JOIN u on (u.ugc = data.ugc) ''' pcursor.execute(""" WITH data as ( SELECT ugc, count(*) / %s as data from (select distinct ugc, generate_series(issue, expire, '1 minute'::interval) from warnings where phenomena in %s and significance = 'W' and ugc is not null and (expire - issue) < '1440 minutes'::interval and issue > %s and issue < %s) as foo2 GROUP by ugc), u as (SELECT ugc, ST_Area(ST_Transform(geom, 2163)) / 1000000. as area from ugcs where substr(ugc,3,1) = 'C' and end_ts is null) SELECT data.ugc, data.data, data.data / u.area from data JOIN u on (u.ugc = data.ugc) """, (opts['years'], opts['dbcols'], opts['sdate'], opts['edate'])) data = {} for row in pcursor: data[row[0]] = float(row[2 if opts['normalized'] else 1]) m.fill_ugc_counties(data, bins, cmap=cmap, units=opts['units']) m.postprocess(filename=fn) subprocess.call("xv %s" % (fn,), shell=True)
u as (SELECT ugc, ST_Area(ST_Transform(geom, 2163)) / 1000000. as area from ugcs where substr(ugc,3,1) = 'C' and end_ts is null) SELECT data.ugc, data.data, data.data / u.area from data JOIN u on (u.ugc = data.ugc) ''' pcursor.execute( """ WITH data as ( SELECT ugc, count(*) / %s as data from (select distinct ugc, generate_series(issue, expire, '1 minute'::interval) from warnings where phenomena in %s and significance = 'W' and ugc is not null and (expire - issue) < '1440 minutes'::interval and issue > %s and issue < %s) as foo2 GROUP by ugc), u as (SELECT ugc, ST_Area(ST_Transform(geom, 2163)) / 1000000. as area from ugcs where substr(ugc,3,1) = 'C' and end_ts is null) SELECT data.ugc, data.data, data.data / u.area from data JOIN u on (u.ugc = data.ugc) """, (opts['years'], opts['dbcols'], opts['sdate'], opts['edate'])) data = {} for row in pcursor: data[row[0]] = float(row[2 if opts['normalized'] else 1]) m.fill_ugc_counties(data, bins, cmap=cmap, units=opts['units']) m.postprocess(filename=fn) subprocess.call("xv %s" % (fn, ), shell=True)
WITH data as ( SELECT distinct s.iemid from summary s JOIN stations t on (t.iemid = s.iemid) WHERE t.network = 'IACOCORAHS' and s.day > '2014-04-12' and pday > 0) SELECT ugc_county, count(*) from stations t JOIN data d on (d.iemid = t.iemid) GROUP by ugc_county ORDER by count DESC """) data = {} for row in icursor: data[row[0]] = row[1] # Query out centroids of counties... pcursor.execute("""SELECT ugc, ST_x(ST_centroid(geom)) as lon, ST_y(ST_centroid(geom)) as lat from ugcs WHERE state = 'IA' and end_ts is null and substr(ugc,3,1) = 'C'""") clons = [] clats = [] cvals = [] for row in pcursor: cvals.append(data.get(row[0],0)) clats.append(row[2]) clons.append(row[1]) m = MapPlot(axisbg='white', title='Iowa CoCoRaHS Observers Per County', subtitle='Sites with at least one report in past year (Apr 2014-2015)') m.fill_ugc_counties(data, [1,2,3,4,5,7,10,15,20]) m.plot_values(clons, clats, cvals) m.drawcounties() m.postprocess(filename='test.png')
axisbg='#EEEEEE', title='1+ TOR warn for 100 most active TOR warn days 1986-2015', subtitle= 'A day is defined as 12 to12 UTC period, did the county get 1+ warning during those 100 events?', cwas=True) bins = np.arange(0, 101, 10) bins[0] = 1 norm = mpcolors.BoundaryNorm(bins, cmap.N) pcursor.execute(""" WITH data as ( SELECT ugc, date(issue at time zone 'UTC' + '12 hours'::interval) from warnings where phenomena in ('TO') and significance = 'W' ), maxdays as ( SELECT date, count(*) from data GROUP by date ORDER by count DESC LIMIT 100 ), events as ( SELECT distinct ugc, d.date from data d JOIN maxdays m on (m.date = d.date) ) SELECT ugc, count(*) from events GROUP by ugc """) data = {} for row in pcursor: data[row[0]] = float(row[1]) m.fill_ugc_counties(data, bins, cmap=cmap, units='Count') m.postprocess(filename='test.png')
SELECT distinct s.iemid from summary s JOIN stations t on (t.iemid = s.iemid) WHERE t.network = 'IACOCORAHS' and s.day > '2014-04-12' and pday > 0) SELECT ugc_county, count(*) from stations t JOIN data d on (d.iemid = t.iemid) GROUP by ugc_county ORDER by count DESC """) data = {} for row in icursor: data[row[0]] = row[1] # Query out centroids of counties... pcursor.execute("""SELECT ugc, ST_x(ST_centroid(geom)) as lon, ST_y(ST_centroid(geom)) as lat from ugcs WHERE state = 'IA' and end_ts is null and substr(ugc,3,1) = 'C'""") clons = [] clats = [] cvals = [] for row in pcursor: cvals.append(data.get(row[0], 0)) clats.append(row[2]) clons.append(row[1]) m = MapPlot( axisbg='white', title='Iowa CoCoRaHS Observers Per County', subtitle='Sites with at least one report in past year (Apr 2014-2015)') m.fill_ugc_counties(data, [1, 2, 3, 4, 5, 7, 10, 15, 20]) m.plot_values(clons, clats, cvals) m.drawcounties() m.postprocess(filename='test.png')