I have only cleaned the database in post-processing and then replaced it. On Tue, Oct 12, 2021, 5:04 PM HRM Resident <foc1...@gmail.com> wrote:
> Regardless of whether it’s a median filter, a Kalman filter or > anything else, integrating it into weewx doesn’t seem trivial to me. This > is something Tom and the other developers would need to consider and decide > the best way to proceed. Also, how many users would take advantage of it. > There’s a time series based on the loop intervals, etc., to think of as > well. > > I agree it probably would be best as an optional add-on, but again, I > am not one of the coders. Personally, I would only use such a feature to > de-spike the signal strength. I don’t have a need for an alarm, but others > might. > > Lastly, I wonder if there’s already a way to trigger an alarm. That’s > a feature that I haven’t researched, if it does exist. I threw my two > cents in only because of the simplicity and robustness of a small median > filter. Getting one working with weewx and/or an alarm is out of my league. > > For what it’s worth, I too am growing tomatoes, but we’ve harvested > the lot this week, escaping the frost due to an unusually mild autumn. But > gardening is off topic! :-) > > - Paul VE1DX > > On Oct 12, 2021, at 8:02 PM, Stephen Hocking <stephen.hock...@gmail.com> > wrote: > > > It does seem like an ideal use-case for a Kalman filter. > > > https://medium.com/@jaems33/understanding-kalman-filters-with-python-2310e87b8f48 > > On Wed, 13 Oct 2021 at 09:54, Richard Horobin <richardh9...@gmail.com> > wrote: > >> I accept that you want to remove spikes. >> >> Do you think this process could be used to detect "warning" levels? eg >> tomato plants are not frost-hardy at all, so we need a warning when the >> temperature goes to 5C. >> >> Many other processes require warnings at plus or minus 2.5 standard >> deviations, as used in Shewhart Statistical Process Control charts. I >> imagine this would be an optional add-on function, as it's non-core to >> weewx. >> >> On Tuesday, 12 October 2021 at 00:14:07 UTC+11 WindnFog wrote: >> >>> FWIW, here's how I'm doing it in C on an Arduino to de-spike/smooth >>> temperature and humidity: >>> >>> #define buff_size 7 // Must be an odd number. Should be greater than >>> 5. 7 works well. >>> >>> >>> >snip< >>> >>> void bubble_sort(float sort_array[], int n) >>> { >>> int i, j; >>> float temp; >>> >>> for (i = 0 ; i < n - 1; i++) >>> { >>> for (j = 0 ; j < n - i - 1; j++) >>> { >>> if (sort_array[j] > sort_array[j+1]) >>> { >>> // Swap values >>> temp = sort_array[j]; >>> sort_array[j] = sort_array[j+1]; >>> sort_array[j+1] = temp; >>> } >>> } >>> } >>> } >>> >>> >>> >snip< >>> >>> >>> // Sort them. Use quick and dirty bubble sort because it's a small >>> number of data points >>> bubble_sort(h_array_sort, buff_size); >>> bubble_sort(t_array_sort, buff_size); >>> >>> // Use the median of the last "buff_size" readings for the display >>> median_index = buff_size / 2; >>> >>> h = h_array_sort[median_index]; >>> t = t_array_sort[median_index]; >>> >>> >>> - Paul VE1DX >>> >>> >>> On 2021-10-11 9:55 a.m., WindnFog wrote: >>> >>> This would be out of my league to develop in Python. In my Fortran and >>> Pascal programming days of data acquisition, we routinely used 5,7, or 9 >>> point median filters to smooth and de-spike oceanographic data. I would >>> think this might be a valuable addition to weewx. >>> >>> It's not a lot of code (bubble sort a small array of input data with the >>> median value being the output). Still, where the weewx developers would put >>> it and how users would configure which channels to filter, the width of the >>> filter, etc., might be a bridge too far. Just a thought. >>> >>> - Paul VE1DX >>> >>> On Tuesday, October 5, 2021 at 10:56:53 AM UTC-3 alankj...@gmail.com >>> wrote: >>> >>>> Here is a description of my adventures cleaning up the database >>>> >>>> http://www.adelieresources.com//2019/01/conversion-from-wview-to-weewx-weather-station-software/ >>>> >>>> On Monday, October 4, 2021 at 6:30:16 AM UTC-7 wfs...@gmail.com wrote: >>>> >>>>> Thanks Tom! I love Weewx and get a kick out of playing with the >>>>> data. My biggest variance here in Springfield Illinois was 4.4 degrees. >>>>> On 4/21/21 at 12:15pm i went from 49.1 to 56.3 then fell back to >>>>> 54.7. No rain that day, just clouds and sun I guess. Breezy. >>>>> >>>>> Walt >>>>> >>>>> On Sunday, October 3, 2021 at 8:39:09 PM UTC-5 tke...@gmail.com wrote: >>>>> >>>>>> Wow! Some serious SQL fu! >>>>>> >>>>>> Very nice. >>>>>> >>>>>> On Sun, Oct 3, 2021 at 12:20 PM wfs...@gmail.com <wfs...@gmail.com> >>>>>> wrote: >>>>>> >>>>>>> Here's an updated query that does better checking and it can delete >>>>>>> the rows or update the temperatures. Test it out on a copy of your >>>>>>> database first. This works for sqlite. Don't know about others. >>>>>>> >>>>>>> Walt >>>>>>> >>>>>>> On Sunday, October 3, 2021 at 6:48:23 AM UTC-5 anc...@gmail.com >>>>>>> wrote: >>>>>>> >>>>>>>> Exactly. Is there a way to consider only cases such as +0, -10, +0 >>>>>>>> (i.e. spikes) and not those like +0, -6. -8 ? >>>>>>>> Meanwhile I thank you for your precious help. >>>>>>>> Andrea >>>>>>>> >>>>>>>> Il giorno giovedì 30 settembre 2021 alle 23:03:35 UTC+2 >>>>>>>> wfs...@gmail.com ha scritto: >>>>>>>> >>>>>>>>> Just a reminder, this delete query will delete all situations >>>>>>>>> where the criteria is met. Some of these situations are legit, temps >>>>>>>>> can >>>>>>>>> change rapidly. You want to make sure it's going to delete only the >>>>>>>>> records you want before running it. There are probably some >>>>>>>>> enhancements >>>>>>>>> that can be made to the query to find more obvious spikes. Something >>>>>>>>> like >>>>>>>>> +0, -10, +0 might be a spike, whereas +0, -6. -8 is not, the temp is >>>>>>>>> just >>>>>>>>> falling fast. >>>>>>>>> >>>>>>>>> On Thursday, September 30, 2021 at 1:11:05 PM UTC-5 >>>>>>>>> wfs...@gmail.com wrote: >>>>>>>>> >>>>>>>>>> Well, make sure you back up your database before you try to >>>>>>>>>> delete anything in case this goes wrong. I would >>>>>>>>>> >>>>>>>>>> 1. Stop weewx >>>>>>>>>> 2. Make a copy of the database >>>>>>>>>> 3. Execute the delete SQL >>>>>>>>>> 4. Run the original query again to see if things are OK >>>>>>>>>> 5. Rebuild weewx dailies >>>>>>>>>> 6. Start weewx >>>>>>>>>> >>>>>>>>>> Hopefully I'm not forgetting anything. >>>>>>>>>> >>>>>>>>>> I'm attaching SQL to delete the spiked records. You will have to >>>>>>>>>> change the database name in the .open statement to the db you want to >>>>>>>>>> change. You may need to run sqlite3 with sudo in order to delete >>>>>>>>>> records. >>>>>>>>>> >>>>>>>>>> I thought a better solution would be to update the spiked temp >>>>>>>>>> with an average of the readings surrounding the spike, but I'm having >>>>>>>>>> trouble coming up with the SQL to do that. >>>>>>>>>> >>>>>>>>>> Walt >>>>>>>>>> On Thursday, September 30, 2021 at 9:41:17 AM UTC-5 >>>>>>>>>> anc...@gmail.com wrote: >>>>>>>>>> >>>>>>>>>>> Thank you Walt, this script works fine! in fact it highlighted >>>>>>>>>>> that spike in my graph: >>>>>>>>>>> >>>>>>>>>>> DT datetime temp temp_prior >>>>>>>>>>> temp_next variance d_back d_forw >>>>>>>>>>> ------------------- ---------- ---------------- >>>>>>>>>>> ---------------- ---------------- ---------- ------------------ >>>>>>>>>>> ---------------- >>>>>>>>>>> 2021-09-30 04:15:00 1632968100 19.2970370370371 >>>>>>>>>>> 19.3363567649282 14.5003779289494 2.38 0.0393197278911508 >>>>>>>>>>> 4.79665910808766 >>>>>>>>>>> >>>>>>>>>>> (I added two columns d_back = |t1-t2| and t_forw = |t1-t3|) >>>>>>>>>>> Now, I need to delete the wrong record: in this case, it is t3 >>>>>>>>>>> (d_forw = 4.8). How can I automatically find it and then remove? >>>>>>>>>>> Unfortunately I don't know the sql language and I can't complete >>>>>>>>>>> the script >>>>>>>>>>> myself. >>>>>>>>>>> >>>>>>>>>>> Thank you very much, >>>>>>>>>>> Andrea >>>>>>>>>>> Il giorno giovedì 30 settembre 2021 alle 15:27:37 UTC+2 >>>>>>>>>>> wfs...@gmail.com ha scritto: >>>>>>>>>>> >>>>>>>>>>>> Here's a query using the "join a table to itself" method if you >>>>>>>>>>>> don't have the LAG function. It prints observations that are >>>>>>>>>>>> outside the >>>>>>>>>>>> average of the surrounding observations by 2+ degrees. I get >>>>>>>>>>>> about 30 >>>>>>>>>>>> observations this year and I think it's legit. Although I don't >>>>>>>>>>>> get spikes >>>>>>>>>>>> in my data, I do use a 15 minute interval and my station is closer >>>>>>>>>>>> to the >>>>>>>>>>>> ground than it should be. A lot can happen in 15 minutes and my >>>>>>>>>>>> temp graph >>>>>>>>>>>> is always kinda wiggly. >>>>>>>>>>>> >>>>>>>>>>>> Caveat Emptor on the attached query. You could modify it to >>>>>>>>>>>> delete or update the offending rows instead of printing them out. >>>>>>>>>>>> >>>>>>>>>>>> Walt >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> On Thursday, September 30, 2021 at 7:52:41 AM UTC-5 >>>>>>>>>>>> tke...@gmail.com wrote: >>>>>>>>>>>> >>>>>>>>>>>>> Yes, it's possible, but it's not a simple SELECT statement. >>>>>>>>>>>>> For example, this query will return all rows where the temperature >>>>>>>>>>>>> difference between adjacent rows is greater than 1 degree: >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> *SELECT g.* FROM (SELECT dateTime, >>>>>>>>>>>>> datetime(dateTime,'unixepoch','localtime'), LAG(outTemp) OVER >>>>>>>>>>>>> (ORDER BY >>>>>>>>>>>>> dateTime) AS prev_outTemp, outTemp FROM archive) AS g WHERE >>>>>>>>>>>>> ABS(g.outTemp-g.prev_outTemp) > 1; * >>>>>>>>>>>>> >>>>>>>>>>>>> For my own database, this returns >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> *1192323300|2007-10-13 17:55:00|64.099998|63.0 >>>>>>>>>>>>> 1192382100|2007-10-14 10:15:00|51.900002|53.0 >>>>>>>>>>>>> 1192756800|2007-10-18 >>>>>>>>>>>>> 18:20:00|44.700001|46.700001 1192757100|2007-10-18 >>>>>>>>>>>>> 18:25:00|46.700001|49.5 >>>>>>>>>>>>> 1192757400|2007-10-18 18:30:00|49.5|47.400002 * >>>>>>>>>>>>> ... >>>>>>>>>>>>> >>>>>>>>>>>>> You would then need to arrange to delete those specific >>>>>>>>>>>>> timestamps. >>>>>>>>>>>>> >>>>>>>>>>>>> The select statement will only work with modern versions of >>>>>>>>>>>>> sqlite, which have the LAG() function. >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> On Thu, Sep 30, 2021 at 3:35 AM anc...@gmail.com < >>>>>>>>>>>>> anc...@gmail.com> wrote: >>>>>>>>>>>>> >>>>>>>>>>>>>> Hi there, >>>>>>>>>>>>>> I need to remove some temperature spikes from my weewx.sdb >>>>>>>>>>>>>> database. I know this has been already asked many times, but my >>>>>>>>>>>>>> case is >>>>>>>>>>>>>> different. I need to delete spikes in which the values are >>>>>>>>>>>>>> climatically >>>>>>>>>>>>>> acceptable: so, it is not sufficient to give (temperature is >>>>>>>>>>>>>> intended in °C) >>>>>>>>>>>>>> >>>>>>>>>>>>>> echo "delete from archive where outTemp < -6;" | sudo sqlite3 >>>>>>>>>>>>>> /home/weewx/archive/weewx.sdb >>>>>>>>>>>>>> >>>>>>>>>>>>>> for solving the problem. In my case, the spike was about 16°C >>>>>>>>>>>>>> when temperature was about 20°C (see the graph below) >>>>>>>>>>>>>> [image: daytempdew.png] >>>>>>>>>>>>>> What I need to do is something like: DELETE if Δt = 300 AND >>>>>>>>>>>>>> |ΔT| > 5, where >>>>>>>>>>>>>> Δt is time difference (in seconds, I chose 300 because my >>>>>>>>>>>>>> archive interval is 5 minutes) and ΔT is the temperature >>>>>>>>>>>>>> difference in that >>>>>>>>>>>>>> time bin. >>>>>>>>>>>>>> In other words I am thinking about using a differential >>>>>>>>>>>>>> approach. It seems to me that this can be the only solution, is >>>>>>>>>>>>>> this >>>>>>>>>>>>>> possible with sqlite3? >>>>>>>>>>>>>> Thank you, >>>>>>>>>>>>>> Andrea >>>>>>>>>>>>>> >>>>>>>>>>>>> -- >>>>>>>>>>>>>> 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/32298c8c-6d93-4a80-8b9e-29fdcc181c8an%40googlegroups.com >>>>>>>>>>>>>> <https://groups.google.com/d/msgid/weewx-user/32298c8c-6d93-4a80-8b9e-29fdcc181c8an%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>>>>>>>>>>> . >>>>>>>>>>>>>> >>>>>>>>>>>>> -- >>>>>>> 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/a3b5661f-58d6-4c17-8ce2-4c55ca620eb1n%40googlegroups.com >>>>>>> <https://groups.google.com/d/msgid/weewx-user/a3b5661f-58d6-4c17-8ce2-4c55ca620eb1n%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>>>> . >>>>>>> >>>>>> -- >>> >>> You received this message because you are subscribed to a topic in the >>> Google Groups "weewx-user" group. >>> To unsubscribe from this topic, visit >>> https://groups.google.com/d/topic/weewx-user/nChGnMJLB2k/unsubscribe. >>> To unsubscribe from this group and all its topics, send an email to >>> weewx-user+...@googlegroups.com. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/weewx-user/f379634b-50bc-49ec-b2a3-ae243f8c8bcfn%40googlegroups.com >>> <https://groups.google.com/d/msgid/weewx-user/f379634b-50bc-49ec-b2a3-ae243f8c8bcfn%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> >>> >>> -- >> 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+unsubscr...@googlegroups.com. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/weewx-user/78f03140-a145-4912-8eb1-c9693370e51en%40googlegroups.com >> <https://groups.google.com/d/msgid/weewx-user/78f03140-a145-4912-8eb1-c9693370e51en%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> > > > -- > > "I and the public know > what all schoolchildren learn > Those to whom evil is done > Do evil in return" W.H. Auden, "September 1, 1939" > > > -- > You received this message because you are subscribed to a topic in the > Google Groups "weewx-user" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/weewx-user/nChGnMJLB2k/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > weewx-user+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/weewx-user/CA%2BxzKjC1UEkL44T6HNzjHfWgfjeAmP3RR%2Bu1J9SipHyyJy1-mg%40mail.gmail.com > <https://groups.google.com/d/msgid/weewx-user/CA%2BxzKjC1UEkL44T6HNzjHfWgfjeAmP3RR%2Bu1J9SipHyyJy1-mg%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > > -- > You received this message because you are subscribed to a topic in the > Google Groups "weewx-user" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/weewx-user/nChGnMJLB2k/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > weewx-user+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/weewx-user/F5E7CC3A-039C-490B-AA65-82260EBB8A0C%40gmail.com > <https://groups.google.com/d/msgid/weewx-user/F5E7CC3A-039C-490B-AA65-82260EBB8A0C%40gmail.com?utm_medium=email&utm_source=footer> > . > -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CADjRZrS116t%3De7nYdgE7HH1yrDgOZ%3D6MWG3yFLVgf-NLy3CT6w%40mail.gmail.com.