Calculating the mean of circular quantities cannot always be done using the usual arithmetic mean function. What would happen if we tried to average the angles 259° and 1°? The answer should quite obviously be 0°, however the arithmetic mean function will return 180°.

1 2 3 4 5 6 7 8 9 |
CREATE TABLE test (val NUMBER); INSERT INTO val (259); INSERT INTO val (1); SELECT AVG(val) FROM test; AVG(val) ---------- 180 |

I was averaging wind direction for some Antarctic weather station data, so this really wasn’t going to work.

The correct way to do it is to convert the angles to linear coordinates by taking their sine and cosine, averaging those and then converting back to polar coordinates.

In order to correctly calculate the mean of circular quantities in an Oracle database, I ended up creating an aggregate function. I’ll explain it in parts, but if you’re after the full code, it’s over on GitHub Gist. I should at this point thank hegge for providing examples in MySQL and PostGIS. Now, some explanation.

Firstly, we need to create a user-defined type for our data to use. I’ve called it “U_CIRCULAR_AVERAGE”. It has a bunch of obligatory function declarations that must be completed but also contains 3 numeric variables that will help us do the calculation.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE OR REPLACE TYPE U_CIRCULAR_AVG AS OBJECT ( running_sum_cos_n NUMBER, -- running sum of the cosine of the numbers passed running_sum_sin_n NUMBER, -- running sum of the sine of the numbers passed running_count NUMBER, -- count of the numbers passed STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT U_CIRCULAR_AVG) RETURN NUMBER, MEMBER FUNCTION ODCIAggregateIterate(self IN OUT U_CIRCULAR_AVG, value IN NUMBER) RETURN NUMBER, MEMBER FUNCTION ODCIAggregateTerminate(self IN U_CIRCULAR_AVG, returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER, MEMBER FUNCTION ODCIAggregateMerge(self IN OUT U_CIRCULAR_AVG, ctx2 IN U_CIRCULAR_AVG) RETURN NUMBER ); |

The breakdown of the above is as follows, but keep in mind that the method used to average a set of numbers is to add them all together and then divide by the number in the set, i.e.

1 |
(1+2+3)/3 = 2 |

So all we need to do it iterate over the values in the set, keep a running sum of them and a running count of how many values there have been so we can divide the sum by that number at the end.

The following functions need to be placed inside of the implementation of your type body, which I’ll show you the outline of here, but won’t fill in with the functions completely so that I can explain them one by one.

1 2 3 |
CREATE OR REPLACE TYPE BODY U_CIRCULAR_AVG IS -- Create the functions here END; |

When the object is initialised, it will run the content of ODCIAggregateInitialise. We use this to initialise the three numeric variables that we defined with the value 0 and to instantiate the object.

1 2 3 4 5 6 |
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT U_CIRCULAR_AVG) RETURN NUMBER IS BEGIN SCTX := U_CIRCULAR_AVG(0, 0, 0); RETURN ODCIConst.Success; END; |

As the function iterates over each value in the set, ODCIAggregateIterate is called. This is where the running totals and value count are recorded. The input is accepted in degrees and converted to radians for the SIN() and COS() functions, which in Oracle only accept radians as inputs.

1 2 3 4 5 6 7 8 9 10 |
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT U_CIRCULAR_AVG, value IN NUMBER) RETURN NUMBER IS BEGIN SELF.running_sum_cos_n := SELF.running_sum_cos_n + COS(value*3.14159265359/180); SELF.running_sum_sin_n := SELF.running_sum_sin_n + SIN(value*3.14159265359/180); SELF.running_count := SELF.running_count + 1; RETURN ODCIConst.Success; END; |

When we have iterated over all values, ODCIAggregateTerminate is called. We use this to divide the sum of all values (for both sin and cos), and then convert back to polar coordinates. Finally we normalise the result so that it’s between 0 and 360.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
MEMBER FUNCTION ODCIAggregateTerminate(self IN U_CIRCULAR_AVG, returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER IS avg_c number; avg_s number; n number; BEGIN avg_c := SELF.running_sum_cos_n / SELF.running_count; avg_s := SELF.running_sum_sin_n / SELF.running_count; n := ATAN2(avg_s, avg_c) * 180 / 3.14159265359; IF n >= 0 THEN returnValue := n; ELSE returnValue := n + 360; END IF; RETURN ODCIConst.Success; END; |

The final function in the TYPE is ODCIAggregateMerge, which entirely optional. It’s used to tell Oracle how to handle the values if it is performing an execution in parallel. This function is called to merge the results before ODCIAggregateTerminate is called on the entire set.

1 2 3 4 5 6 7 8 9 10 |
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT U_CIRCULAR_AVG, ctx2 IN U_CIRCULAR_AVG) RETURN NUMBER IS BEGIN SELF.running_sum_cos_n := SELF.running_sum_cos_n + ctx2.running_sum_cos_n; SELF.running_sum_sin_n := SELF.running_sum_sin_n + ctx2.running_sum_sin_n; SELF.running_count := SELF.running_count + ctx2.running_count; RETURN ODCIConst.Success; END; |

So, all of the above would be established as a part of your TYPE. Now all you need is a function to use it.

1 2 |
CREATE FUNCTION CIRCULAR_AVG (input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING U_CIRCULAR_AVG; |

Now you can call your CIRCULAR_AVG function to do the heavy lifting!

1 2 3 4 5 |
SELECT circular_avg(val) FROM test; CIRCULAR_AVG(X) --------------- 0 |

Download the full SQL for this Oracle circular average aggregate function, or check out the Oracle Database documentation for an overview of user aggregate functions.