none
Vector Average function in T-SQL RRS feed

  • Question

  • Hi - We need to calculate the Vector Average of the Wind direction based on Wind speed in SQL Server. Normal Average function is not suitable for us.  Has anyone earlier worked similar requirement.  Please let me know how you have solved it.

    thanks

    

    Tuesday, April 7, 2015 6:14 AM

Answers

  • Yep.

    DECLARE @windDir TABLE (degrees FLOAT, recordedDateTime DATETIME);
    
    insert into @windDir(recordedDateTime,degrees)
    values
    ('2015-04-05 12:00 PM',359),
    ('2015-04-05 12:10 PM',0),
    ('2015-04-05 12:20 PM',357),
    ('2015-04-05 12:30 PM',355),
    ('2015-04-05 12:40 PM',347),
    ('2015-04-05 12:50 PM',339)
    
    
    select * from @windDir;
    
    ;with q as
    (
    	select degrees * PI()/180. radians, recordedDateTime
    	from @windDir
    ), q2 as
    (
    	select x=avg(sin(radians)), y=avg(cos(radians))
    	from q
    ), q3 as
    (
    select case when x>=0 and y>=0 then 0 + atan(x/y) --NE quadrant
                when x>=0 and y<0 then Pi() - atan(x/-y)  --SE quadrant
                when x<0  and y<0 then Pi() + atan(-x/-y) --SW quadrant
                when x<0  and y>=0 then  2*PI() - atan(-x/y)  end avgRadians --NW quadrant
    from q2
    )
    select avgRadians * 180./PI() avgDegrees
    from q3

    outputs

    avgDegrees
    ------------
    352.851709391681

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, April 8, 2015 1:13 PM

All replies

  • Sounds like this is what you're after

    http://www.nullskull.com/a/1390/sql-server-get-average-wind-direction-with-user-defined-aggregate-functions.aspx


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, April 7, 2015 6:26 AM
  • Thank you! But is there any way we can do it using T-SQL functions without using .net stuff.
    Tuesday, April 7, 2015 6:35 AM
  • How do you calculate "Vector Average of the Wind direction"?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, April 7, 2015 1:49 PM
  • It's looking like you need to determine the "side" of the circle you need to be on by looking at the difference between your min and your max. If it's over 180 degrees, we need to go the other way. Using that, we can have a case statement make the changes needed:

    DECLARE @windDir TABLE (degrees FLOAT, recordedDateTime DATETIME)
    INSERT INTO @windDir (degrees, recordedDateTime) VALUES 
    (350, '2015-04-07 08:00:00'),(350, '2015-04-07 08:05:00'),(350, '2015-04-07 08:10:00'),(350, '2015-04-07 08:15:00'),
    (5  , '2015-04-07 08:20:00'),(5  , '2015-04-07 08:25:00'),(350, '2015-04-07 08:30:00'),(5  , '2015-04-07 08:35:00'),
    (5  , '2015-04-07 08:40:00'),(5  , '2015-04-07 08:45:00'),(350, '2015-04-07 08:50:00'),(5  , '2015-04-07 08:55:00'),
    (15 , '2015-04-07 09:00:00'),(15 , '2015-04-07 09:05:00'),(15 , '2015-04-07 09:10:00'),(15 , '2015-04-07 09:15:00'),
    (5  , '2015-04-07 09:20:00'),(5  , '2015-04-07 09:25:00'),(15 , '2015-04-07 09:30:00'),(5  , '2015-04-07 09:35:00'),
    (5  , '2015-04-07 09:40:00'),(5  , '2015-04-07 09:45:00'),(15 , '2015-04-07 09:50:00'),(5  , '2015-04-07 09:55:00')
    
    SELECT CAST(recordedDateTime AS DATE) AS date, DATEPART(HOUR,recordedDateTime) AS hour, CASE WHEN ABS(MAX(degrees) - MIN(degrees)) > 180 THEN CASE WHEN AVG(CASE WHEN (degrees > 180) THEN degrees-360 
                                                                                    ELSE degrees 
    																		   END) < 0 THEN 360+AVG(CASE WHEN (degrees > 180) THEN degrees-360 ELSE degrees END)
                                                                      ELSE AVG(CASE WHEN (degrees > 180) THEN degrees-360 ELSE degrees END)
                                                                 END
               ELSE AVG(degrees)
           END
      FROM @windDir
     GROUP BY CAST(recordedDateTime AS DATE), DATEPART(HOUR,recordedDateTime)
    In future, when you ask a question, please provide some example DDL, data and an expected output. This will help us to help you. When you provide example data, please do so in a table (as I have done at the top of this example).


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.


    Tuesday, April 7, 2015 2:21 PM
  • Patrick, that won't work in every case.  For example if you load @windDir with

    ;With Numbers As
    (Select 0 As Number
    Union All
    Select Number + 1 From Numbers Where Number < 270)
    INSERT INTO @windDir (degrees, recordedDateTime)
    Select Number, DateAdd(second, Number, '2015-04-04T17:00:00') 
    From Numbers
    Option(MaxRecursion 0)

    That is, one entry for every degree from 0 thru 270.  Then the average should be 135.  Your query returns 15.44.

    A straight forward method is to get the SIN and COS of each entry, average those values, then get the ARCTAN of the result.  So,

    ;With cte As
    (Select CAST(recordedDateTime AS DATE) AS date, DATEPART(HOUR,recordedDateTime) AS hour,
    (180./PI()) * ATN2(AVG(SIN(degrees*PI()/180.)), AVG(COS(degrees*PI()/180.))) As Result
      FROM @windDir
     GROUP BY CAST(recordedDateTime AS DATE), DATEPART(HOUR,recordedDateTime))
    Select date, hour, Case When result < 0 Then result + 360 Else result End As result
    From cte;

    Note, the SIN, COS, and ATN2 functions expect the input/output to be in radians, not degrees.  So the input values are multiplied by PI()/180. to convert to radians and the final result is multiplied by 180./PI() to convert back to degrees.

    Tom


    • Edited by Tom Cooper Tuesday, April 7, 2015 5:06 PM
    Tuesday, April 7, 2015 3:56 PM
  • Tom, you are correct. Up to 180 degrees in start and end, it works. The further passed that, the more it breaks down.

    For testing purposes, I modified your numbers cte:

    ;With Numbers As
    (Select -1 As Number
    Union All
    Select Number + 1 From Numbers Where Number < 270)
    INSERT INTO @windDir (degrees, recordedDateTime)
    Select CASE WHEN Number < 0 then number + 360 else number end, DateAdd(second, CASE WHEN Number< 0 then number + 360 else number end, '2015-04-04T17:00:10') 
    From Numbers
    Option(MaxRecursion 0)

    works well to test the top ends (270-90)


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.


    Tuesday, April 7, 2015 4:55 PM
  • I think that's very close.

    With D in degrees clockwise from north, the point (x,y) : x= avg(sin(D)), y= avg(cos(D)) is on the unit circle in the average direction.  The only thing you need to change is the signs of the x and y before taking the atan() and then offsetting it from either PI() or 0.

    eg

    DECLARE @windDir TABLE (degrees FLOAT, recordedDateTime DATETIME);
    
    With Numbers As
    (Select -1 As Number
    Union All
    Select Number + 1 From Numbers Where Number < 80)
    INSERT INTO @windDir (degrees, recordedDateTime)
    Select CASE WHEN Number < 0 then number + 360 else number end, DateAdd(second, CASE WHEN Number< 0 then number + 360 else number end, '2015-04-04T17:00:10') 
    From Numbers
    Option(MaxRecursion 0)
    
    
    select * from @windDir;
    
    ;with q as
    (
    	select degrees * PI()/180. radians, recordedDateTime
    	from @windDir
    ), q2 as
    (
    	select x=avg(sin(radians)), y=avg(cos(radians))
    	from q
    ), q3 as
    (
    select case when x>=0 and y>=0 then 0 + atan(x/y) --NE quadrant
                when x>=0 and y<0 then Pi() - atan(x/-y)  --SE quadrant
                when x<0  and y<0 then Pi() + atan(-x/-y) --SW quadrant
                when x<0  and y>=0 then  0 - atan(-x/y)  end avgRadians --NW quadrant
    from q2
    )
    select avgRadians * 180./PI() avgDegrees
    from q3

    You can simplify this if you want, but computers don't care and it's clearer if you don't.

    David


    David http://blogs.msdn.com/b/dbrowne/






    Tuesday, April 7, 2015 7:06 PM
  • Thanks all for your response.  Apologies for not keeping the data.  Below is my data for between 12:00PM to 12:50 PM

    DateTime                                        WindSpeed       WindDirection

    ======                                       =======      =========

    5/04/0215 12:00:00 PM                   6                        359

    5/04/2015 12:10:00 PM                   5.5                      0

    5/04/2015 12:20:00 PM                   5.1                     357

    5/04/2015  12:30:00 PM                  4.4                     355

    5/04/2015  12:40:00 PM                  4.7                     347

    5/04/2015  12:50:00 PM                  5.3                     339

    If i do the normal hourly average I will get 292 which is not correct for Wind Direction.   I should get 353 instead of 292. 

    thanks

    

    Wednesday, April 8, 2015 11:10 AM
  • Yep.

    DECLARE @windDir TABLE (degrees FLOAT, recordedDateTime DATETIME);
    
    insert into @windDir(recordedDateTime,degrees)
    values
    ('2015-04-05 12:00 PM',359),
    ('2015-04-05 12:10 PM',0),
    ('2015-04-05 12:20 PM',357),
    ('2015-04-05 12:30 PM',355),
    ('2015-04-05 12:40 PM',347),
    ('2015-04-05 12:50 PM',339)
    
    
    select * from @windDir;
    
    ;with q as
    (
    	select degrees * PI()/180. radians, recordedDateTime
    	from @windDir
    ), q2 as
    (
    	select x=avg(sin(radians)), y=avg(cos(radians))
    	from q
    ), q3 as
    (
    select case when x>=0 and y>=0 then 0 + atan(x/y) --NE quadrant
                when x>=0 and y<0 then Pi() - atan(x/-y)  --SE quadrant
                when x<0  and y<0 then Pi() + atan(-x/-y) --SW quadrant
                when x<0  and y>=0 then  2*PI() - atan(-x/y)  end avgRadians --NW quadrant
    from q2
    )
    select avgRadians * 180./PI() avgDegrees
    from q3

    outputs

    avgDegrees
    ------------
    352.851709391681

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, April 8, 2015 1:13 PM
  • Thanks David! Really appreciate your help!
    Wednesday, April 8, 2015 5:31 PM