windrun calculations

[Pat]

I'm a bit lost on windrun right now and could use guidance. This is on weewx 3.9.2

My $year.windrun.maxsum is reporting 22617.05 miles which seems just a little high. To the point where I thought this was a Belchertown skin issue. I don't think it is because Belchertown isn't calculating windrun, just using the Cheetah tag to display it. 

The calculation of windrun seems simple. windSpeed * interval / 60.0 if windSpeed is not None.

Exporting my database to a new MySQL database, I run this query to reset all my windrun values in the archive table:

UPDATE weewx.archive SET windrun = (windSpeed * `interval` / 60.0) where windSpeed > 0;

Then I dropped daily summaries and rebuilt. After doing that my $year.windrun.maxsum is 9986.7. So there's an improvement here, but still very high. 

According to this, my highest sum windrun is on 1452038400 (Wednesday, January 6, 2016 12:00:00 AM)

MariaDB [weewx]> SELECT * FROM `archive_day_windrun` ORDER BY `sum` DESC LIMIT 1;
+------------+------+------------+---------------+------------+------------------+-------+------------------+---------+
| dateTime   | min  | mintime    | max           | maxtime    | sum              | count | wsum             | sumtime |
+------------+------+------------+---------------+------------+------------------+-------+------------------+---------+
| 1452038400 |    0 | 1452056460 | 32.6649722222 | 1452038460 | 9986.70872221597 |  1418 | 599202.523332966 |   85080 |
+------------+------+------------+---------------+------------+------------------+-------+------------------+---------+
1 row in set (0.00 sec)

To get this day range, it's 1452038400 to 1452142799 (Wednesday, January 6, 2016 11:59:59 PM GMT-05:00)

So this query below takes that time range and figures out the average wind speed, and the average windspeed across 24 hours as suggested here.

MariaDB [weewx]> select avg(windSpeed) as avg_windspeed, (avg(windSpeed)*24) as thisDayWindRun from archive where dateTime >= 1452038400 and dateTime <= 1452142799;
+---------------------+-------------------+
| avg_windspeed       | thisDayWindRun    |
+---------------------+-------------------+
| 0.09232844574780066 | 2.215882697947216 |
+---------------------+-------------------+
1 row in set (0.00 sec)

If windrun is a scalar sum, then I'm pretty sure I just add all the windspeed together to get the daily windrun? This show's it's 157.42

MariaDB [weewx]> select sum(windspeed) from archive where dateTime >= 1452038400 and dateTime <= 1452142799;
+--------------------+
| sum(windspeed)     |
+--------------------+
| 157.42000000000013 |
+--------------------+
1 row in set (0.00 sec)

Here's the same day's wind speed record:

MariaDB [weewx]> SELECT * FROM `archive_day_windSpeed` where dateTime = 1452038400;
+------------+------+------------+------+------------+--------+-------+--------+---------+
| dateTime   | min  | mintime    | max  | maxtime    | sum    | count | wsum   | sumtime |
+------------+------+------------+------+------------+--------+-------+--------+---------+
| 1452038400 |    0 | 1452038460 |  3.5 | 1452098700 | 157.37 |  1419 | 9442.2 |   85140 |
+------------+------+------------+------+------------+--------+-------+--------+---------+
1 row in set (0.00 sec)

So I'm lost on where 9,986.7 is coming from?

[Pat]

Unless the answer is simply that $year.windrun.maxsum is the wrong tag to be using to find the max windrun for the year. 

Reading this, I believe that may be the answer, however I recently changed the tag due to the comment in this issue in the Belchertown GitHub repo. 

[Thomas Keffer]

Windrun is an 'extensive' variable, which should be treated similarly to the other such variables, such as rain and ET. So, it should be summed. The total windrun for the year would be $year.windrun.sum. 

But, I suspect the number will still be big.

With your UPDATE, why the restriction for windSpeed > 0? Perhaps there are values where windrun is non-zero, even if windSpeed is zero?

One more question: what unit system is your database using?

-tk

--
You received this message because you are subscribed to the Google Groups "weewx-development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-developm...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-development/275c4b9f-b0e6-411f-acab-f62dc1bbc057%40googlegroups.com.

[Pat]

I thought that sum was right and not maxsum. This is what I had it at before, but was told I should be using maxsum. So in my research I think I lost my way a little bit and started focusing on month even though the tag was for year. Brain fart moment I guess.

On the update query; not sure on the restriction. I had shot from the hip based on the weewx calculation that windSpeed is not None. So I worked with windSpeed > 0. What would a proper query be to replace in-situ windrun?

usUnits = 1 in my database.

To unsubscribe from this group and stop receiving emails from it, send an email to weewx-de...@googlegroups.com.

[Thomas Keffer]

maxsum scans all the daily sums and return the max value. It's useful for figuring out queries like, "What's the rainiest day of the month?" In your case, it would be returning the windrun for the day with the max windrun.

I'd replace your UPDATE with:

UPDATE weewx.archive SET windrun = (windSpeed * `interval` / 60.0) where windSpeed is not null;

To unsubscribe from this group and stop receiving emails from it, send an email to weewx-developm...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-development/21f464e6-2c18-41aa-b1be-c6b43e9f037f%40googlegroups.com.

[gjr80]

A couple of thoughts...

If you did this:

UPDATE weewx.archive SET windrun = (windSpeed * `interval` / 60.0) where windSpeed > 0; 

and after rebuilding have this:

MariaDB [weewx]> SELECT * FROM `archive_day_windrun` ORDER BY `sum` DESC LIMIT 1;
+------------+------+------------+---------------+------------+------------------+-------+------------------+---------+
| dateTime   | min  | mintime    | max           | maxtime    | sum              | count | wsum             | sumtime |
+------------+------+------------+---------------+------------+------------------+-------+------------------+---------+
| 1452038400 |    0 | 1452056460 | 32.6649722222 | 1452038460 | 9986.70872221597 |  1418 | 599202.523332966 |   85080 |
+------------+------+------------+---------------+------------+------------------+-------+------------------+---------+
1 row in set (0.00 sec)

since the min/max fields refer to windrun per interval period that implies that on the day in question you had at least one windSpeed value that is 32.6649722222 * 60 / 1.0 = 1959.898333. That doesn't seem right. Either there is a windSpeed issue or the update query did not work as intended.

 

If windrun is a scalar sum, then I'm pretty sure I just add all the windspeed together to get the daily windrun?

I also don't think this is a valid assumption. You can certainly sum speeds but there needs to be some time factor applied. For example, if the wind blows a constant 20mph for 1 hour in the day. There has been 20 miles of windrun on the day. But for a one minute archive sum(windSpeed) over the day = 60 * 20 = 1200. For a five minute archive period the sum is 12 * 20 = 240. To convert to wind run in each case you need to divide the total by the number of archive records per hour. So it become 1200/60 = 20 miles for one minute and 240/12 = 20 miles for five minutes. 

Gary

PS. What aggregate are you actually after. $year.windrun.maxsum should return the max daily total windrun since 1 January. On the other hand $year.windrun.sum will return the total windrun for the year to date.

[Pat]

I want to make sure I'm not making things worse for myself. Let me say what I'm trying to do, then let me re-produce the above. 

• Goal 1: 
• Show the single day of the current year with the highest windrun. 
• Currently using: $year.windrun.maxsum 
• This is currently returning a value of 22617.05 miles for 2019. Seems high for a single day.

• Goal 2
• Show the single day of all time with the highest windrun. 
• Currently using: $alltime.windrun.maxsum
• This is currently returning a value of 42664.02 miles for all time. Seems high for a single day. 

I have erased my weewx database and re-created it from a backup. 

Some baseline queries:

Current 2019 max sum:

MariaDB [weewx]> SELECT * FROM `archive_day_windrun` WHERE year( FROM_UNIXTIME( dateTime ) ) = "2019" ORDER BY `archive_day_windrun`.`sum` DESC LIMIT 1;
+------------+--------------------+------------+------------------+------------+------------------+-------+------------------+---------+

| dateTime   | min                | mintime    | max              | maxtime    | sum              | count | wsum             | sumtime |

+------------+--------------------+------------+------------------+------------+------------------+-------+------------------+---------+
| 1549688400 | 0.0211988304093567 | 1549774800 | 128.972153488369 | 1549774500 | 22617.0506134748 |   287 | 22617.0506134748 |     287 |
+------------+--------------------+------------+------------------+------------+------------------+-------+------------------+---------+

1 row in set (0.00 sec)

Current All time max sum:

MariaDB [weewx]> SELECT * FROM `archive_day_windrun` ORDER BY `archive_day_windrun`.`sum` DESC LIMIT 1;
+------------+------------------+------------+---------------+------------+------------------+-------+------------------+---------+

| dateTime   | min              | mintime    | max           | maxtime    | sum              | count | wsum             | sumtime |

+------------+------------------+------------+---------------+------------+------------------+-------+------------------+---------+
| 1452488400 | 0.00133333333333 | 1452488460 | 59.8506111111 | 1452565800 | 42664.0181666637 |  1286 | 42664.0181666637 |    1286 |
+------------+------------------+------------+---------------+------------+------------------+-------+------------------+---------+

1 row in set (0.00 sec)

Then I run Tom's updated query to recalculate windrun:

MariaDB [weewx]> UPDATE weewx.archive SET windrun = (windSpeed * `interval` / 60.0) where windSpeed is not null;
Query OK, 347565 rows affected (2.81 sec)
Rows matched: 395690  Changed: 347565  Warnings: 0

I then drop the dailies

root@windrunfix:/etc/weewx# wee_database --drop
Using configuration file /etc/weewx/weewx.conf
Using database binding 'wx_binding', which is bound to database 'archive_mysql'
Proceeding will delete all your daily summaries from database 'weewx'
Are you sure you want to proceed (y/n)? y
Dropping daily summary tables from 'weewx' ...
Daily summary tables dropped from database 'weewx' in 1.30 seconds

And rebuild them:

root@windrunfix:/etc/weewx# wee_database --rebuild
Using configuration file /etc/weewx/weewx.conf
Using database binding 'wx_binding', which is bound to database 'archive_mysql'
All daily summaries will be rebuilt.
Proceed (y/n)? y
Rebuilding daily summaries in database 'weewx' ...
 Records processed: 396000; Last date: 2019-10-15 17:15:00 UTC (1571159700)
Processed 396100 records to rebuild 1380 day summaries in 125.75 seconds
Rebuild of daily summaries in database 'weewx' complete

Re-running the same queries above:

New 2019 max sum, this changed significantly

MariaDB [weewx]> SELECT * FROM `archive_day_windrun` WHERE year( FROM_UNIXTIME( dateTime ) ) = "2019" ORDER BY `archive_day_windrun`.`sum` DESC LIMIT 1;
+------------+-------------------+------------+------------------+------------+------------------+-------+------------------+---------+

| dateTime   | min               | mintime    | max              | maxtime    | sum              | count | wsum             | sumtime |

+------------+-------------------+------------+------------------+------------+------------------+-------+------------------+---------+
| 1549670400 | 0.116379310344827 | 1549750800 | 1.06481481481482 | 1549719600 | 152.540419966586 |   287 | 45762.1259899758 |   86100 |
+------------+-------------------+------------+------------------+------------+------------------+-------+------------------+---------+

1 row in set (0.00 sec)

New All time max sum, also a significant change:

MariaDB [weewx]> SELECT * FROM `archive_day_windrun` ORDER BY `archive_day_windrun`.`sum` DESC LIMIT 1;
+------------+--------------------+------------+------------------+------------+-----------------+-------+-----------------+---------+

| dateTime   | min                | mintime    | max              | maxtime    | sum             | count | wsum            | sumtime |

+------------+--------------------+------------+------------------+------------+-----------------+-------+-----------------+---------+
| 1489190400 | 0.0899019607843134 | 1489276200 | 1.37694444444444 | 1489256100 | 164.76581710823 |   287 | 49429.745132469 |   86100 |
+------------+--------------------+------------+------------------+------------+-----------------+-------+-----------------+---------+

1 row in set (0.00 sec)

These numbers look way more reasonable than previously. So maybe in the end my SQL query is the source of all my problems? :-)

[Thomas Keffer]

By "SQL query is the source of all my problems" do you mean the UPDATE? If so, maybe. Somehow there were a lot of bad values for windrun when windSpeed was null. You could try a SELECT for that theory.

How did they get there? Beats me. It might be worth searching your archive database and seeing if there were bad windrun values everywhere, or if they started to occur at some time.

-tk

--

You received this message because you are subscribed to the Google Groups "weewx-development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-developm...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-development/4dba4643-631f-48d4-beaf-52fa6a51a33c%40googlegroups.com.