Calculate the circular mean of a set of angles in Oracle using SQL

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°.

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.

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.

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.

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.

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.

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.

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.

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

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

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.

Leave a Reply

Your email address will not be published. Required fields are marked *