Several additions to the website in the latest update (v1.12.0) has been made with several new graphs now available. Various other changes have been made and the seasonal weather summary for the summer of 2020 to 2021 has been published in Reviews.
The new graphs available are as the following:
- Website Analytics: 2 new graphs of cumulative number of visits and page views of all visitors (Cumulative Visits) and return visitors (Cumulative Return Visits)
- Historic Graphs: New graphs for annual thermal energy (shown under Thermal). This is shown for Growing Degree Days and Temperature Sum that utilises daily temperatures compared to base temperatures to track plant growth and seasonal progress
- Heatmap charts: This is a new page that shows our logged historical data for the past 10 years by hour and day across a given year for a selected metric
- Wind Rose charts: This is another new page that charts the distribution of wind speed and wind direction for various periods. The time periods include recent data and any year, month or all data during the last 10 years
To provide further information about how the new graphs have been added, that php scripts are used to run queries against the database and insert that data into JSON format arrays in a format that the Highcharts graphs can use. The link to the js file containing the Javascript code for the highcharts can be viewed from page source code, such as analyticsSQL.js for the Website Analytics graphs. From the Javascript code the code of the php scripts used as the data input can be accessed by appending a ?view=sce to the url.
Adding the new website analytics graphs as mentioned above came about because it is of interest to plot the month to month trend in the visitor numbers. From this it can clearly seen that the trend have been generally linear even though there are variations in individual months. The direct links to the php scripts are as per the following: Cumulative Visits and Cumulative Return Visits
Explaining the Growing Degree Days (GDD) in more detail this provides an easy to use simple indication of crop or plant development over a period of time as result of the accumulation of heat. Plant growth is often dependent on the amount of heat experienced over time. So to quantify the amount of thermal energy available over time, GDD is a cumulative total of daily temperatures that can give an indication of how the season/s is progressing for plant growth and provide a future indication to when a required amount of GDD would be reached.
Note that other factors are not included such time integrated warmth over each day, transpiration, soil moisture and rainfall. The daily GDD contribution is calculated as the difference of the daily average temperature (average of minimum and maximum temperatures) for each day from base temperatures. The daily GDD is set to zero if the average temperature is below the base temperature (not possible in our climate). Daily maximum temperatures over 30 C are capped at 30 C as many plants don’t grow as quickly in hotter weather.
The temperature sum is similar and provides an indication of the accumulative effect of the thermal energy available over a period of time based on the temperature. However that while the Growing Degree Days (GDD) provides an indication for plant growth, Temperature Sum provides a more general gauge on the progress of the total amount of coolth or warmth of the season/s. Similar to the GDD base temperatures are also employed.
The temperature for each day for the temperature sum is calculated as the difference of the daily average temperature (average of minimum and maximum temperatures) for each day from base temperatures. The daily temperatures used for sum that are below the base temperature do reduce the cumulative temperature sum, in contrast to this not affecting the cumulative temperature in GDDs.
The php scripts employed for the GDD and temperature sum are parameterised meaning that values are appended to the end of the url to control the output received in the query. This allows for the base temperatures and starting month to be defined as well as the capping of temperatures at 30 C in GDD or the metric used in the temperature sum. The header in the php script explains in more detail about the parameters the script accepts. The temperature sum is show for daily average, minimum and maximum temperatures (metric used is set in the url parameter) because there are differences between these that show the coolness and warmness of the season in different ways depending on the base temperatures chosen. The direct link to the php scripts is the following: Growing Degree Days and Temperature Sum.
And if of interest this is the main SQL query for the annual Growing Degree Days for each year as contained in the php script which uses both SQL and php variables.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
$query = 'SELECT '. ' UNIX_TIMESTAMP(LogDateCommon), '. ' Year,'. ' ROUND(annual_growing_degree_day_base_1,1) AS annual_growing_degree_day_base_1, '. ' ROUND(annual_growing_degree_day_base_2,1) AS annual_growing_degree_day_base_2 '. ' FROM ( '. ' WITH gdd AS ('. ' /* determine the GDD as the delta of avg temp - base temp */'. ' SELECT '. ' LogDate,'. ' CASE WHEN @startmonth = 1 THEN DATE_FORMAT(LogDate, "2000-%m-%d") '. ' WHEN MONTH(LogDate) >= @startmonth AND @startMonth = 2 THEN DATE_FORMAT(LogDate, "2000-%m-%d") '. ' WHEN MONTH(LogDate) < @startmonth AND @startMonth = 2 THEN DATE_FORMAT(LogDate, "2001-%m-%d") '. ' WHEN MONTH(LogDate) >= @startmonth THEN DATE_FORMAT(LogDate, "1999-%m-%d") '. ' ELSE DATE_FORMAT(LogDate, "2000-%m-%d") END AS LogDateCommon,' . ' CASE WHEN MONTH(LogDate) <= @startMonth - 1 THEN YEAR(LogDate) - 1 ELSE YEAR(LogDate) END AS year, '. ' CASE WHEN AvgTemp > @baseTemp1 THEN AvgTemp - @baseTemp1 ELSE 0 END AS growing_degree_day_base_1, '. ' CASE WHEN AvgTemp > @baseTemp1 THEN AvgTemp - @baseTemp2 ELSE 0 END AS growing_degree_day_base_2 '. ' FROM ( '. ' /* determine the avg temp for each day */'. ' SELECT LogDate, CASE WHEN MinTemp IS NOT NULL AND MaxTemp IS NOT NULL THEN (MinTemp + MaxTemp) / 2 ELSE 0 END AS avgTemp '. ' FROM ( '. ' /* cap max temps at 30 C? */'. ' SELECT LogDate, MinTemp, CASE WHEN MaxTemp > 30 AND @tempCapAt30C = TRUE THEN 30 ELSE MaxTemp END AS MaxTemp '. ' FROM Dayfile '. ' WHERE LogDate >= "2010-02-14" '. ' ) AS groupedData '. ' ) AS AvgTemp '. ' ) '. ' SELECT '. ' /* determine the annual cumulative GDD for each year */'. ' LogDate, '. ' LogDateCommon,'. ' year, '. ' SUM(growing_degree_day_base_1) OVER (PARTITION by year ORDER BY LogDateCommon) AS annual_growing_degree_day_base_1, '. ' SUM(growing_degree_day_base_2) OVER (PARTITION by year ORDER BY LogDateCommon) AS annual_growing_degree_day_base_2 '. ' FROM gdd '. ') AS annualGDD '. 'WHERE LogDate >= "' . $currYear . '-01-01" '. 'AND LogDate < "' . $currYear1 . '-01-01" ' |
And this is the main SQL query for annual temperature sum for each year as contained in the php script which has both SQL and php variables.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
$query = ' SELECT '. ' UNIX_TIMESTAMP(LogDateCommon) AS LogDate, '. ' LogYear, '. ' LogDate, '. ' ROUND(annual_temperature_sum_base_1,1) AS annual_temperature_sum_base_1, '. ' ROUND(annual_temperature_sum_base_2,1) AS annual_temperature_sum_base_2, '. ' ROUND(annual_temperature_sum_base_3,1) AS annual_temperature_sum_base_3 '. ' FROM ( '. ' WITH tempSum AS ( '. ' SELECT '. ' /* determine the temp sum as the avg tmp above the base value */'. ' CASE WHEN @startmonth = 1 THEN DATE_FORMAT(LogDate, "2000-%m-%d") '. ' WHEN MONTH(LogDate) >= @startmonth AND @startMonth = 2 THEN DATE_FORMAT(LogDate, "2000-%m-%d") '. ' WHEN MONTH(LogDate) < @startmonth AND @startMonth = 2 THEN DATE_FORMAT(LogDate, "2001-%m-%d") '. ' WHEN MONTH(LogDate) >= @startmonth THEN DATE_FORMAT(LogDate, "1999-%m-%d") '. ' ELSE DATE_FORMAT(LogDate, "2000-%m-%d") END AS LogDateCommon,'. ' CASE WHEN MONTH(LogDate) <= @startMonth - 1 THEN LogYear - 1 ELSE LogYear END AS LogYear, '. ' LogDate, '. ' temp - @baseTemp1 AS temperature_sum_base_1, '. ' temp - @baseTemp2 AS temperature_sum_base_2, '. ' temp - @baseTemp3 AS temperature_sum_base_3 '. ' FROM ( '. ' /* determine the avg temp for each day */ '. ' SELECT '. ' LogDate, '. ' YEAR(LogDate) AS LogYear, '. $valAggrString . ' AS temp '. ' FROM '. ' Dayfile '. ' WHERE LogDate >= "2010-02-14" '. ' ) AS temp '. ' ) '. ' SELECT '. ' /* determine the annual cumulative temp sum for each year */'. ' LogDateCommon, '. ' LogYear,'. ' LogDate,'. ' SUM(temperature_sum_base_1) OVER (PARTITION by LogYear ORDER BY LogDateCommon) AS annual_temperature_sum_base_1,'. ' SUM(temperature_sum_base_2) OVER (PARTITION by LogYear ORDER BY LogDateCommon) AS annual_temperature_sum_base_2,'. ' SUM(temperature_sum_base_3) OVER (PARTITION by LogYear ORDER BY LogDateCommon) AS annual_temperature_sum_base_3'. ' FROM tempSum '. ' ) AS annualtempSum '. ' WHERE LogDate >= "' . $currYear . '-01-01" '. ' AND LogDate < "' . $currYear1 . '-01-01" '; |
Let us know if you find this content interesting or if you have any questions or comments. I hope that you find the new data helpful or of interest.