Announcements – Ferny Grove Weather Blog

Solar and air quality data

Posted on 11 January 2021 | by Ferny Grove Weather | Leave a Comment

New data is now available on multiple pages to incorporate solar radiation, sunshine and air quality following the start of recording these parameters from 26 December 2020. These sensor were installed along with the change to using a Weatherlink Live receiving unit between 19 and 22 December 2020, which were then tested for compatibility with our processes and existing data.

The following pages have been updated with additional data:

The home page: the solar and air quality data have been added to the real time updates on the home page, sunshine/ night-time bands have been added to the real-time graph, the station forecast have been removed and a Bureau of Meteorology forecast added

Gauges: A gauge for solar radiation was added

Recent Graphs, Past Months Graphs and Historical Graphs: updated with new graphs containing Solar, Insolation, Sunshine and Air Quality data.

Today and Yesterday: Averages, Totals and Extremes for Solar, Insolation, Sunshine and Air Quality added

This Month and Year: Totals and Extremes for Solar, Insolation and Sunshine added

Annual Data Summary: Data for Sunshine and maximum solar radiation added

All-Time Records: Totals and Extremes for Solar, Insolation and Sunshine added

Top 10 Records: Solar and Sunshine records added

Recent Extremes Since: Maximum solar radiation and Sunshine hours added for today and yesterday

About: updated with new information regarding the additional sensors that have been installed and the awards by Weather Observations Website (WOW) for data submitted in 2020.

System Status: Reworked to align with the system changes and to provide additional system information now available with this change

Glossary: Updated with additional terms of Air Quality, Insolation, Solar Radiation and Sunshine.

A routine now runs at the start of each day to collate the air quality data into an additional daily database table used in the Historical graphs. The Realtime database table was altered to include air quality data. A monthly air quality database is also populated with data each 5 minutes.

A combination of our regular data updating on the website and these Realtime and Monthly database tables are used to update the tables and graphs on the website. The Realtime and Monthly database tables are also used as in the input to update the daily air quality database table.

Coming soon will be the addition of UV data. Also air quality data for the this month, this year and all-time records is also planned. Additional data will need to added to the daily database table in order to show that data along with the date and time of the occurrences.

Under progress is the calculation of daily potential evapotranspiration and possibly evaporation now that we are measuring solar radiation. Due to limitations with the system I am using it appears these will have to be calculated, rather than supplied by the weather station directly. These are rather complex multi-step calculations, so considerations are being made with the practicalities of that and what data will be shown from that on the website.

September 2020 weather review

Posted on 30 November 2020 | by Ferny Grove Weather | Leave a Comment

The review of the weather at Ferny Grove during September 2020 is now available.

During September 2020 overnight temperatures were above average and were the highest in 10 years. Similarly to August 2020, the month saw very few cool nights and many warm nights throughout the month. The 18th to 25th saw overnight temperatures peak at 9 ºC above average. Daytime temperatures were close to average and saw a mixture of above and below average temperatures.

Rainfall in September was below average but was close to the long term median, with a total of 14.2 mm (42.7% of the long term average). The long term rainfall for September is generally quite low, hence the close to median rainfall. While the rainfall total was quite low and continuation of the trend of low monthly rainfall totals, the year to date rainfall however remains above average at 9.8 % above the long term average and the highest since 2015. Low daily rainfall totals with light showers during some days during the month. A thunderstorm passed to the south on the 25th, as well as another thunderstorm to the north and brought a brief light shower.

Long period rainfall totals remained well below average during most periods while deteriorating further. 6 monthly rainfall fell significantly to 161.7 mm below average being more than 6 months since significant rainfall at the start of the year. 9 monthly rainfall was 79.4 mm above average while 12 to 48-month rainfall was between 72.4 mm below average for 12 month rainfall and 245.3 mm below average for 24 month rainfall.

The summary containing the key information can be found here.

The full report of more detailed analysis is available here.

Website analytics

Posted on 8 November 2020 | by Ferny Grove Weather | 1 Comment

A new page is now available that has been in development lately of a different nature to the existing content on the website. We use the privacy friendly open source website analytics platform Matomo that is hosted on-premises where we fully own the data that allows us to understand the usage and interest in this service we provide.

This website analytics data have been used to determine whether there is a correlation between the visits are made to the website compared to amount of the rainfall at the time and to test whether visitor activity is stronger during wetter weather. From this it have been discovered that at this time that while a lot of visits are made when there is no rainfall, when expressed in percentage terms that there is a general trend in more interest in the website during wetter weather, whilst visits across the range of rainfall totals are very consistent.

This information of visits by rainfall have been published on the new page as four charts using this information calculated from our databases. In addition to this, more standard summarised data over time of the number of visits, the type of devices used and the visits made by returning and frequently returning visitors. On that page are explanations of the definitions relating to this data and also there are key metric for various periods to provide a quick summary.

Thank you to all those who have shown an interest in this website and the many who have come back to this website many times. I have been quite surprised at the strong visitor activity that has occurred and there is clearly an interest on local real-time weather data. This website is in continued development with completed new works announced on this blog in addition to updates to the website that are mentioned on our Website Info page.

The below information is provided to share how this page is put together at this time for anyone who want to have more understanding of this. Both the analytics data and weather data are stored in MySQL databases, which are both used to create four database tables containing the data for the four graphs for the visits by rainfall. These calculations use table joins and appropriate aggregations to produce the data to allow for a fair comparison. But because these are quite complex calculations the database queries are not executed when a graph is viewed as retrieving the current data will add significant time to load the graph.

So given that this data doesn’t change too quickly, a SQL procedure is executed once a month to update the data for when someone visits the page. The SQL code used in that procedure is similar to the below example. The methodology used is that a table is created to calculate the amount of rainfall in the previous 24 hours for each hour during the past year, of which this data is not readily available in the database. This is derived by first calculating the rain for each hour, then the cumulative rainfall during the last year for each hour and then calculate the amount of rainfall for each hour compared to the same time in the previous day.

Once we have that data then table joins are used to merge the analytics data to this rainfall data for each of the four tables. Finally at the end the rainfall data is deleted, as the data is no longer needed.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `CreateTbls_recent_rainfall_by_visits`()

BEGIN 
DECLARE exit handler for SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
@errno = MYSQL_ERRNO,
@text = MESSAGE_TEXT;
SET
  @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
SELECT
  @full_error;
END;
/* create table of rainfall in past 24 hours for each hour for past year - used for each of the following 4 create tables */
DROP TABLE IF EXISTS last_24_hrs_rain_past_year;
CREATE TABLE last_24_hrs_rain_past_year (
  updated_time DATETIME NOT NULL,
  to_date DATETIME NOT NULL,
  from_date DATETIME NOT NULL,
  rain_in_last_24_hours FLOAT(5, 1) NULL,
  cumulative_rain FLOAT(5, 1) NULL,
  PRIMARY KEY (to_date)
)
SELECT
  DATE_ADD(UTC_TIMESTAMP(), INTERVAL 10 HOUR) AS updated_time,
  ROUND(HourlyCumulativeRain_a.cumulative_rain, 1) AS cumulative_rain,
  HourlyCumulativeRain_a.date_hour AS to_date,
  HourlyCumulativeRain_b.date_hour AS from_date,
  ROUND(
    (
      HourlyCumulativeRain_a.cumulative_rain - HourlyCumulativeRain_b.cumulative_rain
    ),
    1
  ) AS rain_in_last_24_hours
FROM
  (
   /* Query to return the cumulative rainfall for each hour for the past year plus 1 day and for the TO_DATE field of the outer query */
    SELECT
      hour AS date_hour,
      ROUND((@CumlativeRain: = @CumlativeRain + rain_hour), 1) as cumulative_rain
    FROM
      (
        SELECT
          hour,
          SUM(rain_since_midnight_hour) as rain_hour
        FROM
          (
            SELECT
              LogDateTime AS hour,
              IF(
                RainSinceMidnight > 0,
                RainSinceMidnight - @lastValue,
                0
              ) AS rain_since_midnight_hour,
              @lastValue: = RainSinceMidnight,
              @CumlativeRain: = 0
            FROM
              weatherdata.Monthly m,
              (
                SELECT
                  @lastValue: = 0
              ) SQLVars
            WHERE
              LogDateTime >= (CURDATE() - INTERVAL 1 YEAR - INTERVAL 1 DAY)
            GROUP BY
              YEAR(m.LogDateTime),
              MONTH(m.LogDateTime),
              DAY(m.LogDateTime),
              HOUR(m.LogDateTime),
              MINUTE(m.LogDateTime)
            ORDER BY
              (LogDateTime)
          ) as m1
        GROUP BY
          YEAR(hour),
          MONTH(hour),
          DAY(hour),
          HOUR(hour)
      ) as m2
    GROUP BY
      hour
    ORDER BY
      hour
  ) AS HourlyCumulativeRain_a
  LEFT JOIN (
   /* Query to return the cumulative rainfall for each hour for the past year plus 1 day for the FROM_DATE field of the outer query */
    SELECT
      hour AS date_hour,
      ROUND((@CumlativeRain: = @CumlativeRain + rain_hour), 1) as cumulative_rain
    FROM
      (
        SELECT
          hour,
          SUM(rain_since_midnight_hour) as rain_hour
        FROM
          (
            SELECT
              LogDateTime AS hour,
              IF(
                RainSinceMidnight > 0,
                RainSinceMidnight - @lastValue,
                0
              ) AS rain_since_midnight_hour,
              @lastValue: = RainSinceMidnight,
              @CumlativeRain: = 0
            FROM
              weatherdata.Monthly m,
              (
                SELECT
                  @lastValue: = 0
              ) SQLVars
            WHERE
              LogDateTime >= (CURDATE() - INTERVAL 1 YEAR - INTERVAL 1 DAY)
            GROUP BY
              YEAR(m.LogDateTime),
              MONTH(m.LogDateTime),
              DAY(m.LogDateTime),
              HOUR(m.LogDateTime),
              MINUTE(m.LogDateTime)
            ORDER BY
              (LogDateTime)
          ) as m1
        GROUP BY
          YEAR(hour),
          MONTH(hour),
          DAY(hour),
          HOUR(hour)
      ) as m2
    GROUP BY
      hour
    ORDER BY
      hour
  ) AS HourlyCumulativeRain_b ON /* Join the inner queries to find the rainfall in the previous 24 hours for each hour */
  HourlyCumulativeRain_a.date_hour - INTERVAL 1 DAY = HourlyCumulativeRain_b.date_hour
WHERE
  HourlyCumulativeRain_b.cumulative_rain IS NOT NULL;
/* end of create table */

  -- query1 to create table -average daily rain by number of visitors
  DROP TABLE IF EXISTS avg_daily_rain_by_visits;
CREATE TABLE avg_daily_rain_by_visits (
    updated_time DATETIME NOT NULL,
    visit_time_count SMALLINT NOT NULL,
    visit_total SMALLINT NOT NULL,
    rain_in_last_24_hours SMALLINT NULL,
    PRIMARY KEY (rain_in_last_24_hours)
  )
SELECT
  updated_time,
  COUNT(visit_time_hour) AS visit_time_count,
  SUM(visit_count) AS visit_total,
  rain_in_last_24_hours
FROM
  (
    SELECT
      last_24_hrs_rain_past_year.updated_time,
      DATE_ADD(
        DATE_FORMAT(
          matomo.mtys_log_link_visit_action.server_time,
          "%Y-%m-%d %H"
        ),
        INTERVAL 10 HOUR
      ) AS visit_time_hour,
      COUNT(
        DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)
      ) AS visit_count,
      ROUND(
        AVG(last_24_hrs_rain_past_year.rain_in_last_24_hours),
        0
      ) AS rain_in_last_24_hours
    FROM
      matomo.mtys_log_link_visit_action
      JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(
        DATE_ADD(
          matomo.mtys_log_link_visit_action.server_time,
          INTERVAL 10 HOUR
        )
      ) = YEAR(last_24_hrs_rain_past_year.to_date)
      AND MONTH(
        DATE_ADD(
          matomo.mtys_log_link_visit_action.server_time,
          INTERVAL 10 HOUR
        )
      ) = MONTH(last_24_hrs_rain_past_year.to_date)
      AND DAY(
        DATE_ADD(
          matomo.mtys_log_link_visit_action.server_time,
          INTERVAL 10 HOUR
        )
      ) = DAY(last_24_hrs_rain_past_year.to_date)
      AND HOUR(
        DATE_ADD(
          matomo.mtys_log_link_visit_action.server_time,
          INTERVAL 10 HOUR
        )
      ) = HOUR(last_24_hrs_rain_past_year.to_date)
    WHERE
      matomo.mtys_log_link_visit_action.server_time >= (
        weatherdata.last_24_hrs_rain_past_year.updated_time - INTERVAL 1 YEAR
      )
      AND last_24_hrs_rain_past_year.rain_in_last_24_hours <> 0
    GROUP BY
      YEAR(matomo.mtys_log_link_visit_action.server_time),
      MONTH(matomo.mtys_log_link_visit_action.server_time),
      DAY(matomo.mtys_log_link_visit_action.server_time)
  ) AS Data
GROUP BY
  rain_in_last_24_hours;

/* query2 to create table - average hourly rain by number of visitors */
  DROP TABLE IF EXISTS avg_hourly_rain_by_visits;
CREATE TABLE avg_hourly_rain_by_visits (
    updated_time DATETIME NOT NULL,
    visit_time_count SMALLINT NOT NULL,
    visit_total SMALLINT NOT NULL,
    rain_in_last_24_hours SMALLINT NULL,
    PRIMARY KEY (rain_in_last_24_hours)
  )
SELECT
  updated_time,
  COUNT(visit_time_hour) AS visit_time_count,
  SUM(visit_count) AS visit_total,
  rain_in_last_24_hours
FROM
  (
    SELECT
      last_24_hrs_rain_past_year.updated_time,
      DATE_ADD(
        DATE_FORMAT(
          matomo.mtys_log_link_visit_action.server_time,
          "%Y-%m-%d %H"
        ),
        INTERVAL 10 HOUR
      ) AS visit_time_hour,
      COUNT(
        DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)
      ) AS visit_count,
      ROUND(
        AVG(last_24_hrs_rain_past_year.rain_in_last_24_hours),
        0
      ) AS rain_in_last_24_hours
    FROM
      matomo.mtys_log_link_visit_action
      JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(
        DATE_ADD(
          matomo.mtys_log_link_visit_action.server_time,
          INTERVAL 10 HOUR
        )
      ) = YEAR(last_24_hrs_rain_past_year.to_date)
      AND MONTH(
        DATE_ADD(
          matomo.mtys_log_link_visit_action.server_time,
          INTERVAL 10 HOUR
        )
      ) = MONTH(last_24_hrs_rain_past_year.to_date)
      AND DAY(
        DATE_ADD(
          matomo.mtys_log_link_visit_action.server_time,
          INTERVAL 10 HOUR
        )
      ) = DAY(last_24_hrs_rain_past_year.to_date)
      AND HOUR(
        DATE_ADD(
          matomo.mtys_log_link_visit_action.server_time,
          INTERVAL 10 HOUR
        )
      ) = HOUR(last_24_hrs_rain_past_year.to_date)
    WHERE
      matomo.mtys_log_link_visit_action.server_time >= (
        weatherdata.last_24_hrs_rain_past_year.updated_time - INTERVAL 1 YEAR
      )
      AND last_24_hrs_rain_past_year.rain_in_last_24_hours <> 0
    GROUP BY
      YEAR(matomo.mtys_log_link_visit_action.server_time),
      MONTH(matomo.mtys_log_link_visit_action.server_time),
      DAY(matomo.mtys_log_link_visit_action.server_time),
      HOUR(matomo.mtys_log_link_visit_action.server_time)
  ) AS Data
GROUP BY
  rain_in_last_24_hours;

/* query3 to create table - grouped hourly rainfall by visitors */
  DROP TABLE IF EXISTS grouped_hourly_rain_by_visits;
CREATE TABLE grouped_hourly_rain_by_visits (
    updated_time DATETIME NOT NULL,
    rain_in_last_24_hours_grouped VARCHAR(10),
    visit_time_count SMALLINT NOT NULL,
    visitor_total SMALLINT NOT NULL,
    visitor_percent FLOAT(5, 4) NULL,
    PRIMARY KEY (rain_in_last_24_hours_grouped)
  )
SELECT
  updated_time,
  rain_in_last_24_hours_grouped,
  visit_time_count,
  visitor_total,
  visitor_total / total_visitors.visitor_grand_total AS visitor_percent
FROM
  /* take the visitor and recent rainfall values and bin into the standard rainfall groups */
  (
    SELECT
      updated_time,
      COUNT(visit_time_hour) AS visit_time_count,
      SUM(visitor_count) AS visitor_total,
      CASE
        WHEN rain_in_last_24_hours = 0 THEN 0
        WHEN rain_in_last_24_hours = 0.2 THEN 0.2
        WHEN rain_in_last_24_hours BETWEEN 0.2
        AND 1 THEN '0.4 to <1'
        WHEN rain_in_last_24_hours BETWEEN 1
        AND 5 THEN '1 to <5'
        WHEN rain_in_last_24_hours BETWEEN 5
        AND 10 THEN '5 to <10'
        WHEN rain_in_last_24_hours BETWEEN 10
        AND 20 THEN '10 to <20'
        WHEN rain_in_last_24_hours BETWEEN 20
        AND 50 THEN '20 to <50'
        WHEN rain_in_last_24_hours BETWEEN 50
        AND 100 THEN '50 to <100'
        WHEN rain_in_last_24_hours >= 100 THEN '>=100'
        ELSE ROUND(rain_in_last_24_hours, 0)
      END AS rain_in_last_24_hours_grouped,
      rain_in_last_24_hours
    FROM
      (
        /* Inner query to join visitor information to rainfall in the preceding 24 hours for the past year. */
        SELECT
          weatherdata.last_24_hrs_rain_past_year.updated_time AS updated_time,
          DATE_ADD(
            DATE_FORMAT(
              matomo.mtys_log_link_visit_action.server_time,
              "%Y-%m-%d %H"
            ),
            INTERVAL 10 HOUR
          ) AS visit_time_hour,
          COUNT(
            DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)
          ) AS visitor_count,
          last_24_hrs_rain_past_year.rain_in_last_24_hours
        FROM
          matomo.mtys_log_link_visit_action
          LEFT JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = YEAR(last_24_hrs_rain_past_year.to_date)
          AND MONTH(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = MONTH(last_24_hrs_rain_past_year.to_date)
          AND DAY(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = DAY(last_24_hrs_rain_past_year.to_date)
          AND HOUR(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = HOUR(last_24_hrs_rain_past_year.to_date)
        WHERE
          matomo.mtys_log_link_visit_action.server_time >= (
            weatherdata.last_24_hrs_rain_past_year.updated_time - INTERVAL 1 YEAR
          )
        GROUP BY
          YEAR(matomo.mtys_log_link_visit_action.server_time),
          MONTH(matomo.mtys_log_link_visit_action.server_time),
          DAY(matomo.mtys_log_link_visit_action.server_time),
          HOUR(matomo.mtys_log_link_visit_action.server_time)
      ) AS Data1
    GROUP BY
      rain_in_last_24_hours_grouped
  ) AS Data2 /* Cross Join query to find the total number of visits. This is used in the outer SELECT to calculate the % distribution of visits by grouped rainfall amounts */
  CROSS JOIN (
    SELECT
      SUM(visitor_count) AS visitor_grand_total
    FROM
      (
        SELECT
          COUNT(
            DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)
          ) AS visitor_count
        FROM
          matomo.mtys_log_link_visit_action
          LEFT JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = YEAR(last_24_hrs_rain_past_year.to_date)
          AND MONTH(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = MONTH(last_24_hrs_rain_past_year.to_date)
          AND DAY(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = DAY(last_24_hrs_rain_past_year.to_date)
          AND HOUR(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = HOUR(last_24_hrs_rain_past_year.to_date)
        WHERE
          matomo.mtys_log_link_visit_action.server_time >= (
            weatherdata.last_24_hrs_rain_past_year.updated_time - INTERVAL 1 YEAR
          )
        GROUP BY
          YEAR(matomo.mtys_log_link_visit_action.server_time),
          MONTH(matomo.mtys_log_link_visit_action.server_time),
          DAY(matomo.mtys_log_link_visit_action.server_time),
          HOUR(matomo.mtys_log_link_visit_action.server_time)
      ) AS Data
  ) as total_visitors
GROUP BY
  rain_in_last_24_hours_grouped
ORDER BY
  rain_in_last_24_hours;

/* query4 to create table - visitor engagement by grouped daily rainfall */
  /* Query to return the % visitors engagement by rainfall amount joining weatherdata to analytics data */
  DROP TABLE IF EXISTS visit_activity_by_grouped_daily_rain;
CREATE TABLE visit_activity_by_grouped_daily_rain (
    updated_time DATETIME NOT NULL,
    rain_in_last_24_hours_grouped VARCHAR(10) NOT NULL,
    rain_in_last_24_hours_count SMALLINT NOT NULL,
    visit_time_count SMALLINT NULL,
    visitor_total SMALLINT NOT NULL,
    visitor_percent_of_grouped_rain FLOAT(5, 4) NOT NULL,
    PRIMARY KEY (rain_in_last_24_hours_grouped)
  )
SELECT
  updated_time,
  rain_in_last_24_hours_grouped,
  rain_in_last_24_hours_count,
  visit_time_count,
  visitor_total,
  ROUND(visit_time_count / rain_in_last_24_hours_count, 3) AS visitor_percent_of_grouped_rain
FROM
  /* take the visitor and recent rainfall values and bin into the standard rainfall groups */
  (
    SELECT
      updated_time,
      COUNT(rain_time_hour) AS rain_in_last_24_hours_count,
      COUNT(visit_time_hour) AS visit_time_count,
      SUM(visitor_count) AS visitor_total,
      IF(
        rain_in_last_24_hours = 0,
        0,
        IF(
          rain_in_last_24_hours = 0.2,
          0.2,
          IF(
            rain_in_last_24_hours BETWEEN 0.2
            AND 1,
            '0.4 to <1',
            IF(
              rain_in_last_24_hours BETWEEN 1
              AND 5,
              '1 to <5',
              IF(
                rain_in_last_24_hours BETWEEN 5
                AND 10,
                '5 to <10',
                IF(
                  rain_in_last_24_hours BETWEEN 10
                  AND 20,
                  '10 to <20',
                  IF(
                    rain_in_last_24_hours BETWEEN 20
                    AND 50,
                    '20 to <50',
                    IF(
                      rain_in_last_24_hours BETWEEN 50
                      AND 100,
                      '50 to <100',
                      IF(
                        rain_in_last_24_hours BETWEEN 100
                        AND 1000,
                        '>100',
                        ROUND(rain_in_last_24_hours, 0)
                      )
                    )
                  )
                )
              )
            )
          )
        )
      ) as rain_in_last_24_hours_grouped,
      rain_in_last_24_hours
    FROM
      (
        /* Inner query to join visitor information to average rainfall grouped by day by rainfall in the preceding 24 hours for the past year */
        SELECT
          last_24_hrs_rain_past_year.updated_time AS updated_time,
          DATE_ADD(
            DATE_FORMAT(
              last_24_hrs_rain_past_year.to_date,
              "%Y-%m-%d %H"
            ),
            INTERVAL 10 HOUR
          ) AS rain_time_hour,
          DATE_ADD(
            DATE_FORMAT(
              matomo.mtys_log_link_visit_action.server_time,
              "%Y-%m-%d %H"
            ),
            INTERVAL 10 HOUR
          ) AS visit_time_hour,
          CASE
            WHEN COUNT(
              DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)
            ) >= 1 THEN COUNT(
              DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)
            )
            ELSE 0
          END AS visitor_count,
          AVG(
            weatherdata.last_24_hrs_rain_past_year.rain_in_last_24_hours
          ) AS rain_in_last_24_hours
        FROM
          matomo.mtys_log_link_visit_action
          RIGHT JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = YEAR(last_24_hrs_rain_past_year.to_date)
          AND MONTH(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = MONTH(last_24_hrs_rain_past_year.to_date)
          AND DAY(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = DAY(last_24_hrs_rain_past_year.to_date)
          AND HOUR(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = HOUR(last_24_hrs_rain_past_year.to_date)
        WHERE
          last_24_hrs_rain_past_year.to_date >= (CURDATE() - INTERVAL 1 YEAR)
        GROUP BY
          YEAR(last_24_hrs_rain_past_year.to_date),
          MONTH(last_24_hrs_rain_past_year.to_date),
          DAY(last_24_hrs_rain_past_year.to_date)
      ) AS Data
    GROUP BY
      rain_in_last_24_hours_grouped
  ) AS DATA
GROUP BY
  rain_in_last_24_hours_grouped
ORDER BY
  rain_in_last_24_hours;

/* finally delete the rainfall table initially created as we don't need that data now */
  DROP TABLE IF EXISTS last_24_hrs_rain_past_year;
END$$
DELIMITER ;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `CreateTbls_recent_rainfall_by_visits`()

BEGIN

DECLARE exit handler for SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,

@errno = MYSQL_ERRNO,

@text = MESSAGE_TEXT;

SET

@full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);

SELECT

@full_error;

END;

/* create table of rainfall in past 24 hours for each hour for past year - used for each of the following 4 create tables */

DROP TABLE IF EXISTS last_24_hrs_rain_past_year;

CREATE TABLE last_24_hrs_rain_past_year (

updated_time DATETIME NOT NULL,

to_date DATETIME NOT NULL,

from_date DATETIME NOT NULL,

rain_in_last_24_hours FLOAT(5, 1) NULL,

cumulative_rain FLOAT(5, 1) NULL,

PRIMARY KEY (to_date)

)

SELECT

DATE_ADD(UTC_TIMESTAMP(), INTERVAL 10 HOUR) AS updated_time,

ROUND(HourlyCumulativeRain_a.cumulative_rain, 1) AS cumulative_rain,

HourlyCumulativeRain_a.date_hour AS to_date,

HourlyCumulativeRain_b.date_hour AS from_date,

ROUND(

(

HourlyCumulativeRain_a.cumulative_rain - HourlyCumulativeRain_b.cumulative_rain

) AS rain_in_last_24_hours

FROM

(

/* Query to return the cumulative rainfall for each hour for the past year plus 1 day and for the TO_DATE field of the outer query */

SELECT

hour AS date_hour,

ROUND((@CumlativeRain: = @CumlativeRain + rain_hour), 1) as cumulative_rain

FROM

(

SELECT

hour,

SUM(rain_since_midnight_hour) as rain_hour

FROM

(

SELECT

LogDateTime AS hour,

IF(

RainSinceMidnight > 0,

RainSinceMidnight - @lastValue,

) AS rain_since_midnight_hour,

@lastValue: = RainSinceMidnight,

@CumlativeRain: = 0

FROM

weatherdata.Monthly m,

(

SELECT

@lastValue: = 0

) SQLVars

WHERE

LogDateTime >= (CURDATE() - INTERVAL 1 YEAR - INTERVAL 1 DAY)

GROUP BY

YEAR(m.LogDateTime),

MONTH(m.LogDateTime),

DAY(m.LogDateTime),

HOUR(m.LogDateTime),

MINUTE(m.LogDateTime)

ORDER BY

(LogDateTime)

) as m1

GROUP BY

YEAR(hour),

MONTH(hour),

DAY(hour),

HOUR(hour)

) as m2

GROUP BY

hour

ORDER BY

hour

) AS HourlyCumulativeRain_a

LEFT JOIN (

/* Query to return the cumulative rainfall for each hour for the past year plus 1 day for the FROM_DATE field of the outer query */

SELECT

hour AS date_hour,

ROUND((@CumlativeRain: = @CumlativeRain + rain_hour), 1) as cumulative_rain

FROM

(

SELECT

hour,

SUM(rain_since_midnight_hour) as rain_hour

FROM

(

SELECT

LogDateTime AS hour,

IF(

RainSinceMidnight > 0,

RainSinceMidnight - @lastValue,

) AS rain_since_midnight_hour,

@lastValue: = RainSinceMidnight,

@CumlativeRain: = 0

FROM

weatherdata.Monthly m,

(

SELECT

@lastValue: = 0

) SQLVars

WHERE

LogDateTime >= (CURDATE() - INTERVAL 1 YEAR - INTERVAL 1 DAY)

GROUP BY

YEAR(m.LogDateTime),

MONTH(m.LogDateTime),

DAY(m.LogDateTime),

HOUR(m.LogDateTime),

MINUTE(m.LogDateTime)

ORDER BY

(LogDateTime)

) as m1

GROUP BY

YEAR(hour),

MONTH(hour),

DAY(hour),

HOUR(hour)

) as m2

GROUP BY

hour

ORDER BY

hour

) AS HourlyCumulativeRain_b ON /* Join the inner queries to find the rainfall in the previous 24 hours for each hour */

HourlyCumulativeRain_a.date_hour - INTERVAL 1 DAY = HourlyCumulativeRain_b.date_hour

WHERE

HourlyCumulativeRain_b.cumulative_rain IS NOT NULL;

/* end of create table */

-- query1 to create table -average daily rain by number of visitors

DROP TABLE IF EXISTS avg_daily_rain_by_visits;

CREATE TABLE avg_daily_rain_by_visits (

updated_time DATETIME NOT NULL,

visit_time_count SMALLINT NOT NULL,

visit_total SMALLINT NOT NULL,

rain_in_last_24_hours SMALLINT NULL,

PRIMARY KEY (rain_in_last_24_hours)

)

SELECT

updated_time,

COUNT(visit_time_hour) AS visit_time_count,

SUM(visit_count) AS visit_total,

rain_in_last_24_hours

FROM

(

SELECT

last_24_hrs_rain_past_year.updated_time,

DATE_ADD(

DATE_FORMAT(

matomo.mtys_log_link_visit_action.server_time,

"%Y-%m-%d %H"

INTERVAL 10 HOUR

) AS visit_time_hour,

COUNT(

DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)

) AS visit_count,

ROUND(

AVG(last_24_hrs_rain_past_year.rain_in_last_24_hours),

) AS rain_in_last_24_hours

FROM

matomo.mtys_log_link_visit_action

JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = YEAR(last_24_hrs_rain_past_year.to_date)

AND MONTH(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = MONTH(last_24_hrs_rain_past_year.to_date)

AND DAY(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = DAY(last_24_hrs_rain_past_year.to_date)

AND HOUR(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = HOUR(last_24_hrs_rain_past_year.to_date)

WHERE

matomo.mtys_log_link_visit_action.server_time >= (

weatherdata.last_24_hrs_rain_past_year.updated_time - INTERVAL 1 YEAR

)

AND last_24_hrs_rain_past_year.rain_in_last_24_hours <> 0

GROUP BY

YEAR(matomo.mtys_log_link_visit_action.server_time),

MONTH(matomo.mtys_log_link_visit_action.server_time),

DAY(matomo.mtys_log_link_visit_action.server_time)

) AS Data

GROUP BY

rain_in_last_24_hours;

/* query2 to create table - average hourly rain by number of visitors */

DROP TABLE IF EXISTS avg_hourly_rain_by_visits;

CREATE TABLE avg_hourly_rain_by_visits (

updated_time DATETIME NOT NULL,

visit_time_count SMALLINT NOT NULL,

visit_total SMALLINT NOT NULL,

rain_in_last_24_hours SMALLINT NULL,

PRIMARY KEY (rain_in_last_24_hours)

)

SELECT

updated_time,

COUNT(visit_time_hour) AS visit_time_count,

SUM(visit_count) AS visit_total,

rain_in_last_24_hours

FROM

(

SELECT

last_24_hrs_rain_past_year.updated_time,

DATE_ADD(

DATE_FORMAT(

matomo.mtys_log_link_visit_action.server_time,

"%Y-%m-%d %H"

INTERVAL 10 HOUR

) AS visit_time_hour,

COUNT(

DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)

) AS visit_count,

ROUND(

AVG(last_24_hrs_rain_past_year.rain_in_last_24_hours),

) AS rain_in_last_24_hours

FROM

matomo.mtys_log_link_visit_action

JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = YEAR(last_24_hrs_rain_past_year.to_date)

AND MONTH(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = MONTH(last_24_hrs_rain_past_year.to_date)

AND DAY(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = DAY(last_24_hrs_rain_past_year.to_date)

AND HOUR(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = HOUR(last_24_hrs_rain_past_year.to_date)

WHERE

matomo.mtys_log_link_visit_action.server_time >= (

weatherdata.last_24_hrs_rain_past_year.updated_time - INTERVAL 1 YEAR

)

AND last_24_hrs_rain_past_year.rain_in_last_24_hours <> 0

GROUP BY

YEAR(matomo.mtys_log_link_visit_action.server_time),

MONTH(matomo.mtys_log_link_visit_action.server_time),

DAY(matomo.mtys_log_link_visit_action.server_time),

HOUR(matomo.mtys_log_link_visit_action.server_time)

) AS Data

GROUP BY

rain_in_last_24_hours;

/* query3 to create table - grouped hourly rainfall by visitors */

DROP TABLE IF EXISTS grouped_hourly_rain_by_visits;

CREATE TABLE grouped_hourly_rain_by_visits (

updated_time DATETIME NOT NULL,

rain_in_last_24_hours_grouped VARCHAR(10),

visit_time_count SMALLINT NOT NULL,

visitor_total SMALLINT NOT NULL,

visitor_percent FLOAT(5, 4) NULL,

PRIMARY KEY (rain_in_last_24_hours_grouped)

)

SELECT

updated_time,

rain_in_last_24_hours_grouped,

visit_time_count,

visitor_total,

visitor_total / total_visitors.visitor_grand_total AS visitor_percent

FROM

/* take the visitor and recent rainfall values and bin into the standard rainfall groups */

(

SELECT

updated_time,

COUNT(visit_time_hour) AS visit_time_count,

SUM(visitor_count) AS visitor_total,

CASE

WHEN rain_in_last_24_hours = 0 THEN 0

WHEN rain_in_last_24_hours = 0.2 THEN 0.2

WHEN rain_in_last_24_hours BETWEEN 0.2

AND 1 THEN '0.4 to <1'

WHEN rain_in_last_24_hours BETWEEN 1

AND 5 THEN '1 to <5'

WHEN rain_in_last_24_hours BETWEEN 5

AND 10 THEN '5 to <10'

WHEN rain_in_last_24_hours BETWEEN 10

AND 20 THEN '10 to <20'

WHEN rain_in_last_24_hours BETWEEN 20

AND 50 THEN '20 to <50'

WHEN rain_in_last_24_hours BETWEEN 50

AND 100 THEN '50 to <100'

WHEN rain_in_last_24_hours >= 100 THEN '>=100'

ELSE ROUND(rain_in_last_24_hours, 0)

END AS rain_in_last_24_hours_grouped,

rain_in_last_24_hours

FROM

(

/* Inner query to join visitor information to rainfall in the preceding 24 hours for the past year. */

SELECT

weatherdata.last_24_hrs_rain_past_year.updated_time AS updated_time,

DATE_ADD(

DATE_FORMAT(

matomo.mtys_log_link_visit_action.server_time,

"%Y-%m-%d %H"

INTERVAL 10 HOUR

) AS visit_time_hour,

COUNT(

DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)

) AS visitor_count,

last_24_hrs_rain_past_year.rain_in_last_24_hours

FROM

matomo.mtys_log_link_visit_action

LEFT JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = YEAR(last_24_hrs_rain_past_year.to_date)

AND MONTH(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = MONTH(last_24_hrs_rain_past_year.to_date)

AND DAY(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = DAY(last_24_hrs_rain_past_year.to_date)

AND HOUR(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = HOUR(last_24_hrs_rain_past_year.to_date)

WHERE

matomo.mtys_log_link_visit_action.server_time >= (

weatherdata.last_24_hrs_rain_past_year.updated_time - INTERVAL 1 YEAR

)

GROUP BY

YEAR(matomo.mtys_log_link_visit_action.server_time),

MONTH(matomo.mtys_log_link_visit_action.server_time),

DAY(matomo.mtys_log_link_visit_action.server_time),

HOUR(matomo.mtys_log_link_visit_action.server_time)

) AS Data1

GROUP BY

rain_in_last_24_hours_grouped

) AS Data2 /* Cross Join query to find the total number of visits. This is used in the outer SELECT to calculate the % distribution of visits by grouped rainfall amounts */

CROSS JOIN (

SELECT

SUM(visitor_count) AS visitor_grand_total

FROM

(

SELECT

COUNT(

DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)

) AS visitor_count

FROM

matomo.mtys_log_link_visit_action

LEFT JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = YEAR(last_24_hrs_rain_past_year.to_date)

AND MONTH(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = MONTH(last_24_hrs_rain_past_year.to_date)

AND DAY(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = DAY(last_24_hrs_rain_past_year.to_date)

AND HOUR(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = HOUR(last_24_hrs_rain_past_year.to_date)

WHERE

matomo.mtys_log_link_visit_action.server_time >= (

weatherdata.last_24_hrs_rain_past_year.updated_time - INTERVAL 1 YEAR

)

GROUP BY

YEAR(matomo.mtys_log_link_visit_action.server_time),

MONTH(matomo.mtys_log_link_visit_action.server_time),

DAY(matomo.mtys_log_link_visit_action.server_time),

HOUR(matomo.mtys_log_link_visit_action.server_time)

) AS Data

) as total_visitors

GROUP BY

rain_in_last_24_hours_grouped

ORDER BY

rain_in_last_24_hours;

/* query4 to create table - visitor engagement by grouped daily rainfall */

/* Query to return the % visitors engagement by rainfall amount joining weatherdata to analytics data */

DROP TABLE IF EXISTS visit_activity_by_grouped_daily_rain;

CREATE TABLE visit_activity_by_grouped_daily_rain (

updated_time DATETIME NOT NULL,

rain_in_last_24_hours_grouped VARCHAR(10) NOT NULL,

rain_in_last_24_hours_count SMALLINT NOT NULL,

visit_time_count SMALLINT NULL,

visitor_total SMALLINT NOT NULL,

visitor_percent_of_grouped_rain FLOAT(5, 4) NOT NULL,

PRIMARY KEY (rain_in_last_24_hours_grouped)

)

SELECT

updated_time,

rain_in_last_24_hours_grouped,

rain_in_last_24_hours_count,

visit_time_count,

visitor_total,

ROUND(visit_time_count / rain_in_last_24_hours_count, 3) AS visitor_percent_of_grouped_rain

FROM

/* take the visitor and recent rainfall values and bin into the standard rainfall groups */

(

SELECT

updated_time,

COUNT(rain_time_hour) AS rain_in_last_24_hours_count,

COUNT(visit_time_hour) AS visit_time_count,

SUM(visitor_count) AS visitor_total,

IF(

rain_in_last_24_hours = 0,

IF(

rain_in_last_24_hours = 0.2,

0.2,

IF(

rain_in_last_24_hours BETWEEN 0.2

AND 1,

'0.4 to <1',

IF(

rain_in_last_24_hours BETWEEN 1

AND 5,

'1 to <5',

IF(

rain_in_last_24_hours BETWEEN 5

AND 10,

'5 to <10',

IF(

rain_in_last_24_hours BETWEEN 10

AND 20,

'10 to <20',

IF(

rain_in_last_24_hours BETWEEN 20

AND 50,

'20 to <50',

IF(

rain_in_last_24_hours BETWEEN 50

AND 100,

'50 to <100',

IF(

rain_in_last_24_hours BETWEEN 100

AND 1000,

'>100',

ROUND(rain_in_last_24_hours, 0)

)

) as rain_in_last_24_hours_grouped,

rain_in_last_24_hours

FROM

(

/* Inner query to join visitor information to average rainfall grouped by day by rainfall in the preceding 24 hours for the past year */

SELECT

last_24_hrs_rain_past_year.updated_time AS updated_time,

DATE_ADD(

DATE_FORMAT(

last_24_hrs_rain_past_year.to_date,

"%Y-%m-%d %H"

INTERVAL 10 HOUR

) AS rain_time_hour,

DATE_ADD(

DATE_FORMAT(

matomo.mtys_log_link_visit_action.server_time,

"%Y-%m-%d %H"

INTERVAL 10 HOUR

) AS visit_time_hour,

CASE

WHEN COUNT(

DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)

) >= 1 THEN COUNT(

DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)

)

ELSE 0

END AS visitor_count,

AVG(

weatherdata.last_24_hrs_rain_past_year.rain_in_last_24_hours

) AS rain_in_last_24_hours

FROM

matomo.mtys_log_link_visit_action

RIGHT JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = YEAR(last_24_hrs_rain_past_year.to_date)

AND MONTH(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = MONTH(last_24_hrs_rain_past_year.to_date)

AND DAY(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = DAY(last_24_hrs_rain_past_year.to_date)

AND HOUR(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = HOUR(last_24_hrs_rain_past_year.to_date)

WHERE

last_24_hrs_rain_past_year.to_date >= (CURDATE() - INTERVAL 1 YEAR)

GROUP BY

YEAR(last_24_hrs_rain_past_year.to_date),

MONTH(last_24_hrs_rain_past_year.to_date),

DAY(last_24_hrs_rain_past_year.to_date)

) AS Data

GROUP BY

rain_in_last_24_hours_grouped

) AS DATA

GROUP BY

rain_in_last_24_hours_grouped

ORDER BY

rain_in_last_24_hours;

/* finally delete the rainfall table initially created as we don't need that data now */

DROP TABLE IF EXISTS last_24_hrs_rain_past_year;

END$$

DELIMITER ;

The other graphs that don’t compare the rainfall by visits use php scripts to run a query to retrieve the necessary information from the database and insert that data into arrays in a format that the Highcharts graphs can use. This is quite similar to the other graphs on the website. The Javascript code for the highcharts is heavily based on the graphs elsewhere on this website, which is accessible through viewing the page source code. 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.

The summary statistics at the top of the page uses data from a php script that contains a series of variables whose values are assigned from the result of SQL queries. That php script is used as an php include in the web page like this: include './utils/SQL-queries/analyticsData.php'; and the variables in that script are used within the table such as: <?php echo $visits7day ?>

Similarly a php script is used to return a variable for populating the update time for the visits by rainfall data. This is quite simple in that it returns the distinct updated_time field of the one of the database tables containing this data and the query is formatted in php to show the date in the required date format.

These are the SQL statements to calculate the summary statistics in the table:

/* construct a new query for visits and visitors in last 24 hrs */
	SELECT
					COUNT(DISTINCT(idvisit)) AS visits,
					COUNT(DISTINCT(idvisitor)) AS visitors
				FROM
					mtys_log_link_visit_action
				WHERE 
					DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 DAY );
	
/* construct a new query for visits in last 7 days */
				SELECT
					COUNT(DISTINCT(idvisit)) AS visits,
					COUNT(DISTINCT(idvisitor)) AS visitors
				FROM
					mtys_log_link_visit_action
				WHERE 
					DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 7 DAY );


/*construct a new query for visits in last 30 days*/
				SELECT
					COUNT(DISTINCT(idvisit)) AS visits,
					COUNT(DISTINCT(idvisitor)) AS visitors
				FROM
					mtys_log_link_visit_action
				WHERE 
					DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 MONTH );

/*construct a new query for visits -all time*/
				SELECT
					COUNT(DISTINCT(idvisit)) AS visits,
					COUNT(DISTINCT(idvisitor)) AS visitors
				FROM
					mtys_log_link_visit_action;

/*construct a new query for page views in last 24 hrs*/
				SELECT
					COUNT(idvisitor) AS page_views
				FROM
					mtys_log_link_visit_action
				WHERE 
					DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 DAY );

/* construct a new query for page views in last 7 days */
				SELECT
					COUNT(idvisitor) AS page_views
				FROM
					mtys_log_link_visit_action
				WHERE 
					DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 7 DAY );
 

/* construct a new query for page views in last 30 days*/
				SELECT
					COUNT(idvisitor) AS page_views
				FROM
					mtys_log_link_visit_action
				WHERE 
					DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 MONTH );

/* construct a new query for page views -all time*/
				SELECT
					COUNT(idvisitor) AS page_views
				FROM
					mtys_log_link_visit_action;

/* construct a new query for return visits in last 24 hrs*/
				SELECT
					COUNT(DISTINCT idvisit) AS return_visits
				FROM
					mtys_log_visit
				WHERE
					visitor_returning <> 0 
					AND DATE_ADD(visit_last_action_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 DAY );

/* construct a new query for return visits in last 7 days */
			SELECT
				COUNT(DISTINCT idvisit) AS return_visits
			FROM
				mtys_log_visit
            WHERE
            	visitor_returning <> 0 
                AND DATE_ADD(visit_last_action_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 7 DAY );

/* construct a new query for return visits in last 30 days */
			SELECT
				COUNT(DISTINCT idvisit) AS return_visits
			FROM
				mtys_log_visit
            WHERE
            	visitor_returning <> 0 
                AND DATE_ADD(visit_last_action_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 30 DAY );

/* construct a new query for return visits -all time*/
			SELECT
				COUNT(DISTINCT idvisit) AS return_visits
			FROM
				mtys_log_visit
            WHERE
            	visitor_returning <> 0;


/* construct a new query for frequent return visits in last 24 hrs*/
			/*To return the number of regular visitors and the number of visits*/
			SELECT COUNT(visitors) AS visitors, SUM(count_visits) as visits
			FROM(
				-- inner query to get all regular visitors  
				SELECT
						idvisitor AS visitors,
						count(idvisitor) as count_visits
				FROM
						mtys_log_visit
				WHERE
						visitor_returning > 0 
						AND DATE_ADD(visit_last_action_time, INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 DAY )
						AND visitor_days_since_last <=30
				GROUP BY 
						idvisitor
				) AS Data
				/* subquery to return just the visitors that have contributed 2 visits */
			WHERE count_visits >= 2;

/* construct a new query for frequent return visits in last 7 days*/
			/*-- To return the number of regular visitors and the number of visits*/
			SELECT COUNT(visitors) AS visitors, SUM(count_visits) as visits
			FROM(
				-- inner query to get all regular visitors  
				SELECT
						idvisitor AS visitors,
						count(idvisitor) as count_visits
				FROM
						mtys_log_visit
				WHERE
						visitor_returning > 0 
						AND DATE_ADD(visit_last_action_time, INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 7 DAY )
						AND visitor_days_since_last <=30
				GROUP BY 
						idvisitor
				) AS Data
				/* subquery to return just the visitors that have contributed 4 visits*/
			WHERE count_visits >= 4;
	
/* construct a new query for frequent return visits in last 30 days*/
			/*To return the number of regular visitors and the number of visits*/
			SELECT COUNT(visitors) AS visitors, SUM(count_visits) as visits
			FROM(
				/* inner query to get all regular visitors  */
				SELECT
						idvisitor AS visitors,
						count(idvisitor) as count_visits
				FROM
						mtys_log_visit
				WHERE
						visitor_returning > 0 
						AND DATE_ADD(visit_last_action_time, INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 30 DAY )
						AND visitor_days_since_last <=30
				GROUP BY 
						idvisitor
				) AS Data
				/* subquery to return just the visitors that have contributed to 5 visits*/
			WHERE count_visits >= 5;

	
	
/* construct a new query for frequent return visits - all time*/
			/* To return the number of regular visitors and the number of visits*/
			SELECT COUNT(visitors) AS visitors, SUM(count_visits) as visits
			FROM(
				/* inner query to get all regular visitors */ 
				SELECT
						idvisitor AS visitors,
						count(idvisitor) as count_visits
				FROM
						mtys_log_visit
				WHERE
						visitor_returning > 0
						AND visitor_days_since_last <=30
				GROUP BY 
						idvisitor
				) AS Data
				/* subquery to return just the visitors that have contributed to 1 % of the total frequent return visitor numbers */
			WHERE count_visits >= (SELECT 
										0.01*COUNT(idvisitor) 
								   FROM 
										mtys_log_visit 
								   WHERE
										visitor_returning > 0 
										AND visitor_days_since_last <=30);

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

/* construct a new query for visits and visitors in last 24 hrs */

SELECT

COUNT(DISTINCT(idvisit)) AS visits,

COUNT(DISTINCT(idvisitor)) AS visitors

FROM

mtys_log_link_visit_action

WHERE

DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 DAY );

/* construct a new query for visits in last 7 days */

SELECT

COUNT(DISTINCT(idvisit)) AS visits,

COUNT(DISTINCT(idvisitor)) AS visitors

FROM

mtys_log_link_visit_action

WHERE

DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 7 DAY );

/*construct a new query for visits in last 30 days*/

SELECT

COUNT(DISTINCT(idvisit)) AS visits,

COUNT(DISTINCT(idvisitor)) AS visitors

FROM

mtys_log_link_visit_action

WHERE

DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 MONTH );

/*construct a new query for visits -all time*/

SELECT

COUNT(DISTINCT(idvisit)) AS visits,

COUNT(DISTINCT(idvisitor)) AS visitors

FROM

mtys_log_link_visit_action;

/*construct a new query for page views in last 24 hrs*/

SELECT

COUNT(idvisitor) AS page_views

FROM

mtys_log_link_visit_action

WHERE

DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 DAY );

/* construct a new query for page views in last 7 days */

SELECT

COUNT(idvisitor) AS page_views

FROM

mtys_log_link_visit_action

WHERE

DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 7 DAY );

/* construct a new query for page views in last 30 days*/

SELECT

COUNT(idvisitor) AS page_views

FROM

mtys_log_link_visit_action

WHERE

DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 MONTH );

/* construct a new query for page views -all time*/

SELECT

COUNT(idvisitor) AS page_views

FROM

mtys_log_link_visit_action;

/* construct a new query for return visits in last 24 hrs*/

SELECT

COUNT(DISTINCT idvisit) AS return_visits

FROM

mtys_log_visit

WHERE

visitor_returning <> 0

AND DATE_ADD(visit_last_action_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 DAY );

/* construct a new query for return visits in last 7 days */

SELECT

COUNT(DISTINCT idvisit) AS return_visits

FROM

mtys_log_visit

WHERE

visitor_returning <> 0

AND DATE_ADD(visit_last_action_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 7 DAY );

/* construct a new query for return visits in last 30 days */

SELECT

COUNT(DISTINCT idvisit) AS return_visits

FROM

mtys_log_visit

WHERE

visitor_returning <> 0

AND DATE_ADD(visit_last_action_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 30 DAY );

/* construct a new query for return visits -all time*/

SELECT

COUNT(DISTINCT idvisit) AS return_visits

FROM

mtys_log_visit

WHERE

visitor_returning <> 0;

/* construct a new query for frequent return visits in last 24 hrs*/

/*To return the number of regular visitors and the number of visits*/

SELECT COUNT(visitors) AS visitors, SUM(count_visits) as visits

FROM(

-- inner query to get all regular visitors

SELECT

idvisitor AS visitors,

count(idvisitor) as count_visits

FROM

mtys_log_visit

WHERE

visitor_returning > 0

AND DATE_ADD(visit_last_action_time, INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 DAY )

AND visitor_days_since_last <=30

GROUP BY

idvisitor

) AS Data

/* subquery to return just the visitors that have contributed 2 visits */

WHERE count_visits >= 2;

/* construct a new query for frequent return visits in last 7 days*/

/*-- To return the number of regular visitors and the number of visits*/

SELECT COUNT(visitors) AS visitors, SUM(count_visits) as visits

FROM(

-- inner query to get all regular visitors

SELECT

idvisitor AS visitors,

count(idvisitor) as count_visits

FROM

mtys_log_visit

WHERE

visitor_returning > 0

AND DATE_ADD(visit_last_action_time, INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 7 DAY )

AND visitor_days_since_last <=30

GROUP BY

idvisitor

) AS Data

/* subquery to return just the visitors that have contributed 4 visits*/

WHERE count_visits >= 4;

/* construct a new query for frequent return visits in last 30 days*/

/*To return the number of regular visitors and the number of visits*/

SELECT COUNT(visitors) AS visitors, SUM(count_visits) as visits

FROM(

/* inner query to get all regular visitors */

SELECT

idvisitor AS visitors,

count(idvisitor) as count_visits

FROM

mtys_log_visit

WHERE

visitor_returning > 0

AND DATE_ADD(visit_last_action_time, INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 30 DAY )

AND visitor_days_since_last <=30

GROUP BY

idvisitor

) AS Data

/* subquery to return just the visitors that have contributed to 5 visits*/

WHERE count_visits >= 5;

/* construct a new query for frequent return visits - all time*/

/* To return the number of regular visitors and the number of visits*/

SELECT COUNT(visitors) AS visitors, SUM(count_visits) as visits

FROM(

/* inner query to get all regular visitors */

SELECT

idvisitor AS visitors,

count(idvisitor) as count_visits

FROM

mtys_log_visit

WHERE

visitor_returning > 0

AND visitor_days_since_last <=30

GROUP BY

idvisitor

) AS Data

/* subquery to return just the visitors that have contributed to 1 % of the total frequent return visitor numbers */

WHERE count_visits >= (SELECT

0.01*COUNT(idvisitor)

FROM

mtys_log_visit

WHERE

visitor_returning > 0

AND visitor_days_since_last <=30);

And these are the SQL statements to return the data for the graphs excluding the visits by rainfall which while a php script also retrieves that data, those queries are very basic because the data is already pre-generated.

/* construct a new query for visits by month*/
			SELECT
				UNIX_TIMESTAMP(server_time),
				COUNT(DISTINCT(idvisit)) AS visits,
				COUNT(idvisitor) AS page_views,
				COUNT(DISTINCT(idvisitor)) AS visitors
			FROM
				mtys_log_link_visit_action
			GROUP BY
				YEAR(server_time),
				MONTH(server_time);
	
/* construct a new query for monthly visits by device type */
			SELECT UNIX_TIMESTAMP(date_format(visit_time, "%Y-%m-01")) as visit_time, desktop_visit, mobile_visit, tablet_visit, mobile_percent
			FROM (
				SELECT  server_time as visit_time,     
						COUNT(DISTINCT CASE WHEN config_device_type = 0 THEN mtys_log_link_visit_action.idvisit END) AS desktop_visit,         
						COUNT(DISTINCT CASE WHEN config_device_type = 1 THEN mtys_log_link_visit_action.idvisit END) AS mobile_visit,         
						COUNT(DISTINCT CASE WHEN config_device_type = 2 THEN mtys_log_link_visit_action.idvisit END) AS tablet_visit,                                       
						ROUND(COUNT(DISTINCT CASE WHEN config_device_type = 1 THEN mtys_log_link_visit_action.idvisit END) / COUNT(DISTINCT mtys_log_link_visit_action.idvisit) *100,1) AS mobile_percent 

				FROM mtys_log_visit INNER JOIN mtys_log_link_visit_action ON mtys_log_visit.idvisitor = mtys_log_link_visit_action.idvisitor 
				GROUP BY YEAR(visit_time), MONTH(visit_time)
				) AS Data
			GROUP BY YEAR(visit_time), MONTH(visit_time);


/*construct a new query for monthly return and frequent return visits*/
				SELECT 	UNIX_TIMESTAMP(date_format(visit_last_action_time, "%Y-%m-01")) AS visit_last_action_time,
				COUNT(DISTINCT idvisitor) as visitors,
				SUM(visits) AS visits
				FROM(
					SELECT 	*
						FROM(
							SELECT
								DATE_ADD(visit_last_action_time, INTERVAL 10 HOUR) AS visit_last_action_time,
								idvisitor,
								COUNT( idvisitor) AS visits
							FROM
									mtys_log_visit
							WHERE
									visitor_returning > 0 
									AND visitor_days_since_last <=30
							GROUP BY year(visit_last_action_time), month(visit_last_action_time), idvisitor
							ORDER BY `visits`  DESC
							) AS Visits
					WHERE visits >= 5
				)AS visits_summ
			GROUP BY year(visit_last_action_time), month(visit_last_action_time);

/* construct a new query for visits by month*/

SELECT

UNIX_TIMESTAMP(server_time),

COUNT(DISTINCT(idvisit)) AS visits,

COUNT(idvisitor) AS page_views,

COUNT(DISTINCT(idvisitor)) AS visitors

FROM

mtys_log_link_visit_action

GROUP BY

YEAR(server_time),

MONTH(server_time);

/* construct a new query for monthly visits by device type */

SELECT UNIX_TIMESTAMP(date_format(visit_time, "%Y-%m-01")) as visit_time, desktop_visit, mobile_visit, tablet_visit, mobile_percent

FROM (

SELECT server_time as visit_time,

COUNT(DISTINCT CASE WHEN config_device_type = 0 THEN mtys_log_link_visit_action.idvisit END) AS desktop_visit,

COUNT(DISTINCT CASE WHEN config_device_type = 1 THEN mtys_log_link_visit_action.idvisit END) AS mobile_visit,

COUNT(DISTINCT CASE WHEN config_device_type = 2 THEN mtys_log_link_visit_action.idvisit END) AS tablet_visit,

ROUND(COUNT(DISTINCT CASE WHEN config_device_type = 1 THEN mtys_log_link_visit_action.idvisit END) / COUNT(DISTINCT mtys_log_link_visit_action.idvisit) *100,1) AS mobile_percent

FROM mtys_log_visit INNER JOIN mtys_log_link_visit_action ON mtys_log_visit.idvisitor = mtys_log_link_visit_action.idvisitor

GROUP BY YEAR(visit_time), MONTH(visit_time)

) AS Data

GROUP BY YEAR(visit_time), MONTH(visit_time);

/*construct a new query for monthly return and frequent return visits*/

SELECT UNIX_TIMESTAMP(date_format(visit_last_action_time, "%Y-%m-01")) AS visit_last_action_time,

COUNT(DISTINCT idvisitor) as visitors,

SUM(visits) AS visits

FROM(

SELECT *

FROM(

SELECT

DATE_ADD(visit_last_action_time, INTERVAL 10 HOUR) AS visit_last_action_time,

idvisitor,

COUNT( idvisitor) AS visits

FROM

mtys_log_visit

WHERE

visitor_returning > 0

AND visitor_days_since_last <=30

GROUP BY year(visit_last_action_time), month(visit_last_action_time), idvisitor

ORDER BY `visits` DESC

) AS Visits

WHERE visits >= 5

)AS visits_summ

GROUP BY year(visit_last_action_time), month(visit_last_action_time);

New graphs

Posted on 18 August 2020 | by Ferny Grove Weather | Leave a Comment

In the past month two new pages are available that show recent and historical data as charts for various parameters. This data is drawn from the weather station database each time one of these graphs are viewed. These new pages are:

Historic Graphs that shows the historical data for the various parameters summarised as daily, monthly and annually. This uses data from the entire station record of the past 9 to 11 years (depending on the parameter). Among these graphs is the plotting of the long term average for each month of the year, along with the highest and lowest measurement made.
Hourly Data Graphs shows the average value for each hour for the past 30 days compared to the long term average for the respective hour and month. Also plotted is a dashed line of the hourly average value. This allows to view the trends summarised as hour over time and see how the much the plotted data is above or below average (or close to average). This calculation is a little intensive, so the graph my take a little longer to load than the other graphs because all the sub-hourly data records over the entire station record need to retrieved from the database to determine the long term average which is then compared to the calculated average value for each hour.

May 2020 website additions

Posted on 31 May 2020 | by Ferny Grove Weather | Leave a Comment

Several new pages are now available on the website using data retrieved from a database of the weather station data. These new pages are:

Past Month Graphs that graphically shows the data for the various parameters at 5 minute intervals for the past 30 days. This is functionally very similar to the Recent Data graphs which shows the last 7 days but at a 1 minute resolution.
Top 10 Records shows the Top 10 extremes measured at this station for various parameters.
Recent Extremes Since looks at some of the extremes for today and yesterday and compared that to the all-time extreme records and in the past for the current month by showing the last time these extremes was higher or lower.

Various other modifications that has been made and are summarised as:

Some bugs/ corrections with the graphing have been made with the major issues being the home page real-time graph not updating with new data after the page loads and the “Daily Temperatures” and “Daily Rainfall” graphs showing dates into the future.
The introduction of Past Month Graphs replaced a previously available page to show data in case of system interruptions. That was a non database data source and so this removal brought a reduction in bandwidth usage.
Backend improvements to the file structure, updating J Query and consolidating the graphing to use a single source of styling
The Seabreeze Forecast was fixed after it stopped working on the Forecasts and Observations page.
The station status showing the station online or off-line on the home page and similarly on the System Status have been improved. It now handles instances when the station goes off-line when issues occur or for maintenance in a more robust manner. It does that by now making these checks on all uploaded data. It also now indicates whether interruptions or going off-line is a partial issue affecting all data updating on the website or whether is only affecting some of the data.

Welcome to the blog

Posted on 26 April 2020 | by Ferny Grove Weather | Leave a Comment

Today is one year to the day that this website was launched. Just over 2000 page views have been made from at least 1200 visits in the last year.

The Ferny Grove Weather website was created as a personal project to replace the existing website that was well over-due for a revamp and for the current weather data to be available online in real-time.

I would like to thank everyone for visiting this website during this time, many of who have have showed interest by returning numerous times or by providing feedback on how you like the website. It was not anticipated that this website would continue to see people visiting this website in this way. In fact during active weather events visitor activity of the number of people who visit the site would significantly increase.

I offer this weather data without warranty in the hope that it is useful. There are many people publishing weather station data through international personal weather station networks. However there are very few that run independent weather websites in our region of south east Queensland. The benefit of these websites is that they measure weather data at locations without official stations nearby with the freedom to present the data how we like to show it.

I am starting up this blog given this interest and primarily as a way to communicate that new content is available and content that is under testing/ or trial. There is much planned to add to what we already have available involving learning different processes to deliver that data and that will take time to achieve. Additionally I would like to see this as a way to inform on how this data is produced and published if there is appetite for this information.

Please tell us if you have thoughts on what you information you would like to see on this blog.