windspeed extreme values

[Joachim Puttkammer]

Hi,

how can i delete the two peaks in the berlchertown year chart?
These peaks do not appear in the season year diagram.

Joachim

[gjr80]

Hi,

Have you worked through the Cleaning up old 'bad' data wiki page with an emphasis on the field windSpeed? You most likely do not see the spikes on the Seasons year plots as the Seasons year plots plot a day average value, perhaps you are seeing the spikes on the Belchertown plots because they plot a different aggregate (or perhaps no aggregate at all).

Gary

[Joachim Puttkammer]

I have tried the instruction "old 'bad' data" . But I use the mysql-database. To convert the DB ( 1 million records) to a sqlite-DB was not succesfull.
Furthermore run here 2 instances, so no weewx.conf or weewx db but with other names ( ecowitt.conf with weewx_eco db etc).

Joachim

[Tom Keffer]

MySQL should have similar SQL commands. Off the top of my head, I can't tell you what they are, but I imagine they are nearly identical.

You would type them in using the MySQL client tool "mysql". If you don't know how to use that tool, I would suggest learning. I would not recommend using MySQL unless you are thoroughly familiar with it. 

--
You received this message because you are subscribed to the Google Groups "weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/11ca26b6-bbc2-46ec-9d98-e067f51ae86en%40googlegroups.com.

[vince]

When you picked mysql you essentially signed up for learning more on your own.

See if https://www.mysqltutorial.org/mysql-update-data.aspx helps you any in figuring out the mysql command to do something similar to what the wiki sqlite3 example is doing.

[gjr80]

I'm afraid your going to have to do a bit of self help. If you've taken the steps to use MySQL/MariaDB there is an expectation you have some level of familiarity with MySQL/MariaDB.  All you are doing is searching your archive table for values that are clear wrong.

Something like:

$ mysql -u root -p
mysql> SELECT * FROM archive WHERE windSpeed>100;

or limit the fields you are displaying:

mysql> SELECT datetime,windSpeed FROM archive WHERE windSpeed>100;


if you have any replace the 'bad' fields with null:

mysql> UPDATE archive SET windSpeed=NULL WHERE windSpeed>100;

Then drop/rebuild the daily summaries as per the wiki page.

Gary

[gjr80]

Also, when using commands such as wee_database in a multi (WeeWX) instance environment you specify which WeeWX config file (and hence which database) you are operating on with the --config command line option, eg:

$ wee_database --config=/home/weewx/my_config.conf --drop-daily

This is a concept that weewx-multi users need to be familiar with. Might pay to have a read of the Utilities Guide for the utilities you need to use.

Gary

[Doug Jenkins]

Joachim:

Cleaning up bad data in your MySQL database can be done using a SQL editor. I used DBeaver (dbeaver.io) which is a universal database manager. It will connect to MySQL, SQLite, and MariaDB (which is what I use for WeeWX) and a lot of other databases.

This is what i would do:

1. Download dbeaver from dbeaver.io and set it up on your workstation

2. in DBeaver, under Database, select new connection. Provide the host name and port (I believe it is 3306) of the mySQL Server instance and login with the same login that you are using with WeeWX.

3. Open a new SQL Script window by clicking on SQL Editor->New SQL Script. Ensure you select the connection and database above.

4. Now using the Bad Data wiki, find the errant record by querying the archive table in the database. The SQLite commands should work as both databases are SQL99 compliant.

5. Before you delete any rows, dump the database to a file. you can do that with DBeaver by clicking on the connection, navigate to the database, right click on the database and select dump database. See screenshot for details.

6. delete the row by finding the dateTime value you want to remove and use that in your WHERE Clause. You can use the keyword FROM_UNIXTIME to convert the dateTime value to a real date. here is an example:
     select FROM_UNIXTIME(dateTime) , windGust from archive; 

Let me know if that helps

--

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

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/dd279948-9e2f-43d3-88ca-be017b753eecn%40googlegroups.com.

--

Thanks,

Doug Jenkins

Phone: 813.391.5686 | do...@dougjenkins.com | www.dougjenkins.com

[Joachim Puttkammer]

@gjr80,
I had already executed the command

mysql> UPDATE archive SET windSpeed=NULL WHERE windSpeed>100;

 with the result that the two extreme values disappeared in the season year query.

The --config command line option was the solution for the wee_database line.

▶ wee_database --config=/etc/weewx/eco.conf --drop-daily
Using configuration file /etc/weewx/eco.conf
Using database binding 'wx_binding', which is bound to database 'archive_mysql'
Proceeding will delete all your daily summaries from database 'weewx_eco'
Are you sure you want to proceed (y/n)? y
Dropping daily summary tables from 'weewx_eco' ...
Daily summary tables dropped from database 'weewx_eco' in 1.36 seconds

▶ wee_database --config=/etc/weewx/eco.conf --rebuild-daily
Using configuration file /etc/weewx/eco.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_eco' ...
Records processed: 1070000; time: 2022-01-14 13:16:00 CET (1642162560)
Processed 1070232 records to rebuild 745 daily summaries in 1826.84 seconds
Rebuild of daily summaries in database 'weewx_eco' complete

I was initially satisfied,but the Belchertown plot continued to have 2 peaks.

@do...@dougjenkins.com,
thanks for the tip, I will take a closer look at dbeaver-ce.

Joachim

[vince]

Those peaks are windGust, not wind.

You need to also clean up the windGust values.

[Joachim Puttkammer]

@vince;
Database changed
MariaDB [weewx_eco]> SELECT * FROM `archive` WHERE `windGust` > 100;
Empty set (9.819 sec)

It wasn'nt

[vince]

Check archive_day_windSpeed and archive_day_windGust and see which days have max over 100.

select dateTime,max,maxtime from `archive_day_windSpeed` where `max` > 100;

select dateTime,max,maxtime from `archive_day_windGust` where `max` > 100;

[gjr80]

It's pretty clear from the original post that the issue is with windSpeed not windGust (unless you live in an area where over time windSpeed is always equal to or higher than windGust :) ). 

Plus the following would indicate that the two bogus/extreme values have been found and nulled:

> I had already executed the command 

> mysql> UPDATE archive SET windSpeed=NULL WHERE windSpeed>100;

> with the result that the two extreme values disappeared in the season year query.

A quick look at the Belchertown code shows that that daily summaries are not used for Highcharts plots, besides they will be 'fixed' when you drop/rebuild them. Further looking at the Belchertown code it appears that a similar approach is used to WeeWX in that Highcharts data files that cover longer periods (eg year) are not generated every archive cycle. Given this you might want to go through your Belchertown output and delete all of the .json files. This should force regeneration.

Gary

[Joachim Puttkammer]

The peak problem is eliminated ( solved ?)

MariaDB [weewx_eco]> SELECT 'windSpeed' FROM `archive` WHERE `windSpeed` > 100;

I have reduced the value 100 in steps to the value 10.

MariaDB [weewx_eco]> SELECT 'windSpeed' FROM `archive` WHERE `windSpeed` > 10;
2 rows in set (2.307 sec)

and set the windspeed value to NULL in these two rows.
The two peaks have now disappeared from the annual chart.
I do not understand why the extreme values were only displayed at threshold 10 in the query.

Joachim