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.

2 thoughts on “Average direction in SQL

  1. says:

    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.

Leave a Reply