Welcome to the Cumulus Support forum.

Latest Cumulus MX release 3.10.5 (build 3122) - 06 April 2021 (please see announcement regarding releases since 3.5.0)
Legacy Cumulus 1 release v1.9.4 (build 1099) - 28 November 2014 (a patch is available for 1.9.4 build 1099 that extends the date range of drop-down menus to 2030)

Download the Software (Cumulus MX / Cumulus 1 and other related items) from the Wiki

Create Missing for MX

Discussion and support for 3rd-party (non-Sandaysoft) tools for Cumulus
Post Reply
Posts: 979
Joined: Fri 27 Jul 2012 11:29 am
Weather Station: Combination Mix of FO stations
Operating System: R Pi OS

Create Missing for MX

Post by sfws »

My first Php Hypertext Pre-processor script can be used by those using Cumulus 1 as well as those using MX. It works on any standard log file, and calculates derived fields, adding any that are missing. The output is a file with a different extension (.csv) so using it won't ruin your original file. Obviously if you are happy, you can remove old file and rename new file to make it have correct (.txt) extension.

This first script processStandardLog.php is written as a web page with a HTML form where you select
* A standard log file that you want to update (choose the month). You need to have uploaded this file into your web server, because it is doubtful you can access where your log files are from a web server environment (and even if you can access external files, many browsers will give "cross origin" error messages for files not in your web server).
* Whether you use Celsius or are in USA and use Fahrenheit for your temperature values in that log file
* Which unit you use for wind speed
* Whether you use decimal point or decimal comma.
When you submit the form, it runs a check that all inputs have been made (there are no initial defaults); if any input has not been selected, you are asked to check the form and resubmit.

The script transfers all lines of the selected log file into an array, it reads the temperature, humidity and average wind speed for each individual log line. It then checks the following fields (counting from zero as date and time), updating if necessary:
* Field 4: Dew point
* Field 15: Wind chill
* Field 16: USA Heat Index
* Field 21: Australian Apparent temperature
* Field 27: Feels like temperature (the first version of this script used MX formula as per version 3.6.0 onwards, the next version of my script used incorrect MX formula as per 3.6.8, but script has been updated to use formula as per version 3.6.10 onwards, and I hope that really is the final formula)
* Field 28: Canadian Humidity Index (Humidex) - available from version 3.6.12, properly released in 3.7.0

You need to install 3 files on your web server for this web page to work: form.css (styling), phpInclude.php (formulas), and processStandardLog.php (the file that produces web page). The 3.6.10-12 improved script runs so quickly that normally you won't see the progress messages, but when it completes you will see a message that it has created a new log file with the extension ".csv". Obviously, this script will add "Feels Like" to any log entry created before version 3.6.0 (even old Cumulus 1 logs), and correct it into latest formula calculation for any log entry between version 3.6.0 and 3.6.9. Equally this script will add Humidex to any log entry created before 3.7.0 (even old Cumulus 1 logs).
If for any reason you made an edit to temperature, humidity, or wind speeds since the log line was first created, then the derived values will be wrong, so my script will also automatically correct all fields listed above if the recalculation is different to value read from log line.
When it completes one update, the form returns with the settings from the previous run, so all you need to edit is the log file name, then press submit, to update another log file.

The form content also explains how to get your log files onto your web server where the script runs, and how to get your database table updated from these corrected log files. Once you have updated your database table, you are ready for my next script!

In Cumulus 1 there was a facility to read the standard logs created each month to hold a small sub-set of all the readings, to recalculate the daily extremes to insert for any missing day in the daily summary log. Steve Loft called this "Create Missing". I know the current MX developer has a low priority idea for implementing a separate executable that can offer similar functionality for MX users.

In the meantime, part of that functionality is in the second of my Php Hypertext Pre-processor scripts:

Please be aware that as this script works on database tables, not log files, it is not ready to run without some editing by you. You need to tell it what your database tables are, and how to connect to your database, my script does not read any information from Cumulus configuration files. You need to be able to code in PHP, you need to know what details to add to the script and how to add them, so the script can connect to your database, I cannot provide any support on that.

My second script will read any column in monthly table (default is FeelsLike column, but you can edit it so it works on Humidex, apparent temperature, or whatever), find the maximum and minimum for each day, and update the daily summary table (default is MaxFeelsLike and MinFeelsLike columns, and their corresponding time-stamps) with these. This action mimics part of Steve Loft's Create Missing functionality.

This is based on some scripts that I routinely use. Mine has separate scripts for updating minimum and maximum columns in the output table, and a parameter where you specify the column to read on the input table, while it has the equivalent output columns in my schema for all possible input columns hard coded into it.

Rather than share my scripts that are specific to my schema (my database column names), I have shared this related script that updates maximum and minimum in one script, is hard coded for feels like, and takes no parameters, so it can be run as a web page (there are no external parameters to put in a query-string) in a browser or in batch mode against a web/database server.

Consequently, I stress you need to edit this PHP code directly to change which columns it updates. You will need some PHP knowledge to make it able to connect to your database and/or to modify the column to be read in the monthly table. If the column to be updated in the daily table is something like total rainfall, wind run, heat index, or humidex, you will need even more PHP knowledge as you need to remove all the minimum handling code.

I could have written this as a HTML form, but this script is more complicated than the log file update script and trying to write alternative code for all the parts that could be edited manually is not something I wished to attempt.

This script can either replace null values or update existing values in your database table, I have included a check whether it is reducing the existing low values and raising the existing high values, and I have included a cut-off date you must change this to when MX 3.6.10 was implemented on your site and the correct feels like values started being stored. The script will output the previous value (be it null or something else) when it successfully does an update. Depending on how you decide to run this script, the output it produces might be suppressed or hidden away in a console/terminal log.

You could replace the test for the single cut-off date test by a test against a range of date, if that suits your purpose better. But once more, you need to understand PHP!

I must mention standard warning that this script does an updating of your existing table without any option to rollback update. So, you use at your own risk, I can't be responsible if you overwrite data you intended to keep, you should always do what I too often forget, take a back-up first. I do actually have a second copy of my database tables, periodically refreshed from main tables, where I can test run my scripts.

A note about my testing
When I was testing this script, I initially made it update a test column. This gave me the ability to check the values my scripts generated against the values since 3.6.0 started placing values in that table. Consequently, the most recent rows showed both figures and I could confirm that they matched. Next, I modified my script so it would update the missing rows in the official Feels Like column(s) instead of the test column. If you have the necessary SQL skills, you could create a test column and change the SQL to update that, so you too can check my script works correctly.

(Before someone tells me, I am aware that (using pure SQL) a single query could work out MIN or MAX retrieving all rows in monthly table (or all rows for each day) BETWEEN rollovertime and just before next rollovertime, and (by group-by if needed) assign to a particular date. However, I realise that a) it is very easy to make a mistake with SQL, b) it is not possible to adjust from 9am to 10am rollover mid year in that approach, and c) not everyone can understand how SQL is expressed and works. If someone prefers the SQL I considered but rejected, then feel free to actually write out the SQL and share it.

Instead I have chosen to work row by row, and used PHP to determine min/max and when rollover occurs. (I am guessing more people will understand this PHP step-by-step approach than will understand the single SQL statement with more complicated clauses, but I may be wrong). Anyway, in my approach I process only one row in each iteration of my loop, I compare the value (Feels like by default, but you can change this) with existing minimum and maximum, updating as needed, and when I reach roll over time, I update the daily summary table and reset the minimum and maximum. I hope people find that easy to follow, even if their PHP knowledge is limited.
I hope some people do find these shared scripts useful. If you are less rusty than me in writing scripts, you might be able to improve on mine, and I would be grateful if you do. I have not put a copyright or other restriction on them. How you upload them to your web server, and/or choose to run them, will depend on the types of access you have to your web server. I cannot provide support on that.
You do not have the required permissions to view the files attached to this post.

Post Reply