Belchertown Skin: Average Monthly Rain Across Years Issue

[Shane Burkhardt]

I am trying to create a chart in the Belchertown skin that would display the average monthly climatological data such as a average high, average low, average dewpoint and average rainfall per month. This would average across my entire database so it would not be year specific.

I have the averages for everything working correctly except the rainfall. No matter what I do, I am either only getting a per day average, or a sum of all rainfall (i.e. monthly rainfall in February for each year TOTAL instead of average. So in the graph below it is showing 13in when in fact that is the sum of rain in February over a 3 year period and not the average of the total monthly rainfall in each February).

I have consulted the Belchertowwn skin wiki, but the definitions of aggregate_type, aggregate_interval, and average_type, are a bit unclear and use some circular definitions. I looked at some chart examples, but they also seem to sum the totals and not average them. I do not mean this as criticism as the skin really rocks!!!!! I know this skin uses Highcharts, but the Highcharts forums seem a foreign language to me.

I have tried different combinations of interval, and aggregate/average tyIIpes but that does not seem to work.

Here is the code from my graphs.conf:

[averageclimate]
    title = "Average Climate by Month"
    show_button = true
    button_text = "Average Climate"
    type = spline
    time_length = all
    tooltip_date_format = "dddd LL"
    aggregate_interval = 86400 # 1 day
    gapsize = 86400 # 1 day in seconds
    start_at_beginning_of_month = true

    [[avgclimatetotal]]
        title = "Average Climatological Values by Month"
        type = spline
        aggregate_interval = 86400 # 1 day
        xAxis_groupby = month
        xAxis_categories = 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'
        [[[outTemp]]]
            aggregate_type = avg
            average_type = max
            zIndex = 2
            name = Max Average Temperature
            color = red
            [[[[marker]]]]
                enabled = true
                radius = 4
        [[[outTemp_min]]]
            name = Min Average Temperature
            observation_type = outTemp
            aggregate_type = avg
            average_type = min
            zIndex = 2
            [[[[marker]]]]
                enabled = true
                radius = 4
        [[[dewpoint]]]
            name = Average Dewpoint
            aggregate_type = avg
            zIndex = 2
            color = purple
            [[[[marker]]]]
                enabled = true
                radius = 4
        [[[rain]]]
            name = Rain Total
            aggregate_interval = day
            aggregate_type = sum
            average_type = none
            type = column
            yAxis = 1
            zIndex = 0
            color = "#268bd2"

Here is an example of the output I am getting:

I am screwing something up here, I just do not know what it is. Any help is appreciated!!!

[Shane Burkhardt]

UPDATE:

For those that may be interested. I was able to get this working! It was not easy though due to limitation on how weewx stores current data and problems with averaging across years when you do group_by. So to handle that I did two things:

1. Created a custom service that adds weekly, monthly and yearly rain totals to the weewx archive as separate tables. This makes averaging much easier. The service will parse all archived data and then keep track of future data on database changes.

2. Modified belchertown.pay to add the ability to run custom sql queries in-line in graphs.conf. I added two more parameters (use_custom_sql=boolean) and (custom_sql_query="string"). This allowed me, in the instance of creating monthly rain averages across years to write a custom sql query of

custom_sql_query = "SELECT month, AVG(avg_rain) AS avg_monthly_climo_rain FROM rain_monthly_averages GROUP BY month ORDER BY month ASC"

that is placed in my graphs.conf table.

The graphs.conf would look like:

        [[[custom_average_rains]]]
            name = Average Monthly Rain Total
            use_custom_sql = true # This tells Belchertown to use a custom SQL query
            custom_sql_query = "SELECT month, AVG(avg_rain) AS avg_monthly_climo_rain FROM rain_monthly_averages GROUP BY month ORDER BY month ASC"
            x_column = month # The column from your SQL query for the X-axis (1-12)
            y_column = avg_monthly_climo_rain # The column from your SQL query for the Y-axis (the average total rain)
            type = column # Display as a bar chart
            yAxis = 1 # Use the secondary Y-axis, typically for rain
            zIndex = 0
            color = "#268bd2" # Use your preferred color for rain bars

And the output renders:

Now it has the correct averages for each month instead of the SUM, which it would do no matter how you asked it to create averages.

If anyone is interested in the code for the service or modifications to belchertown.py, I am doing some cleanup, but can put those up on github. I have not had the time to do a lot of testing, so I am sure there are edge cases that might break things. I am not a Python coder, I muddle more in javascript, and knew turbo pascal well back in high school (yeah I am getting old). But I at least got the code to do what I wanted, while trying to make this applicable for future custom queries. Oh the queries that are placed in weewx.conf should also be able to be called this way as well. I have not tried it yet, but it should work. I will try to test that as well when I clean up some of the code.

Shane