<html>
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 10 (filtered)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"Lucida Grande";
panose-1:0 0 0 0 0 0 0 0 0 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:blue;
text-decoration:underline;}
p
{margin-right:0in;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman";}
span.EmailStyle21
{font-family:Arial;
color:navy;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=blue style='word-wrap: break-word;-khtml-nbsp-mode: space;
-khtml-line-break: after-white-space'>
<div class=Section1>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>avgpar, FROM weather</span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> </span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>eliminate the comma.</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<div>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Robert W. Burgholzer</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Surface Water Modeler</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Office of Water Supply and Planning</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Virginia</span></font><font size=2
color=navy face=Arial><span style='font-size:10.0pt;font-family:Arial;
color:navy'> </span></font><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>Department</span></font><font
size=2 color=navy face=Arial><span style='font-size:10.0pt;font-family:Arial;
color:navy'> of Environmental Quality</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><a
href="mailto:rwburgholzer@deq.virginia.gov">rwburgholzer@deq.virginia.gov</a></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>804-698-4405</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Open Source Modeling Tools:</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><a
href="http://sourceforge.net/projects/npsource/">http://sourceforge.net/projects/npsource/</a></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Web-Based Water Supply Planning Demo:</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><a
href="http://soulswimmer.dynalias.net/models/wsdemo/demo_hsi.php">http://soulswimmer.dynalias.net/models/wsdemo/demo_hsi.php</a></span></font></p>
</div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>-----Original Message-----<br>
<b><span style='font-weight:bold'>From:</span></b>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <b><span
style='font-weight:bold'>On Behalf Of </span></b>Kirk Wythers<br>
<b><span style='font-weight:bold'>Sent:</span></b> </span></font><font size=2 face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'>Wednesday,
August 15, 2007</span></font><font size=2 face=Tahoma><span style='font-size:
10.0pt;font-family:Tahoma'> </span></font><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>2:58 PM</span></font><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'><br>
<b><span style='font-weight:bold'>To:</span></b> </span></font><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'>PostGIS Users
Discussion</span></font><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'><br>
<b><span style='font-weight:bold'>Subject:</span></b> Re: [postgis-users]
monthly climate query</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>You were right </span></font>Regina. I did some
testing and the subselects were indeed returning averages for the entire
weather table. </p>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>I also pulled all the -999 values out and replaced
them with NULLs. The way it should have been in the first place. </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>Here is my latest crack at your suggestions (including
getting rid of the double subselect), However I'm getting syntax error at the
FROM weather line at the end that I just can't find. Do you see it? </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>SELECT CASE</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>WHEN w.station_id = site_near.station_id THEN w.obs_id</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><span class=apple-tab-span><font
size=3 face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>ELSE
s.obs_id</p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><span class=apple-tab-span><font
size=3 face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>END
AS obs_id, site_near.station_id, site_near.longname, w.year, w.doy</p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>FROM site_near</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>INNER JOIN solar s</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><span class=apple-tab-span><font
size=3 face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>ON
(site_near.ref_solar_station_id = s.station_id AND site_near.obs_year = s.year)</p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>INNER JOIN weather w</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><span class=apple-tab-span><font
size=3 face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>ON
(site_near.ref_weather_station_id = w.station_id AND site_near.obs_year =
w.year AND s.date = w.date)</p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>INNER JOIN (SELECT month, station_id,</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><span class=apple-tab-span><font
size=3 face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>round(avg(precip)::numeric,
2) AS avgprecip,</p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><span class=apple-tab-span><font
size=3 face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>round(avg(tmin)::numeric,
2) AS avgtmin,</p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><span class=apple-tab-span><font
size=3 face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>round(avg(tmax)::numeric,
2) AS avgtmax,</p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><span class=apple-tab-span><font
size=3 face="Times New Roman"><span style='font-size:12.0pt'> </span></font></span>round(avg(par)::numeric,
2) AS avgpar,</p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>FROM weather</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>GROUP BY month, station_id) AS avgclim</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>WHERE w.station_id = 219101;</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>On </span></font>Aug 14, 2007, at 3:44 PM, Obe, Regina wrote:</p>
</div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'><br>
<br>
</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>Yes I think you did or I
missed something. Your subselects are going to return the average for the
whole weather and solar tables (not for just that reference site) which doesn't
quite seem like what you want if you are trying to put realistic placeholders
for missing data in a site. </span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>Also I don't think you
need to do 2 subselects for weather - I think you can combine into a single one
with something like below</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=3 color=black
face="Times New Roman"><span style='font-size:12.0pt;color:black'>(SELECT
MONTH, station_id,</span></font><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'> </span></font></p>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>
round(avg(CASE WHEN precip != -999 THEN precip ELSE NULL END)::numeric, 2) AS
avgprecip, </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>
round(avg(CASE WHEN tmax != -999 THEN tmax ELSE NULL END)::numeric, 2) AS
avgtmax,</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>
FROM weather</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>
GROUP BY MONTH, station_id) As
wmonthsummer</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'> </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>Actually if you had put
in NULLS for precip amd tmax instead of -999, you would be better off since
those fall out of the equation quite nicely since aggregates completely ignore
null.</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'> </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>And then you could
blissfully do</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'> </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 color=black
face="Times New Roman"><span style='font-size:12.0pt;color:black'>(SELECT
MONTH, station_id,</span></font><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'> </span></font></p>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>
round(avg(precip)::numeric, 2) AS avgprecip, </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>
round(avg(tmax)::numeric, 2) AS avgtmax,</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>
FROM weather</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>
GROUP BY MONTH, station_id) As
wmonthsummer</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'> </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>Hope that helps,</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>Regina</span></font></p>
</div>
</div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<div class=MsoNormal align=center style='margin-left:.5in;text-align:center'><font
size=3 face="Times New Roman"><span style='font-size:12.0pt'>
<hr size=2 width="100%" align=center>
</span></font></div>
<p class=MsoNormal style='margin-right:0in;margin-bottom:12.0pt;margin-left:
.5in'><b><font size=2 face=Tahoma><span style='font-size:10.0pt;font-family:
Tahoma;font-weight:bold'>From:</span></font></b><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>
postgis-users-bounces@postgis.refractions.net [<a
href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</a>]
<b><span style='font-weight:bold'>On Behalf Of </span></b>Kirk Wythers<br>
<b><span style='font-weight:bold'>Sent:</span></b> </span></font><font size=2 face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'>Tuesday, August
14, 2007</span></font><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'> </span></font><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>4:25 PM</span></font><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'><br>
<b><span style='font-weight:bold'>To:</span></b> PostGIS Users Discussion<br>
<b><span style='font-weight:bold'>Subject:</span></b> Re: [postgis-users]
monthly climate query</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>If I follow your point Regina (as far as the working
daily query goes), I am returning data for a particular site based on the last
line... WHERE w.station_id = some_valid_number. </span></font></p>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>Did I miss something? </span></font></p>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>On </span></font>Aug 14, 2007, at 3:13 PM, Obe, Regina wrote:</p>
</div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'><br>
<br>
</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>Wouldn't you want to
return the average for month for that particular site? None of your month
subselects seem to group by station id. Would seem to me
logically you should add station_id to each of your subselects, group by
station_id as well as month and then your ON clause would be month and
station_id.</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>Hope that helps,</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=blue face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:blue'>Regina</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<div class=MsoNormal align=center style='margin-left:.5in;text-align:center'><font
size=3 face="Times New Roman"><span style='font-size:12.0pt'>
<hr size=2 width="100%" align=center>
</span></font></div>
<p class=MsoNormal style='margin-right:0in;margin-bottom:12.0pt;margin-left:
.5in'><b><font size=2 face=Tahoma><span style='font-size:10.0pt;font-family:
Tahoma;font-weight:bold'>From:</span></font></b><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'> <a
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>
[<a href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</a>]
<b><span style='font-weight:bold'>On Behalf Of </span></b>Kirk Wythers<br>
<b><span style='font-weight:bold'>Sent:</span></b> </span></font><font size=2 face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'>Tuesday, August
14, 2007</span></font><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'> </span></font><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>12:15 PM</span></font><font
size=2 face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'><br>
<b><span style='font-weight:bold'>To:</span></b> Discussion PostGIS Users<br>
<b><span style='font-weight:bold'>Subject:</span></b> [postgis-users] monthly
climate query</span></font></p>
<div>
<p class=MsoNormal style='margin-left:.5in'><span class=apple-style-span><font
size=1 face="Lucida Grande"><span style='font-size:8.5pt;font-family:"Lucida Grande"'>I
need some help with rewriting a query. I have a query that dumps daily climate
data, filling in missing data with monthly averages (one line per
day). </span></font></span></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><span class=apple-style-span><font
size=1 face="Lucida Grande"><span style='font-size:8.5pt;font-family:"Lucida Grande"'>I
want to output monthly averages (one line per month). I am having a hard time
wrapping my head around this. Particularly how to deal with the doy column (day
of year). I have tried several approaches and my forehead is starting to get my
keyboard bloody. </span></font></span></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><span class=apple-style-span><font
size=1 face="Lucida Grande"><span style='font-size:8.5pt;font-family:"Lucida Grande"'>Here
is the daily query:</span></font></span></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>SELECT CASE</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>WHEN w.station_id = site_near.station_id THEN w.obs_id</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>ELSE s.obs_id</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>END</span></font> AS obs_id,</p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>site_near.station_id,</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>site_near.longname,</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>w.year,</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>w.doy,</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>--replace missing values (-999) with the monthly
average</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> CASE w.tmax</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> WHEN -999 THEN
avgtmax.avg</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> ELSE w.tmax</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> END,</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>CASE w.tmin</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> WHEN -999 THEN
avgtmin.avg</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> ELSE w.tmin</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> END,</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>CASE s.par</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> WHEN -999 THEN
avgpar.avg</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> ELSE s.par</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> END,</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>CASE w.precip</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> WHEN -999 THEN
avgprecip.avg</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> ELSE w.precip</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> END</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>FROM site_near</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> INNER JOIN solar s</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> ON
(site_near.ref_solar_station_id = s.station_id</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> AND
site_near.obs_year = s.year)</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> INNER JOIN weather w</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> ON
(site_near.ref_weather_station_id = w.station_id</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> AND
site_near.obs_year = w.year</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> AND
s.date = w.date)</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> INNER JOIN
(SELECT MONTH,</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>
round(avg(tmax)::numeric, 2)
AS avg</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>
FROM weather</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>
WHERE tmax != -999</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>
GROUP BY MONTH) AS avgtmax</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> ON (w.month =
avgtmax.month)</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>INNER JOIN (SELECT MONTH,</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>
round(avg(tmin)::numeric, 2)
AS avg</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>
FROM weather</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>
WHERE tmin != -999</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>
GROUP BY MONTH) AS avgtmin</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> ON (w.month =
avgtmin.month)</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> INNER JOIN
(SELECT MONTH,</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>
round(avg(par)::numeric, 2) AS
avg</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>
FROM solar</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>
WHERE par != -999</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>
GROUP BY MONTH) AS avgpar</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> ON (s.month =
avgpar.month)</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>INNER JOIN (SELECT MONTH,</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>
round(avg(precip)::numeric, 2)
AS avg</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>
FROM weather</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>
WHERE precip != -999</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>
GROUP BY MONTH) AS avgprecip</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> ON (w.month =
avgprecip.month)</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>--select station to output climate data by id number</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>WHERE w.station_id = 219101</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
<div class=MsoNormal align=center style='margin-left:.5in;text-align:center'><font
size=3 face="Times New Roman"><span style='font-size:12.0pt'>
<hr size=1 width="100%" align=center>
</span></font></div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
<p style='margin-left:.5in'><strong><b><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>The substance of this message, including any
attachments, may be confidential, legally privileged and/or exempt from
disclosure pursuant to </span></font></b></strong><strong><b><font
face="Times New Roman">Massachusetts</font></b></strong><strong><b><font
face="Times New Roman"> law. It is intended solely for the addressee. If you
received this in error, please contact the sender and delete the material from
any computer. </font></b></strong></p>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>_______________________________________________</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>postgis-users mailing list</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a></span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'><a
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a></span></font></p>
</div>
</div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
<div class=MsoNormal align=center style='margin-left:.5in;text-align:center'><font
size=3 face="Times New Roman"><span style='font-size:12.0pt'>
<hr size=1 width="100%" align=center>
</span></font></div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
<p style='margin-left:.5in'><strong><b><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>The substance of this message, including any
attachments, may be confidential, legally privileged and/or exempt from
disclosure pursuant to </span></font></b></strong><strong><b><font
face="Times New Roman">Massachusetts</font></b></strong><strong><b><font
face="Times New Roman"> law. It is intended solely for the addressee. If you
received this in error, please contact the sender and delete the material from
any computer. </font></b></strong></p>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>_______________________________________________</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>postgis-users mailing list</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a></span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'><a
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a></span></font></p>
</div>
</div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
</div>
</body>
</html>