Monthly Records - Spot the additions... And New Wind Rose

[mcrossley]

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

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

[laulau]

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

[mcrossley]

Do you mean the SQL queries?

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

and minimum monthly rainfall is...

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

We just need Kerry to run an expert eye over them and suggest some optimisations

[laulau]

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

[mcrossley]

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

[tobyspond]

Hi Mark,

I think your queries are just fine.

Kerry

[mcrossley]

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.

[tobyspond]

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

[mcrossley]

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

[mcrossley]

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

[tobyspond]

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

[mcrossley]

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.

[tobyspond]

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

[mcrossley]

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?

[tobyspond]

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