[postgis-users] monthly climate query

Obe, Regina robe.dnd at cityofboston.gov
Wed Aug 15 12:45:20 PDT 2007


In addition to what Rob mentioned, I think you are also missing an ON
for the avgclim part
 
I'm guessing should be
avgclim ON avgclim.station_id = w.station_id
________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Burgholzer,Robert
Sent: Wednesday, August 15, 2007 3:09 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] monthly climate query



avgpar, FROM weather

 

eliminate the comma.

 

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: 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: 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. 

_______________________________________________

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/20070815/ba417876/attachment.html>


More information about the postgis-users mailing list