Cumulus Support

http://weather.wilmslowastro.com/records_monthly.php

The basic SQL query took a bit of working out, many blind alleys with inner joins etc!

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

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

So i guess you have a script that outputs the (values = {}) array with a specific SQL querie for each line ?

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!).

Hi Mark,

I think your queries are just fine.

Kerry

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.

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

Great, that is a completely different approach from what I am doing, I'll have a play with it tomorrow. Thanks.

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').

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

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.

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

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?

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