Postgres集结函数，用于计算风速（矢量幅值）和风向（矢量方向）的矢量平均值 [英] Postgres aggregrate function for calculating vector average of wind speed (vector magnitude) and wind direction (vector direction)

问题描述

我有一个包含两列 wind_speed 和 wind_direction 的表。我想要一个自定义的聚集函数，该函数将返回平均 wind_speed 和 wind_direction 。 wind_speed 和 wind_direction 组合定义了一个矢量，其中 wind_speed 是向量的大小， wind_direction 是向量的方向。 avg_wind_direction 函数应返回平均值 wind_speed 作为幅度，并返回 wind_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;

相关问题：针对圆形平均值的自定义PostgreSQL聚合

推荐答案

因此，我已经能够创建一个聚集函数来执行向量平均。它假定矢量在极坐标中，而角度在度中，而不是弧度。

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

测试：

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

测试：

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

结果：

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

删除所有行：

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

结果：

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

这篇关于Postgres集结函数，用于计算风速（矢量幅值）和风向（矢量方向）的矢量平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！