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 w

Solution 1:

First sorry if i break any posting rules here, first time poster and all that.

Wanted to use the above answer together with the timescaledb addition to postgres for my diy weather station but it turns out that the function is not parallel safe. Also afik the use of atan does not yield the correct answer.

So this is my modified version that i think should be parallel safe and uses atan2 instead.

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);

CREATEOR REPLACE FUNCTION sum_vector (vectors vector_sum, magnitude float, direction float)
  RETURNS vector_sum LANGUAGEsql PARALLEL SAFE STRICT AS'SELECT vectors.x + (magnitude * cos(radians(direction))), vectors.y + (magnitude * sin(radians(direction))), vectors.count + 1';

CREATEOR REPLACE FUNCTION combine_sum (part1 vector_sum , part2 vector_sum)
  RETURNS vector_sum LANGUAGEsql PARALLEL SAFE STRICT AS'SELECT (part1.x+part2.x)/2,(part1.y+part2.y)/2,part1.count+part2.count';

CREATEOR REPLACE FUNCTION avg_vector_finalfunc(vectors vector_sum)
RETURNS avg_vector
AS
$$
DECLARE
        x float;
        y float;
        d float;
BEGINBEGIN
        IF vectors.count =0THENRETURN (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 =0OR y =0THENRETURN (0, 0)::avg_vector;
        END IF;

         d:=degrees(atan2(y,x));

        -- atan2 returns negative result for angles > 180

        IF d <0THEN
          d := d+360;
        END IF;

        RETURN (sqrt(power(x, 2) +power(y, 2)), d )::avg_vector;
    EXCEPTION WHEN others THENRETURN (NULL, NULL)::avg_vector;
    END;
END;
$$
LANGUAGE'plpgsql'
PARALLEL SAFE
RETURNSNULLONNULL INPUT;

CREATE AGGREGATE vector_avg (float, float) (
   sfunc     = sum_vector
 , stype     = vector_sum
 , combinefunc = combine_sum
 , finalfunc = avg_vector_finalfunc
 , initcond  ='(0.0, 0.0, 0)'
 , PARALLEL  = SAFE

Test from a very small sample:

psql-dweather-c"select * from windavgtest;"time|direction|speed-------------------------------+-----------+-------2019-08-01 16:51:53.199357+00|170|12019-08-01 16:51:54.388392+00|170|12019-08-01 16:51:55.335034+00|170|12019-08-01 16:51:56.362812+00|170|12019-08-01 16:52:07.191919+00|190|12019-08-01 16:52:08.250756+00|190|12019-08-01 16:52:09.193265+00|190|12019-08-01 16:52:10.224283+00|190|1(8rows)

yields:

psql -d weather -c  "select round((vector_avg(speed, direction)).direction) AS wdirection from windavgtest;
"
 wdirection 
------------
        180
(1 row)

Solution 2:

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);

CREATEOR REPLACE FUNCTION sum_vector (vectors vector_sum, magnitude float, direction float)
  RETURNS vector_sum LANGUAGEsql STRICT AS'SELECT vectors.x + (magnitude * cos(direction * (pi() / 180))), vectors.y + (magnitude * sin(direction  * (pi() / 180))), vectors.count + 1';
CREATEOR 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;
BEGINBEGIN
        IF vectors.count =0THENRETURN (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 =0OR y =0THEN
            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 ASNUMERIC), CAST(x ASNUMERIC)) * (180.0/ pi());
        angle :=MOD(CAST((maybe_neg_angle +360.0) ASNUMERIC), CAST(360.0ASNUMERIC));

        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'RETURNSNULLONNULL INPUT;


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

Test:

DROPTABLE t;
CREATE TEMP TABLE t(speed float, direction float);
INSERTINTO 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:

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

Result:

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