avgpar, FROM weather
eliminate the comma. Robert W. Burgholzer Surface Water Modeler Office of Water Supply and Planning Virginia Department of Environmental Quality [EMAIL PROTECTED] 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kirk Wythers Sent: Wednesday, August 15, 2007 2:58 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] monthly climate query You were right Regina. I did some testing and the subselects were indeed returning averages for the entire weather table. I also pulled all the -999 values out and replaced them with NULLs. The way it should have been in the first place. 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? 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 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, station_id, round(avg(precip)::numeric, 2) AS avgprecip, round(avg(tmin)::numeric, 2) AS avgtmin, round(avg(tmax)::numeric, 2) AS avgtmax, round(avg(par)::numeric, 2) AS avgpar, FROM weather GROUP BY month, station_id) AS avgclim WHERE w.station_id = 219101; On Aug 14, 2007, at 3:44 PM, Obe, Regina wrote: 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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@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. _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
