[postgis-users] monthly climate query

[Kirk Wythers]

I will give this a try Robert. Thanks. The daily query is working and  
I think you are visualizing the math correctly. I probably do not  
need the -999 replacement (actually, as I think about it, the -999  
replacement would quite mess up the monthly averages since the  
replacement algorithm finds the average monthly value for all  
years...  some years are missing all data from some months). You  
suggestion to group on month and year should take care of that.

the obs_id column is simply a concatenated unique value to insure  
that there is no data duplication. If memory serves it is a  
combination of station_id, year, month, day.
On Aug 14, 2007, at 3:12 PM, Burgholzer,Robert wrote:

> Kirk,
>
> I am not sure what exactly is NOT working here, but I will take a  
> stab.  If your daily is WORKING already, then simply encase it in a  
> sub-query, i.e.:
>
>
> Select a.month, a.year, avg(a.tmax) as tmax, avg(a.tmin) as tmin,  
> avg(a.precip) as precip) from
>
>   ( your daily patching clause that already works)
>
> as a
>
> group by a.month, a.year;
>
>
> However, I am not sure that you even need to use your “patching”  
> query at all, if all you are doing during the replacement of -999  
> values is to replace them with the average of days that don’t have  
> -999s.  If I am visualizing the math right, these additional days  
> will not change the monthly daily average at all since they are  
> equivalent to it, therefore, you could just multiply the monthly  
> daily average (for days without -999) by the number of days in the  
> month.
>
>
> I am entirely unsure of what you are doing with the w.obs_id and  
> s.obs_id columns, and why that is in the condition, perhaps an  
> explanation could shed some light.
>
>
> Best of luck,
>
>
> Robert W. Burgholzer
>
> Surface Water Modeler
>
> Office of Water Supply and Planning
>
> Virginia Department of Environmental Quality
>
> rwburgholzer at deq.virginia.gov
>
> 804-698-4405
>
> Open Source Modeling Tools:
>
> http://sourceforge.net/projects/npsource/
>
> Web-Based Water Supply Planning Demo:
>
> http://soulswimmer.dynalias.net/models/wsdemo/demo_hsi.php
>
> -----Original Message-----
> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070814/75bf8e5e/attachment.html>