[postgis-users] monthly climate query

[Obe, Regina]

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. 
 
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
 
(SELECT   MONTH, station_id, 
                            round(avg(CASE WHEN precip != -999 THEN
precip ELSE NULL END)::numeric, 2) AS avgprecip, 
                         round(avg(CASE WHEN tmax != -999 THEN tmax ELSE
NULL END)::numeric, 2) AS avgtmax,
                   FROM     weather
                   GROUP BY MONTH, station_id) As wmonthsummer
 
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.
 
And then you could blissfully do
 
(SELECT   MONTH, station_id, 
                            round(avg(precip)::numeric, 2) AS avgprecip,

                         round(avg(tmax)::numeric, 2) AS avgtmax,
                   FROM     weather
                   GROUP BY MONTH, station_id) As wmonthsummer
 
Hope that helps,
Regina

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Kirk
Wythers
Sent: Tuesday, August 14, 2007 4:25 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] monthly climate query


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.  

Did I miss something? 

On Aug 14, 2007, at 3:13 PM, Obe, Regina wrote:


	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.
	 
	Hope that helps,
	Regina

________________________________

	From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Kirk
Wythers
	Sent: Tuesday, August 14, 2007 12:15 PM
	To: Discussion PostGIS Users
	Subject: [postgis-users] monthly climate query
	
	
	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).  
	
	
	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. 
	
	
	Here is the daily query:
	
	
	SELECT CASE
	WHEN w.station_id = site_near.station_id THEN w.obs_id
	ELSE s.obs_id
	END AS obs_id,
	site_near.station_id,
	site_near.longname,
	w.year,
	w.doy,
	--replace missing values (-999) with the monthly average
	       CASE w.tmax
	         WHEN -999 THEN avgtmax.avg
	         ELSE w.tmax
	       END,
	CASE w.tmin
	         WHEN -999 THEN avgtmin.avg
	         ELSE w.tmin
	       END,
	CASE s.par
	         WHEN -999 THEN avgpar.avg
	         ELSE s.par
	       END,
	CASE w.precip
	         WHEN -999 THEN avgprecip.avg
	         ELSE w.precip
	       END
	FROM  site_near
	       INNER JOIN solar s
	         ON (site_near.ref_solar_station_id = s.station_id
	             AND site_near.obs_year = s.year)
	       INNER JOIN weather w
	         ON (site_near.ref_weather_station_id = w.station_id
	             AND site_near.obs_year = w.year
	             AND s.date = w.date)
	       INNER JOIN (SELECT   MONTH,
	                            round(avg(tmax)::numeric, 2) AS avg
	                   FROM     weather
	                   WHERE    tmax != -999
	                   GROUP BY MONTH) AS avgtmax
	         ON (w.month = avgtmax.month)
	INNER JOIN (SELECT   MONTH,
	                            round(avg(tmin)::numeric, 2) AS avg
	                   FROM     weather
	                   WHERE    tmin != -999
	                   GROUP BY MONTH) AS avgtmin
	         ON (w.month = avgtmin.month)
	       INNER JOIN (SELECT   MONTH,
	                            round(avg(par)::numeric, 2) AS avg
	                   FROM     solar
	                   WHERE    par != -999
	                   GROUP BY MONTH) AS avgpar
	         ON (s.month = avgpar.month)
	INNER JOIN (SELECT   MONTH,
	                            round(avg(precip)::numeric, 2) AS
avg
	                   FROM     weather
	                   WHERE    precip != -999
	                   GROUP BY MONTH) AS avgprecip
	         ON (w.month = avgprecip.month)
	--select station to output climate data by id number
	WHERE  w.station_id = 219101

________________________________



	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. 

	_______________________________________________
	postgis-users mailing list
	postgis-users at postgis.refractions.net
	http://postgis.refractions.net/mailman/listinfo/postgis-users





-----------------------------------------
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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070814/8f7184f5/attachment.html>