from pyiem.plot import MapPlot, nwsprecip
from shapely.wkb import loads
import psycopg2
import numpy as np
from matplotlib.patches import Polygon
from matplotlib.collections import PatchCollection
import matplotlib.colors as mpcolors

DBCONN = psycopg2.connect(database='idep', host='iemdb', user='nobody')
cursor = DBCONN.cursor()


m = MapPlot(sector='iowa', axisbg='white', nologo=True,
            subtitle='1 Jan 2013 thru 27 September 2016',
            title='DEP Calendar Days with HUC12 Precip >= 2 inches')

cursor.execute("""
 WITH data as (
    select huc_12, count(*) as val from results_by_huc12 where scenario = 0
    and qc_precip > (25.4 * 2) and valid > '2013-01-01'
    GROUP by huc_12
)

 SELECT ST_Transform(simple_geom, 4326), data.val
 from huc12 i JOIN data on (data.huc_12 = i.huc_12) ORDER by val DESC

""")

bins = np.arange(0, 19, 3)
cmap = nwsprecip()
cmap.set_under('white')
cmap.set_over('black')
norm = mpcolors.BoundaryNorm(bins, cmap.N)
patches = []

for row in cursor:
    # print "%s,%s" % (row[2], row[1])
    polygon = loads(row[0].decode('hex'))
    a = np.asarray(polygon.exterior)
    x, y = m.map(a[:, 0], a[:, 1])
    a = zip(x, y)
    # c = cmap( norm([float(row[1]),]) )[0]
    c = cmap(norm([float(row[1]), ]))[0]
    p = Polygon(a, fc=c, ec='None', zorder=2, lw=.1)
    patches.append(p)

m.ax.add_collection(PatchCollection(patches, match_original=True))
m.draw_colorbar(bins, cmap, norm, units='days')

m.drawcounties()
m.postprocess(filename='test.png')

@@ -327,16 +327,30 @@
 sor()%0A%0A%0A
+threshold = 2%0A
 m = MapP
@@ -477,21 +477,17 @@
 tle=
+(
 'DEP 
-Calendar
+Two
  Day
@@ -510,20 +510,27 @@
 cip 
+Total 
 %3E= 
-2
+%25s
  inches'
 )%0A%0Ac
@@ -525,16 +525,52 @@
  inches'
+%0A                   ) %25 (threshold,)
 )%0A%0Acurso
@@ -622,120 +622,550 @@
 12, 
-count(*) as val from results_by_huc12 where scenario = 0%0A    and qc_precip %3E (25.4
+qc_precip,%0A      qc_precip +%0A      lag(qc_precip) OVER (PARTITION by huc_12 ORDER by valid ASC) as p1,%0A      qc_precip +%0A      lead(qc_precip) OVER (PARTITION by huc_12 ORDER by valid ASC) as p2,%0A      valid,%0A      lag(valid) OVER (PARTITION by huc_12 ORDER by valid ASC) as lag_valid,%0A      lead(valid) OVER (PARTITION by huc_12 ORDER by valid ASC) as lead_valid%0A    from results_by_huc12 where scenario = 0%0A    and valid %3E '2013-01-01'%0A), agg as (%0A    SELECT huc_12, count(*) as val%0A    from data where p1 %3E (%25s
  * 2
+5.4
 ) and 
-valid %3E '2013-01-01'
+p2 %3C (%25s * 25.4)
 %0A   
@@ -1226,19 +1226,16 @@
 4326), d
-ata
 .val%0A fr
@@ -1254,21 +1254,19 @@
 OIN 
-data
+agg d
  on (d
-ata
 .huc
@@ -1303,16 +1303,40 @@
 ESC%0A%0A%22%22%22
+, (threshold, threshold)
 )%0A%0Abins 
@@ -1354,13 +1354,13 @@
 (0, 
-19, 3
+29, 4
 )%0Acm

save sql for precip logic