Postgres aggregrate function for calculating vector average of wind speed (vector magnitude) and wind direction (vector direction)

I have a table with two columns wind_speed and wind_direction. I want to have a custom aggregrate function that would return average wind_speed and wind_direction. wind_speed and wind_direction in combination defines a vector where wind_speed is the magnitude of the vector and wind_direction is the direction of the vector. avg_wind_direction function should return average wind_speed as magnitude and wind_direction as direction of the average vector.

SELECT avg_wind_direction(wind_speed, wind_direction)
FROM sometable
GROUP BY location;

Related question: Custom PostgreSQL aggregate for circular average

Answer

So I have been able to create an aggregrate function that does the vector averaging. It makes the assumption that the vector is in polar co-ordinates and the angle is in degrees, as opposed to radian.

DROP AGGREGATE IF EXISTS vector_avg(float, float) CASCADE;
DROP TYPE IF EXISTS vector_sum CASCADE;
DROP TYPE IF EXISTS avg_vector CASCADE;

CREATE TYPE vector_sum AS (x float, y float, count int);
CREATE TYPE avg_vector AS (magnitude float, direction float);

CREATE OR REPLACE FUNCTION sum_vector (vectors vector_sum, magnitude float, direction float)
  RETURNS vector_sum LANGUAGE sql STRICT AS
'SELECT vectors.x + (magnitude * cos(direction * (pi() / 180))), vectors.y + (magnitude * sin(direction  * (pi() / 180))), vectors.count + 1';
CREATE OR REPLACE FUNCTION avg_vector_finalfunc(vectors vector_sum) RETURNS avg_vector AS
$$
DECLARE
        x float;
        y float;
        maybe_neg_angle numeric;
        angle numeric;

        v_state   TEXT;
        v_msg     TEXT;
        v_detail  TEXT;
        v_hint    TEXT;
        v_context TEXT;
BEGIN
    BEGIN
        IF vectors.count = 0 THEN
            RETURN (NULL, NULL)::avg_vector;
        END IF;

        x := (vectors.x/vectors.count); 
        y := (vectors.y/vectors.count);

        -- This means the vector is null vector
        -- Please see: https://math.stackexchange.com/a/3682/10842
        IF x = 0 OR y = 0 THEN
            RAISE NOTICE 'X or Y component is 0. Returning NULL vector';
            RETURN (0.0, 0.0)::avg_vector;
        END IF;

        maybe_neg_angle := atan2(CAST(y AS NUMERIC), CAST(x AS NUMERIC)) * (180.0 / pi());
        angle := MOD(CAST((maybe_neg_angle + 360.0) AS NUMERIC), CAST(360.0 AS NUMERIC));

        RETURN (sqrt(power(x, 2) + power(y, 2)), angle)::avg_vector;

    EXCEPTION WHEN others THEN
        RAISE NOTICE 'Exception was raised. Returning just NULL';
        RETURN (NULL, NULL)::avg_vector;
    END;
END;
$$
LANGUAGE 'plpgsql'
RETURNS NULL ON NULL INPUT;


CREATE AGGREGATE vector_avg (float, float) (
   sfunc     = sum_vector
 , stype     = vector_sum
 , finalfunc = avg_vector_finalfunc
 , initcond  = '(0.0, 0.0, 0)'
);

Test:

DROP TABLE t;
CREATE TEMP TABLE t(speed float, direction float);
INSERT INTO t VALUES (23, 334), (20, 3), (340, 67);

Test:

SELECT (vector_avg(speed, direction)).magnitude AS speed, (vector_avg(speed, direction)).direction AS direction FROM t;

Result:

+-----------------+-------------------+
| speed           | direction         |
+=================+===================+
| 108.44241888507 | 0.972468335643555 |
+-----------------+-------------------+

Removing all the rows:

DELETE FROM t;
SELECT (vector_avg(speed, direction)).magnitude AS speed, (vector_avg(speed, direction)).direction AS direction FROM t;

Result:

+---------+-------------+
| speed   | direction   |
+=========+=============+
| <null>  | <null>      |
+---------+-------------+