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

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 >=.

Answer 2

Check this out: http://www.h2ns.com/media/tech-notes/tn09.pdf

In my case I have a table with wind direction (deg) and speed recorded every 20 or 30 seconds, so I need to average in 1 minute.

I use a SQL statement like this:

select
    DATE_FORMAT(ut,'%Y-%m-%d %H:%i:00'),
    SQRT(POW(avg(WS*sin(RADIANS(WD))),2)+ POW(avg(WS*cos(RADIANS(WD))),2))
as UV, ATAN(avg(WS*sin(RADIANS(WD))) / avg(WS*cos(RADIANS(WD))))
as AV
from Weather
GROUP BY DATE(ut),HOUR(ut),MINUTE(ut);

Answer 3

I've got this solution for calculating daily average wind direction in degrees for multiple values in SQL Server 2008. ##Day is one day of time-series values.

SELECT @DayMean =
CASE WHEN ABS(@DayMax - @DayMin) > 180 THEN --this line could be wrong, might need to compare consecutive values
  CASE WHEN AVG(CASE WHEN (Value > 180) THEN Value-360 ELSE Value END) < 0 
    THEN 
      360+AVG(CASE WHEN (Value > 180) THEN Value-360 ELSE Value END)
    ELSE
      AVG(CASE WHEN (Value > 180) THEN Value-360 ELSE Value END)
  END
ELSE
  AVG(Value)
END
FROM ##Day

Inspired from the second to last post on this page http://www.control.com/thread/1026210133 and then figured out in excel.

This line could be wrong, but the calculation in excel involved finding the difference between successive numbers, including the last value minus zero, which doesn't make much sense to me (and could just be there because it's easier to compare consecutive values than min and max's in excel).

CASE WHEN ABS(@DayMax - @DayMin) > 180 THEN

So instead, maybe it should be 'if any value is over 180', or 'if the maximum "distance" between consecutive points is over 180'. A solution to the latter case would probably make the calculation much slower.

Please crit and correct where you can :)

Answer 4

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.