Aug 15, 2007
Average direction in SQL
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.
Aug 15, 2007 | 8:23 PM
Oh dear. I sense you may have had to spend a bit of time on this, then?
Aug 16, 2007 | 6:10 AM
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.