0

Background:

I have snowpack records (expressed as Snow Water Equivalent) for the past 40+ years for numerous sites in the Pacific Northwest, USA. I need to determine the first and last dates that snow was on the ground for each water year (which extends from October 1 to September 30), for each site.

During the summer, the snowpack is (not surprisingly) zero. As winter approaches, a date will occur when the first snowfall occurs; a non-zero value will then appear in the data for that date. As winter progresses the snowpack increases to a maximum, then decreases throughout the spring back to zero.

I need to find the first and last dates where the snowpack value was non-zero, for all 40+ years, for all sites.

There is a separate Geopackage attribute table (containing all 40+ years of data) for each site, that looks like this:

Water Year Date    Snowpack
1978   1978-10-01  0.0
1978   1978-10-02  0.1
1978   1978-10-03  0.0 => Here, all the snow melted; these are ignored
1978   1978-10-04  0.1
1978   1978-10-05  0.2
1978   1978-10-06  0.4
...
This continues through water year 2021

In the above example, the first date for water year 1978 would be 1978-10-02.

I have been determining these dates manually, by a series of filters and sorts. However, this will become exhausting, so I'm looking for a way to automate.

Question:

For each water year, how do I determine the first and last dates that snow was recorded, without resorting to a manual method?

Bonus for also determining the date with the maximum snowpack value, along with that value!

1
  • This is a very simple summary statistics task. The only challenge is creating an integer attribute for individual hydrological year.
    – FelixIP
    Mar 14 at 6:24

2 Answers 2

1

First and last days in the year with snowpack value:

select x.theyear, x.mindate, max(sn.snowpack) as firstsnowval, y.maxdate, max(sx.snowpack) as lastsnowval
from (
    select theyear,  min(thedate)  as mindate
    from snows
    where snowpack > 0
    group by theyear )  x 
join (
    select theyear,  max(thedate)  as maxdate
    from snows
    where snowpack > 0
    group by theyear )  y on y.theyear = x.theyear
join snows sn on sn.thedate = x.mindate
join snows sx on sx.thedate = y.maxdate
group by x.theyear, x.mindate, y.maxdate
order by x.theyear

enter image description here

0

In QGIS you can use virtual Layers with SQL for that. The SQL statement should be something like this one:

select max(Date) from your_table group by water_year having snowpack > 0

you should avoid column names with spaces if possible

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.