Welcome to the Cumulus Support forum.
Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024
Cumulus MX V4 beta test release 4.0.0 (build 4019) - 03 April 2024
Legacy Cumulus 1 release 1.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
Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024
Cumulus MX V4 beta test release 4.0.0 (build 4019) - 03 April 2024
Legacy Cumulus 1 release 1.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
Monthly Records - Spot the additions... And New Wind Rose
Moderator: daj
- mcrossley
- Posts: 12770
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Monthly Records - Spot the additions... And New Wind Rose
http://weather.wilmslowastro.com/records_monthly.php
The basic SQL query took a bit of working out, many blind alleys with inner joins etc!
The basic SQL query took a bit of working out, many blind alleys with inner joins etc!
Last edited by mcrossley on Sat 04 Oct 2014 1:49 pm, edited 1 time in total.
- laulau
- Posts: 678
- Joined: Tue 13 Oct 2009 10:52 pm
- Weather Station: WeatherDuino Pro2
- Operating System: Win 7
- Location: Meyenheim, Alsace, FR
- Contact:
Re: Monthly Records - Spot the additions...
Hi Mark,
Is there a chance you share that code for those who don't have enough programming knowledge to do such a thing
Thanks
Is there a chance you share that code for those who don't have enough programming knowledge to do such a thing
Thanks
- mcrossley
- Posts: 12770
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: Monthly Records - Spot the additions...
Do you mean the SQL queries?
The dry/wet days per month is...
and minimum monthly rainfall is...
We just need Kerry to run an expert eye over them and suggest some optimisations
The dry/wet days per month is...
Code: Select all
SELECT MAX(days), date
FROM (
SELECT DATE_FORMAT(LogDate, "%M %Y") AS date
MONTH(LogDate) AS mon,
COUNT(*) AS days
FROM daydata
WHERE TotRainFall <0.2 /* or >0.1 for wet days*/
GROUP BY DATE_FORMAT(LogDate, "%b%Y")
ORDER BY days DESC, date ASC
) AS a
GROUP BY mon
Code: Select all
SELECT ROUND(MIN(rain),1), date
FROM (
SELECT DATE_FORMAT(LogDate, "%M %Y") AS date,
MONTH(LogDate) AS mon,
SUM(TotRainFall) AS rain
FROM daydata
WHERE DATE_FORMAT(LogDate, "%Y%m") < DATE_FORMAT(CURDATE(), "%Y%m")
GROUP BY DATE_FORMAT(LogDate, "%m%Y")
ORDER BY rain ASC, date ASC
) AS a
- laulau
- Posts: 678
- Joined: Tue 13 Oct 2009 10:52 pm
- Weather Station: WeatherDuino Pro2
- Operating System: Win 7
- Location: Meyenheim, Alsace, FR
- Contact:
Re: Monthly Records - Spot the additions...
So i guess you have a script that outputs the (values = {}) array with a specific SQL querie for each line ?
- mcrossley
- Posts: 12770
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: Monthly Records - Spot the additions...
I get the results - an array with 12 members - and simply use PHP write them out as additional data items in the JavaScript. The additional fields are defined in the HTML and the script matches the field 'id's to the data property names.
If you take a look at the source for my page you will see the data items and script etc. What you wont see of course is the PHP code to write out the values, but that is pretty straight forward. The existing data items are constructed from the standard Culumus web tags (via PHP of course!).
If you take a look at the source for my page you will see the data items and script etc. What you wont see of course is the PHP code to write out the values, but that is pretty straight forward. The existing data items are constructed from the standard Culumus web tags (via PHP of course!).
-
- Posts: 252
- Joined: Fri 24 Jun 2011 5:57 pm
- Weather Station: Davis Vantage Pro2
- Operating System: Windows 10
- Location: Lamoine, Maine, USA
Re: Monthly Records - Spot the additions...
Hi Mark,
I think your queries are just fine.
Kerry
I think your queries are just fine.
Kerry
- mcrossley
- Posts: 12770
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: Monthly Records - Spot the additions...
Thanks Kerry, I went around the houses to get there, but the 'order by' clause was the clever bit I was missing to avoid all sorts of fancy joins.
I sweated over another bit of SQL today, to extract wind rose data. I gave up on SQL filling in the 'missing' values from list tables with 'right joins', and added the 'missing' readings (direction bins for which there is no data in the period) in PHP JavaScript! Results here.
I sweated over another bit of SQL today, to extract wind rose data. I gave up on SQL filling in the 'missing' values from list tables with 'right joins', and added the 'missing' readings (direction bins for which there is no data in the period) in PHP JavaScript! Results here.
Last edited by mcrossley on Sun 28 Sep 2014 9:41 pm, edited 1 time in total.
-
- Posts: 252
- Joined: Fri 24 Jun 2011 5:57 pm
- Weather Station: Davis Vantage Pro2
- Operating System: Windows 10
- Location: Lamoine, Maine, USA
Re: Monthly Records - Spot the additions...
The windrose looks nice. One of these days I'll have to make the switch from JPgraphs to HighCharts or RGraph. To deal with missing data, I set the default in the MySQL table to NULL.
I have not revisited this code in awhile and it is built for JPGraph, but here's the code I use (I use mph):
Select dir,
sum(if(windspeed < 1,percent,0)) as calm,
sum(if(windspeed >=1 and windspeed <4,percent,0) )as '1-3',
sum(if(windspeed >=4 and windspeed <8,percent,0) ) as '4-7',
sum(if(windspeed >=8 and windspeed <13,percent,0) ) as '8-12',
sum(if(windspeed >=13 and windspeed <18,percent,0)) as '13-17',
sum(if(windspeed >=18 and windspeed <25,percent,0)) as '18-24',
sum(if(windspeed >=25,percent,0)) as '>=25'
from (select windspeed, windbearingsym as dir, count(*),
100*(count(*))/(select count(*) as total FROM monthfile where month(logdatetime)= $month and year(logdatetime)=$year) as percent FROM monthfile where month(logdatetime)= $month and year(logdatetime)=$year and windbearingsym IN('N', 'NNE', 'NE', 'ENE', 'E', 'ESE', 'SSE', 'SE','S', 'SSW', 'SW', 'WSW', 'W', 'WNW', 'NNW', 'NW') group by windbearingsym, windspeed, day(logdatetime)) as d group by dir
Kerry
I have not revisited this code in awhile and it is built for JPGraph, but here's the code I use (I use mph):
Select dir,
sum(if(windspeed < 1,percent,0)) as calm,
sum(if(windspeed >=1 and windspeed <4,percent,0) )as '1-3',
sum(if(windspeed >=4 and windspeed <8,percent,0) ) as '4-7',
sum(if(windspeed >=8 and windspeed <13,percent,0) ) as '8-12',
sum(if(windspeed >=13 and windspeed <18,percent,0)) as '13-17',
sum(if(windspeed >=18 and windspeed <25,percent,0)) as '18-24',
sum(if(windspeed >=25,percent,0)) as '>=25'
from (select windspeed, windbearingsym as dir, count(*),
100*(count(*))/(select count(*) as total FROM monthfile where month(logdatetime)= $month and year(logdatetime)=$year) as percent FROM monthfile where month(logdatetime)= $month and year(logdatetime)=$year and windbearingsym IN('N', 'NNE', 'NE', 'ENE', 'E', 'ESE', 'SSE', 'SE','S', 'SSW', 'SW', 'WSW', 'W', 'WNW', 'NNW', 'NW') group by windbearingsym, windspeed, day(logdatetime)) as d group by dir
Kerry
Last edited by tobyspond on Sun 28 Sep 2014 9:57 pm, edited 1 time in total.
- mcrossley
- Posts: 12770
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: Monthly Records - Spot the additions...
Great, that is a completely different approach from what I am doing, I'll have a play with it tomorrow. Thanks.
- mcrossley
- Posts: 12770
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: Monthly Records - Spot the additions...
Hi Kerry, I have taken a look at your query, it's a bit faster than mine and does provide all the 'zero' data for missing slots. The problem I am having is converting it to my requirements. I need the data grouped by windspeed range, with a column per the 16 directions, and if possible force the direction order to be as per the array rather than alphabetic. Not much too ask!
I'll carry on playing with it today to see if I can come up with something...
PS. You seem to have a superfluous count(*) on line 10? And the direction array has the last two elements swapped (not that it matters as you are using an 'in').
I'll carry on playing with it today to see if I can come up with something...
PS. You seem to have a superfluous count(*) on line 10? And the direction array has the last two elements swapped (not that it matters as you are using an 'in').
-
- Posts: 252
- Joined: Fri 24 Jun 2011 5:57 pm
- Weather Station: Davis Vantage Pro2
- Operating System: Windows 10
- Location: Lamoine, Maine, USA
Re: Monthly Records - Spot the additions...
Hi Mark,
I'll take a look at what you need. If I understand, you want to reverse the columns and rows in my query such that the wind speeds are rows and the directions are the columns and you want to have the directions in numeric order (I think and order by should take care of that).
Yes, the count(*0) is superfluous; it's leftover QA.
Kerry
I'll take a look at what you need. If I understand, you want to reverse the columns and rows in my query such that the wind speeds are rows and the directions are the columns and you want to have the directions in numeric order (I think and order by should take care of that).
Yes, the count(*0) is superfluous; it's leftover QA.
Kerry
- mcrossley
- Posts: 12770
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: Monthly Records - Spot the additions...
Yes, that is basically it, swap the columns/rows of the result around. I could process the result in PHP in guess. I'm also not sure what the "group by ..., day(logdatetime) is for? It seems to work fine without it, but the results are very slightly different.
-
- Posts: 252
- Joined: Fri 24 Jun 2011 5:57 pm
- Weather Station: Davis Vantage Pro2
- Operating System: Windows 10
- Location: Lamoine, Maine, USA
Re: Monthly Records - Spot the additions...
Mark,
You are correct, you do not need the grouping day(logdatetime)
This should do it:
Select ws,
sum(if(windbearing >=348.75 and windbearing <=360 or windbearing >=0 and windbearing <11.25,percent,0)) as 'N',
sum(if(windbearing >=11.25 and windbearing <33.75,percent,0)) as 'NNE',
sum(if(windbearing >=33.75 and windbearing <56.25,percent,0)) as 'NE',
sum(if(windbearing >=11.25 and windbearing <33.75,percent,0)) as 'NNE',
sum(if(windbearing >=33.75 and windbearing <56.25,percent,0)) as 'NE',
sum(if(windbearing >=56.25 and windbearing <78.75,percent,0)) as 'ENE',
sum(if(windbearing >=78.75 and windbearing <101.25,percent,0)) as 'E',
fill in the rest of the directions here
from (select windbearing, case when windspeed = 0 then 'calm' when windspeed >=1 and windspeed<=3 then '1-3' add additional windspeeds as you like here end as ws,
100*(count(*))/(select count(*) as total FROM mfile where month(logdatetime)= 8 and year(logdatetime)=2014) as percent FROM mfile where month(logdatetime)= 8 and year(logdatetime)=2014 group by windbearing, windspeed) as d where ws is not null group by ws order by windbearing
Kerry
You are correct, you do not need the grouping day(logdatetime)
This should do it:
Select ws,
sum(if(windbearing >=348.75 and windbearing <=360 or windbearing >=0 and windbearing <11.25,percent,0)) as 'N',
sum(if(windbearing >=11.25 and windbearing <33.75,percent,0)) as 'NNE',
sum(if(windbearing >=33.75 and windbearing <56.25,percent,0)) as 'NE',
sum(if(windbearing >=11.25 and windbearing <33.75,percent,0)) as 'NNE',
sum(if(windbearing >=33.75 and windbearing <56.25,percent,0)) as 'NE',
sum(if(windbearing >=56.25 and windbearing <78.75,percent,0)) as 'ENE',
sum(if(windbearing >=78.75 and windbearing <101.25,percent,0)) as 'E',
fill in the rest of the directions here
from (select windbearing, case when windspeed = 0 then 'calm' when windspeed >=1 and windspeed<=3 then '1-3' add additional windspeeds as you like here end as ws,
100*(count(*))/(select count(*) as total FROM mfile where month(logdatetime)= 8 and year(logdatetime)=2014) as percent FROM mfile where month(logdatetime)= 8 and year(logdatetime)=2014 group by windbearing, windspeed) as d where ws is not null group by ws order by windbearing
Kerry
- mcrossley
- Posts: 12770
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: Monthly Records - Spot the additions...
Thanks Kerry, that query becomes pretty horrendous doesn't it!
Meanwhile I have taken yours, and done the row/column switch in PHP, which also has the benefit that I add in any missing directions. Your query like my original does not return data for a direction if there is none - if you see what I mean. It usually only occurs for short time periods where the wind hasn't swung through 360.
I have updated http://weather.wilmslowastro.com/windrose/windrose.php to use your original query. (you may need to force a browser update as the default cache time is 3 days)
Looks like the "and windbearingsym IN('N', 'NNE', 'NE',..." isn't needed either?
Meanwhile I have taken yours, and done the row/column switch in PHP, which also has the benefit that I add in any missing directions. Your query like my original does not return data for a direction if there is none - if you see what I mean. It usually only occurs for short time periods where the wind hasn't swung through 360.
I have updated http://weather.wilmslowastro.com/windrose/windrose.php to use your original query. (you may need to force a browser update as the default cache time is 3 days)
Looks like the "and windbearingsym IN('N', 'NNE', 'NE',..." isn't needed either?
-
- Posts: 252
- Joined: Fri 24 Jun 2011 5:57 pm
- Weather Station: Davis Vantage Pro2
- Operating System: Windows 10
- Location: Lamoine, Maine, USA
Re: Monthly Records - Spot the additions...
Hi Mark,
Your windrose is looking good!
Yes, MySQL does not handle crosstabs well at all! You may not need the windbearingsym IN but in order to get jpgraphs to display the windrose properly, I need to include it in my query.
Kerry
Your windrose is looking good!
Yes, MySQL does not handle crosstabs well at all! You may not need the windbearingsym IN but in order to get jpgraphs to display the windrose properly, I need to include it in my query.
Kerry