Primary menu

Post navigation

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

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 ORREPLACE TYPE U_CIRCULAR_AVG ASOBJECT

(

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

STATICFUNCTIONODCIAggregateInitialize(sctx INOUT U_CIRCULAR_AVG)

RETURNNUMBER,

MEMBER FUNCTIONODCIAggregateIterate(selfINOUT U_CIRCULAR_AVG,

value INNUMBER)RETURNNUMBER,

MEMBER FUNCTIONODCIAggregateTerminate(selfINU_CIRCULAR_AVG,

returnValue OUT NUMBER,flags INNUMBER)RETURNNUMBER,

MEMBER FUNCTIONODCIAggregateMerge(selfINOUT U_CIRCULAR_AVG,

ctx2 INU_CIRCULAR_AVG)RETURNNUMBER

);

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 ORREPLACE 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

STATICFUNCTIONODCIAggregateInitialize(sctx INOUT U_CIRCULAR_AVG)

RETURNNUMBER IS

BEGIN

SCTX:=U_CIRCULAR_AVG(0,0,0);

RETURNODCIConst.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 FUNCTIONODCIAggregateIterate(selfINOUT U_CIRCULAR_AVG,

value INNUMBER)RETURNNUMBER 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;

RETURNODCIConst.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 FUNCTIONODCIAggregateTerminate(selfINU_CIRCULAR_AVG,

returnValue OUT NUMBER,flags INNUMBER)RETURNNUMBER IS

avg_c number;

avg_s number;

nnumber;

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;

IFn>=0THEN

returnValue:=n;

ELSE

returnValue:=n+360;

ENDIF;

RETURNODCIConst.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 FUNCTIONODCIAggregateMerge(selfINOUT U_CIRCULAR_AVG,

ctx2 INU_CIRCULAR_AVG)RETURNNUMBER 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;

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