Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Mean of circular quantities in SQL
-- MySQL
SELECT UNIX_TIMESTAMP(date), avg_cyclic, frequency_start , frequency_stop, intensity, source
FROM (SELECT MAX(id) AS max_id, CONCAT(echo.frequency_start , '-', echo.frequency_stop) AS frequency
FROM echo
WHERE channel = {0}
GROUP BY frequency) AS id,
echo,
(SELECT
IF (avg(sin(direction*PI()/180))>0 AND avg(cos(direction*PI()/180))>0,
atan(avg(sin(direction*PI()/180))/avg(cos(direction*PI()/180))),
IF (avg(cos(direction*PI()/180))<0,
atan(avg(sin(direction*PI()/180))/avg(cos(direction*PI()/180)))+PI(),
IF (avg(sin(direction*PI()/180))<0 AND avg(cos(direction*PI()/180))>0,
atan(avg(sin(direction*PI()/180))/avg(cos(direction*PI()/180)))+2*PI(),
1000
)
)
)*180/PI() AS avg_cyclic, frequency
FROM (SELECT direction, CONCAT(echo.frequency_start , '-', echo.frequency_stop) AS frequency
FROM echo WHERE channel = {0} AND date > now() - interval {1} second) AS echo_last
GROUP BY frequency) AS echo_avg
WHERE id.max_id = echo.id
AND echo_avg.frequency = id.frequency
-- PostgreSQL
CREATE OR REPLACE FUNCTION avg_cyclic_sfunc(float8[], float8) RETURNS float8[]
AS 'select ARRAY[$1[1]+sin($2*pi()/180), $1[2]+cos($2*pi()/180), $1[3]+1];'
LANGUAGE SQL
RETURNS NULL ON NULL INPUT;
CREATE OR REPLACE FUNCTION avg_cyclic_finalfunc(float8[]) RETURNS float8 AS
$$
DECLARE
s float8;
c float8;
result float8;
BEGIN
s := $1[1]/$1[3];
c := $1[2]/$1[3];
result := atan2(s, c)*180/pi();
IF s > 0 AND c > 0 THEN
RETURN result;
ELSIF c < 0 THEN
result := result + 180;
ELSIF s < 0 AND c > 0 THEN
result := result + 360;
END IF;
RETURN 1000;
END;
$$
LANGUAGE 'plpgsql'
RETURNS NULL ON NULL INPUT;
CREATE AGGREGATE avg_cyclic(float8)
(
sfunc = avg_cyclic_sfunc,
stype = float8[],
finalfunc = avg_cyclic_finalfunc,
initcond = '{0,0,0}'
);
@milesjordan

This comment has been minimized.

Copy link

commented May 20, 2013

I wanted to thank you for providing this. In my fork, I added an Oracle aggregate function. Your PL/SQL code helped me with the arithmetic, however simply averaging the linear coordinates and passing to atan2 was enough, without the extra checking. Cheers.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.