I have a table with the wind direction (among other weather values) every 10 seconds on a new row. The direction is stored as degrees: 0 - 360.
Purpose
What is the meaning of this average? The database stores every 10 seconds a row with information. For performance issues I want to aggregate the data older then 5 days to one (average) line per hour.
With the temperature it is easy to accomplish: avg(temp) does the trick as the temperature would not be jumping between to much different values.
With the prevailing wind it's a lot harder to get this 'average'.
Calculating the average wind direction in degrees is not as simple as using the aggregate function avg() as it's a circle, e.g.:
dir1 = 10; dir2 = 350;
avg() = (10+350)/2 = 180;
Which is not accurate; it should be: 0 or 360.
So with my trigonometry classes in university in the back of my head it thought, If i convert it to Radians, and calculate the x and y component I would be able to recalculate the avg direction.
in php with $w['w'] being the direction stored in the database.
while($w = $stmt->fetch()){
$x += cos(deg2rad($w['w']));
$y += sin(deg2rad($w['w']));
}
$angle = atan2($y, $x);
$angle = 360 + round(rad2deg($angle));
Is this formula correct?
If this formula is correct; ideally I would like to get the complete function to MySQL. I made this out of it; but I find a lot of ()'s...
(360 + degrees(atan2(sum(sin(radians(W))), sum(cos(radians(W))))))) AS angle
The problem with the initial suggestion is that it always adds 360, whereas really you only want to add 360 if the atan2 gives a negative result. This can be sorted with an IF statement, but it's even more bulky than the original!
The second solution can't be used for time averaging because it's not an aggregate function.
The best solution I've managed actually uses a stored function, which you can create if you have the privileges on the database as follows:
You can then calculate the average by calling on the function within a query:
I've included the group by to make the point that the average procedure then works for any averaging period.
The actual best answer, I suspect, is to use a user-defined function, but I haven't managed that as it has to be done in C or C++ and I don't have any background in those languages.