Consecutive wet & dry days

[AndyH]

Hi all,

I have been using mySQL to process the dayfile for my website for quite a while and would like to add another statistic to my website - but I cannot get my head around it and wondered whether anyone out there had done a similar job.

Basically, I want to be able to produce the top x consecutive wet days and the same for dry days. I would like to do this server side using mySQL. Has anyone got some help/advice.

If it makes things clearer, I have done similar with highest temperatures, wind gusts, average temperature etc etc on this page:
http://www.sompt.com/weather/history/to ... php?top=10

Thank you!

[tobyspond]

Hi,

These two threads may help:

https://cumulus.hosiene.co.uk/viewtopic.php?f=14&t=2608
https://cumulus.hosiene.co.uk/viewtopic.p ... l&start=30

Kerry

[AndyH]

Thanks Kerry - that has given me some good pointers. The result I now get is the max consecutive days with/without rain but not the top 10 etc. If I change the LIMIT from 1 then it gives me more data but I want the unique top 10 days rather than including those on the run up to number 1 (if that makes sense)

I did write and try the code below which I wrote which works, EXCEPT that it does not carry on the count past the end of the month. i.e when the month rolls over it starts the count again:

set @count=0;
select
dayfile.logdate, dayfile.totrainfall, t2.totrainfall,
@count := if(dayfile.totrainfall > 0 AND t2.totrainfall > 0, @count + 1, 1)
from dayfile
left join dayfile t2 on dayfile.logdate = t2.logdate + 1
where dayfile.totrainfall > 0

It also takes a while to run but that's another issue I will deal with later.

Any ideas how I can get around the month roll over issue?

[mcrossley]

The best I can come up with quickly is below. Separate queries for wet and dry days, but they return the top 10, and take into account 0.1mm of rain being counted as a 'dry' day - you may want to tweak that.

Processes 1300 rows in my dayfile table in around 70ms on my old Pentium M laptop.

DRY DAYS
** DELETED INCORRECT CODE **

Code: Select all

SELECT MAX(LogDate) AS date, COUNT(*) cnt
FROM (
    SELECT @r := @r + (@rain != (TotRainFall > 0.1) OR @rain IS NULL) AS gr,
        @rain := (TotRainFall > 0.1),
        df.LogDate
    FROM (
        SELECT @r := 0,
            @rain := NULL
        ) vars,
        dayfile df
    ORDER BY LogDate
    ) x
GROUP BY gr
ORDER BY cnt DESC
LIMIT 10

[/s]

WET DAYS
** DELETED INCORRECT CODE **

Code: Select all

SELECT MAX(LogDate) AS date, COUNT(*) cnt
FROM (
    SELECT @r := @r + (@rain != (TotRainFall < 0.2) OR @rain IS NULL) AS gr,
        @rain := (TotRainFall < 0.2),
        df.LogDate
    FROM (
        SELECT @r := 0,
            @rain := NULL
        ) vars,
        dayfile df
    ORDER BY LogDate
    ) x
GROUP BY gr
ORDER BY cnt DESC
LIMIT 10

[/s]

[AndyH]

Oh my word - I think that does it! I have just tried the dry days code and it returns pretty much what I expected (and wanted). I shall check it against my data to make sure!

Thank you very much - I knew I could rely on this excellent forum to help me out with a problem I have been struggling with over the last few weeks.

I shall try and implement this into my website over the next week or so (partner and kids back from holiday tomorrow so time will be limited) and let you know the outcome.

Thank you again!

[mcrossley]

Let us know if it checks out, I only checked the top value. I may be using this one myself sometime

[tobyspond]

Hi Mark,

Thanks for this. I could not figure out how to create the grouping variable. However, I'm getting a mixture of wet and dry days. So I modified your code a bit to get it to work for me. Here it is:

for Wet Days
SELECT max(LogDate) AS date, COUNT(*) cnt, wd
FROM (
SELECT @r := @r + (@rain != (TotRainFall > 0) OR @rain IS NULL) AS gr,
@rain := (TotRainFall > 0), if( totrainfall <0.01, "D", "W") as wd,
df.LogDate
FROM (
SELECT
@r := 0,
@rain := NULL
) vars,
dayfile df
ORDER BY LogDate
) x where wd="W"
GROUP BY gr
ORDER BY cnt DESC
limit 10

Dry Days
SELECT max(LogDate) AS date, COUNT(*) cnt, wd
FROM (
SELECT @r := @r + (@rain != (TotRainFall > 0) OR @rain IS NULL) AS gr,
@rain := (TotRainFall > 0), if( totrainfall <0.01, "D", "W") as wd,
df.LogDate
FROM (
SELECT
@r := 0,
@rain := NULL
) vars,
dayfile df
ORDER BY LogDate
) x where wd="D"
GROUP BY gr
ORDER BY cnt DESC
limit 10


Kerry

[mcrossley]

Hi Kerry, I just tried your version (changing the dry day threshold) but the top outout does not match my records?

A refinement of my version would be to pull back both the from and to dates. Using dry days as an example...
DELETED - INCORRECT CODE

Code: Select all

SELECT MIN(LogDate) AS date_from,
       MAX(LogDate) AS date_to,
       COUNT(*) cnt
FROM
  (
  SELECT @r := @r + (@rain != (TotRainFall > 0.1) OR @rain IS NULL) AS gr,
         @rain := (TotRainFall > 0.1),
         df.LogDate
  FROM
    (
    SELECT @r := 0,
           @rain := NULL
     ) vars,
     dayfile df
     ORDER BY LogDate
  ) x
GROUP BY gr
ORDER BY cnt DESC
LIMIT 10

[/s]

Gives...

Code: Select all

date_from  date_to    cnt   
2013-02-15 2013-03-06 20 
2013-07-04 2013-07-22 19 
2010-04-08 2010-04-24 17 
2012-06-26 2012-07-11 16 
2011-02-01 2011-02-16 16 
2013-03-27 2013-04-10 15 
2012-03-19 2012-04-01 14 
2011-05-05 2011-05-18 14 
2011-12-01 2011-12-13 13 
2011-04-23 2011-05-04 12 

Its 2:18 am now - time to try and sleep! I'll look at your query again tomorrow...

[mcrossley]

Kerry, your code works better when I edit it correctly! It also has the advantage you can use a single query and get either dry or wet days back by changing a single variable. Here is your code reformatted and taking my 0.1mm threshold into account. I also added the start date...

Code: Select all

SET @wetOrDry = "D";
SELECT MIN(LogDate) AS date_from,
       MAX(LogDate) AS date_to,
       COUNT(*) cnt
FROM
  (
  SELECT @r := @r + (@rain != (TotRainFall > 0.1) OR @rain IS NULL) AS gr,
         @rain := (TotRainFall > 0.1),
         IF (TotRainFall < 0.2, "D", "W") AS wd, 
         df.LogDate
  FROM
    (
    SELECT @r := 0,
           @rain := NULL
    ) vars, 
    dayfile df
  ORDER BY LogDate
  ) x
WHERE wd = @wetOrDry 
GROUP BY gr
ORDER BY cnt DESC
LIMIT 10

The two Dry day outputs compared...

Code: Select all

My version                    Kerry's version
==========                    ===============
date_from  date_to    cnt     date_from  date_to    cnt
---------- ---------- ---     ---------- ---------- ---
2013-02-15 2013-03-06 20      2013-02-15 2013-03-06 20 
2013-07-04 2013-07-22 19      2013-07-04 2013-07-22 19 
2010-04-08 2010-04-24 17      2010-04-08 2010-04-24 17 
2012-06-26 2012-07-11 16      2013-03-27 2013-04-10 15 
2011-02-01 2011-02-16 16      2012-03-19 2012-04-01 14 
2013-03-27 2013-04-10 15      2011-04-23 2011-05-04 12 
2012-03-19 2012-04-01 14      2013-05-31 2013-06-11 12 
2011-05-05 2011-05-18 14      2010-12-17 2010-12-27 11 
2011-12-01 2011-12-13 13      2012-05-20 2012-05-30 11 
2011-04-23 2011-05-04 12      2013-04-29 2013-05-08 10 

As you can see there are differences further down the list, I'm going to have to get the dayfile into Excel to check this - I suspect Kerry's is correct

EDIT: Kerry's is of course correct - I'll delete details of my query above to avoid confusion.

[tobyspond]

Mark,

Like your reformatted version!

Users should note that the TotRainFall can be in mm or inches depending on their set up and should change those values accordingly.

Kerry

[AndyH]

Thank you again for all your efforts. Just implemented this code (with a few very minor changes to customise it for my webpages) and it works brilliantly. Hopefully others will find this useful for their pages too.

[steve]

I'm not following the SQL discussion, but presumably this would all be much easier if Cumulus logged the current wet and dry spell figures to dayfile.txt?

[mcrossley]

Yes, but where would the fun be in that!

[AndyH]

I'm not following the SQL discussion, but presumably this would all be much easier if Cumulus logged the current wet and dry spell figures to dayfile.txt?

Yes Steve, it probably would. If it's possible then I suppose see whether there is anyone else who thinks it would be useful and go with the majority. Personally, I prefer to get this sort of stuff my a database it's just that my MySQL skills are rather rusty having left the IT world a number of years back.

[mm23]

Hi,

i am looking for data for longest dry and wet spell. As I understand the wabtags is consecutive rain and wet days from ini pages.
Which is the way to put that data on my month and year table....or should I simply use the carry code for database from above?

Thanks everybody!