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
Advertisement
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> |
+---------+-------------+