Calculate average of wind direction in MySQL

Question

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

Answer 1

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!

IF( degrees (atan2( sum(sin(radians(WindDirection))) , sum(cos(radians(WindDirection))) ) )<0,
360+degrees ( atan2( sum(sin(radians(WindDirection))), sum(cos(radians(WindDirection))) )),
degrees(atan2(sum(sin(radians(WindDirection))), sum(cos(radians(MastDirection))) )) ) AS angle 

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:

CREATE FUNCTION Vavg (sumsindir FLOAT,sumcosdir FLOAT) 
RETURNS FLOAT DETERMINISTIC 
RETURN IF( DEGREES(ATAN2(sumsindir,sumcosdir))<0,360+DEGREES(ATAN2(sumsindir,sumcosdir)) , DEGREES(ATAN2(sumsindir,sumcosdir)))

You can then calculate the average by calling on the function within a query:

SELECT VavgTest( sum(sin(radians(WindDirection))),sum(cos(radians(WindDirection))) ) AS vectorAverage FROM table GROUP BY HOUR(MyTimestamp),DAYOFYEAR(MyTimestamp),YEAR(MyTimestamp)
ORDER BY MyTimestamp;

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.

Answer 2

If we draw the directions on a circle, we see that the "average" that you're after in this case falls on the halfway of the shorter segment of the circle (green segment). The halfway point of the longer segment is on the exact opposite side of the other segment's halfway point.

A simple (A+B)/2 calculation gives either the longer or the shorter segment's halfway point. If it's the longer segment, the shorter segment can be found with S1 = (S2+180) % 360 where S2 is the longer segment (and % is the modulo operator).

Now all that's left to do is to determine when we get the longer segment and when we get the shorter segment, and that is whenever ABS(B-A) > 180.

Put all this together and the calculation would be:

( (A+B)/2 + IF( ABS(B-A) > 180, 180, 0 ) ) % 360

This is most likely much faster than trigonometric functions.

As mentioned in the comments, if the two directions are exactly opposite of each other (like 0° and 180°), the "average" could be on either side of the circle (90° or 270°). You'll just have to decide which one to pick. The above formula would pick 90° in this case. If you want 270° instead, change the comparison from > to >=.