<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16481" name=GENERATOR>
<STYLE>@font-face {
font-family: Tahoma;
}
@font-face {
font-family: Lucida Grande;
}
@page Section1 {size: 8.5in 11.0in; margin: 1.0in 1.25in 1.0in 1.25in; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: blue; TEXT-DECORATION: underline
}
P {
FONT-SIZE: 12pt; MARGIN-LEFT: 0in; MARGIN-RIGHT: 0in; FONT-FAMILY: "Times New Roman"
}
SPAN.EmailStyle21 {
COLOR: navy; FONT-FAMILY: Arial
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-US
style="WORD-WRAP: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space"
vLink=blue link=blue>
<DIV dir=ltr align=left><SPAN class=274355119-15082007><FONT face=Arial
color=#0000ff size=2>On second thought</FONT></SPAN><SPAN
class=274355119-15082007><FONT face=Arial color=#0000ff size=2></DIV>
<DIV dir=ltr align=left>
<DIV dir=ltr align=left><SPAN class=564223919-15082007><FONT face=Arial
color=#0000ff size=2>avgclim <STRONG>ON avgclim.station_id =</STRONG> <FONT
face="Times New Roman"><FONT color=#000000><FONT
size=3><STRONG>w.station_id<SPAN class=274355119-15082007> AND w.month =
avgclim.month</SPAN></STRONG></FONT></FONT></FONT></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=564223919-15082007><FONT face=Arial
color=#0000ff size=2><FONT face="Times New Roman"><FONT color=#000000><FONT
size=3><SPAN
class=274355119-15082007></SPAN></FONT></FONT></FONT></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=564223919-15082007><FONT face=Arial
color=#0000ff size=2><FONT face="Times New Roman"><FONT color=#000000><FONT
size=3><SPAN class=274355119-15082007>and also adding the new fields in the
select part with cases</SPAN></FONT></FONT></FONT></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=564223919-15082007><FONT face=Arial
color=#0000ff size=2><FONT face="Times New Roman"><FONT color=#000000><FONT
size=3><SPAN
class=274355119-15082007></SPAN></FONT></FONT></FONT></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=564223919-15082007><FONT face=Arial
color=#0000ff size=2><FONT face="Times New Roman"><FONT color=#000000><FONT
size=3><SPAN
class=274355119-15082007>e.g</SPAN></FONT></FONT></FONT></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=564223919-15082007><FONT face=Arial
color=#0000ff size=2><FONT><FONT><SPAN class=274355119-15082007><FONT
face="Times New Roman" color=#000000 size=3> CASE w.tmax</FONT>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT color=#000000><SPAN
style="FONT-SIZE: 12pt"> WHEN <SPAN
class=274355119-15082007>Null</SPAN> THEN avgtmax.avg</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT color=#000000><SPAN
style="FONT-SIZE: 12pt"> ELSE
w.tmax</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><SPAN
style="FONT-SIZE: 12pt"><FONT color=#000000> END<SPAN
class=274355119-15082007> As
tmaxfinal</SPAN></FONT></SPAN></P></DIV></SPAN></FONT></FONT></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=564223919-15082007><FONT face=Arial
color=#0000ff size=2><STRONG><FONT face="Times New Roman"><FONT
color=#000000><FONT size=3><SPAN
class=274355119-15082007></SPAN></FONT></FONT></FONT></STRONG></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=564223919-15082007><FONT face=Arial
color=#0000ff size=2><STRONG><FONT face="Times New Roman"><FONT
color=#000000><FONT size=3><SPAN
class=274355119-15082007></SPAN></FONT></FONT></FONT></STRONG></FONT></SPAN> </DIV></FONT></SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Obe,
Regina<BR><B>Sent:</B> Wednesday, August 15, 2007 3:45 PM<BR><B>To:</B> PostGIS
Users Discussion<BR><B>Subject:</B> RE: [postgis-users] monthly climate
query<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV dir=ltr align=left><SPAN class=564223919-15082007><FONT face=Arial
color=#0000ff size=2>In addition to what Rob mentioned, I think you are also
missing an ON for the avgclim part</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=564223919-15082007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=564223919-15082007><FONT face=Arial
color=#0000ff size=2>I'm guessing should be</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=564223919-15082007><FONT face=Arial
color=#0000ff size=2>avgclim <STRONG>ON avgclim.station_id = <FONT
face="Times New Roman" color=#000000
size=3>w.station_id</FONT></STRONG></FONT></SPAN></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Burgholzer,Robert<BR><B>Sent:</B> Wednesday, August 15, 2007 3:09
PM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> RE: [postgis-users]
monthly climate query<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt">avgpar, FROM weather</SPAN></FONT></P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt">eliminate the comma.</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<DIV>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Robert W.
Burgholzer</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Surface Water
Modeler</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Office of Water Supply
and Planning</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Virginia</SPAN></FONT><FONT
face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"> </SPAN></FONT><FONT
face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Department</SPAN></FONT><FONT
face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"> of Environmental
Quality</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><A
href="mailto:rwburgholzer@deq.virginia.gov">rwburgholzer@deq.virginia.gov</A></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">804-698-4405</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Open Source Modeling
Tools:</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><A
href="http://sourceforge.net/projects/npsource/">http://sourceforge.net/projects/npsource/</A></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Web-Based Water Supply
Planning Demo:</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><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: 0.5in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; 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 face=Tahoma
size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">Wednesday, August 15,
2007</SPAN></FONT><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"> </SPAN></FONT><FONT face=Tahoma
size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">2:58
PM</SPAN></FONT><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"><BR><B><SPAN
style="FONT-WEIGHT: bold">To:</SPAN></B> </SPAN></FONT><FONT face=Tahoma
size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">PostGIS Users
Discussion</SPAN></FONT><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; 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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">SELECT CASE</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">WHEN w.station_id = site_near.station_id
THEN w.obs_id</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><SPAN class=apple-tab-span><FONT
face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt">
</SPAN></FONT></SPAN>ELSE s.obs_id</P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><SPAN class=apple-tab-span><FONT
face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt">
</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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">FROM
site_near</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">INNER JOIN solar s</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><SPAN class=apple-tab-span><FONT
face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt">
</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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">INNER JOIN weather
w</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><SPAN class=apple-tab-span><FONT
face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt">
</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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">INNER JOIN (SELECT month,
station_id,</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><SPAN class=apple-tab-span><FONT
face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt">
</SPAN></FONT></SPAN>round(avg(precip)::numeric, 2) AS avgprecip,</P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><SPAN class=apple-tab-span><FONT
face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt">
</SPAN></FONT></SPAN>round(avg(tmin)::numeric, 2) AS avgtmin,</P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><SPAN class=apple-tab-span><FONT
face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt">
</SPAN></FONT></SPAN>round(avg(tmax)::numeric, 2) AS avgtmax,</P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><SPAN class=apple-tab-span><FONT
face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt">
</SPAN></FONT></SPAN>round(avg(par)::numeric, 2) AS avgpar,</P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">FROM weather</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">GROUP BY month, station_id) AS
avgclim</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">WHERE w.station_id =
219101;</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">On </SPAN></FONT>Aug 14, 2007, at 3:44 PM,
Obe, Regina wrote:</P></DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"><BR><BR></SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
color=black size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: black">(SELECT MONTH,
station_id,</SPAN></FONT><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial"> </SPAN></FONT></P>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">
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: 0.5in"><FONT face=Arial color=blue
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">
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: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">
FROM
weather</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">
GROUP BY MONTH,
station_id) As wmonthsummer</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">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: 0.5in"><FONT face=Arial color=blue
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">And then
you could blissfully do</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
color=black size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: black">(SELECT MONTH,
station_id,</SPAN></FONT><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial"> </SPAN></FONT></P>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">
round(avg(precip)::numeric, 2) AS avgprecip,
</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">
round(avg(tmax)::numeric, 2) AS avgtmax,</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">
FROM
weather</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">
GROUP BY MONTH,
station_id) As wmonthsummer</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">Hope that
helps,</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">Regina</SPAN></FONT></P></DIV></DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<DIV class=MsoNormal style="MARGIN-LEFT: 0.5in; TEXT-ALIGN: center"
align=center><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">
<HR align=center width="100%" SIZE=2>
</SPAN></FONT></DIV>
<P class=MsoNormal
style="MARGIN-BOTTOM: 12pt; MARGIN-LEFT: 0.5in; MARGIN-RIGHT: 0in"><B><FONT
face=Tahoma size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">From:</SPAN></FONT></B><FONT
face=Tahoma size=2><SPAN style="FONT-SIZE: 10pt; 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 face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">Tuesday, August 14,
2007</SPAN></FONT><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"> </SPAN></FONT><FONT face=Tahoma
size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">4:25
PM</SPAN></FONT><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; 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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">Did I miss something? </SPAN></FONT></P>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">On </SPAN></FONT>Aug 14, 2007, at 3:13 PM,
Obe, Regina wrote:</P></DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"><BR><BR></SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">Hope that
helps,</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">Regina</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<DIV class=MsoNormal style="MARGIN-LEFT: 0.5in; TEXT-ALIGN: center"
align=center><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">
<HR align=center width="100%" SIZE=2>
</SPAN></FONT></DIV>
<P class=MsoNormal
style="MARGIN-BOTTOM: 12pt; MARGIN-LEFT: 0.5in; MARGIN-RIGHT: 0in"><B><FONT
face=Tahoma size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">From:</SPAN></FONT></B><FONT
face=Tahoma size=2><SPAN style="FONT-SIZE: 10pt; 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 face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">Tuesday, August 14,
2007</SPAN></FONT><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"> </SPAN></FONT><FONT face=Tahoma
size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">12:15
PM</SPAN></FONT><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; 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: 0.5in"><SPAN class=apple-style-span><FONT
face="Lucida Grande" size=1><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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><SPAN class=apple-style-span><FONT
face="Lucida Grande" size=1><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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><SPAN class=apple-style-span><FONT
face="Lucida Grande" size=1><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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">SELECT CASE</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">WHEN w.station_id = site_near.station_id
THEN w.obs_id</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">ELSE s.obs_id</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">END</SPAN></FONT> AS obs_id,</P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN
style="FONT-SIZE: 12pt">site_near.station_id,</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">site_near.longname,</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">w.year,</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">w.doy,</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">--replace missing values (-999) with the
monthly average</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"> CASE
w.tmax</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"> WHEN -999
THEN avgtmax.avg</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"> ELSE
w.tmax</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
END,</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">CASE w.tmin</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"> WHEN -999
THEN avgtmin.avg</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"> ELSE
w.tmin</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
END,</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">CASE s.par</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"> WHEN -999
THEN avgpar.avg</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"> ELSE
s.par</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
END,</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">CASE w.precip</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"> WHEN -999
THEN avgprecip.avg</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"> ELSE
w.precip</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
END</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">FROM
site_near</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"> INNER JOIN solar
s</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"> ON
(site_near.ref_solar_station_id = s.station_id</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
AND site_near.obs_year = s.year)</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"> INNER JOIN
weather w</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"> ON
(site_near.ref_weather_station_id = w.station_id</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
AND site_near.obs_year = w.year</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
AND s.date = w.date)</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"> INNER JOIN
(SELECT MONTH,</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
round(avg(tmax)::numeric, 2) AS avg</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
FROM
weather</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
WHERE tmax !=
-999</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
GROUP BY MONTH) AS avgtmax</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"> ON
(w.month = avgtmax.month)</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">INNER JOIN (SELECT
MONTH,</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
round(avg(tmin)::numeric, 2) AS avg</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
FROM
weather</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
WHERE tmin !=
-999</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
GROUP BY MONTH) AS avgtmin</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"> ON
(w.month = avgtmin.month)</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"> INNER JOIN
(SELECT MONTH,</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
round(avg(par)::numeric,
2) AS avg</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
FROM solar</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
WHERE par !=
-999</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
GROUP BY MONTH) AS avgpar</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"> ON
(s.month = avgpar.month)</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">INNER JOIN (SELECT
MONTH,</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
round(avg(precip)::numeric, 2) AS avg</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
FROM
weather</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
WHERE precip !=
-999</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
GROUP BY MONTH) AS avgprecip</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"> ON
(w.month = avgprecip.month)</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">--select station to output climate data by
id number</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">WHERE w.station_id =
219101</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV class=MsoNormal style="MARGIN-LEFT: 0.5in; TEXT-ALIGN: center"
align=center><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">
<HR align=center width="100%" SIZE=1>
</SPAN></FONT></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<P style="MARGIN-LEFT: 0.5in"><STRONG><B><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN
style="FONT-SIZE: 12pt">_______________________________________________</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">postgis-users mailing
list</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"><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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"><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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV class=MsoNormal style="MARGIN-LEFT: 0.5in; TEXT-ALIGN: center"
align=center><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">
<HR align=center width="100%" SIZE=1>
</SPAN></FONT></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<P style="MARGIN-LEFT: 0.5in"><STRONG><B><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN
style="FONT-SIZE: 12pt">_______________________________________________</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">postgis-users mailing
list</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"><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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"><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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV></DIV>
<P>
<HR SIZE=1>
<P></P>
<P><STRONG>The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure pursuant to
Massachusetts 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.
</STRONG></P></BODY></HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts 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.
</STRONG></P></BODY></HTML>