[postgis-users] monthly climate query

[Kirk Wythers]

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

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