Given a column of polar directions in degrees, this is a single SQL expression to compute their average, for use in a GROUP BY query. Some functions may be MySQL-specific.
IF(DEGREES(ATAN2(
-AVG(SIN(RADIANS(direction_deg)))
,-AVG(COS(RADIANS(direction_deg))))
) < 180
,DEGREES(ATAN2(
-AVG(SIN(RADIANS(direction_deg)))
,-AVG(COS(RADIANS(direction_deg))))
) + 180
,DEGREES(ATAN2(
-AVG(SIN(RADIANS(direction_deg)))
,-AVG(COS(RADIANS(direction_deg))))
) - 180
)Thought someone might like to run across this.
Oh dear. I sense you may have had to spend a bit of time on this, then?
Yeah, but it saves me from having to radically redesign a reporting tool. I need to benchmark it for larger queries, but it seems fast. I think MySQL optimizes away the identical DEGREES(…) expressions.