T-SQL Math problem in a Select statement

Andrew Meador 156 Reputation points
2021-04-14T11:53:44.753+00:00

Sorry, I can't think of how to title what I'm trying to do - same reason I can't find a solution and am asking here.

I have a insert into...select statement that is performing a math calculation - but it could potentially frequently be calling it hundreds of thousands of times - or even into the millions of times. So I need a way to adjust the resulting value - preferably without doing the whole calculation again for the minor adjustment.

Here is the select statement as is:

declare @NewLocationID int
declare @iLatFrom decimal(18,8)
declare @iLongFrom decimal(18,8)

select @NewLocationID = inserted.ID,
 @iLatFrom = inserted.Latitude,
 @iLongFrom = inserted.Longitude
from inserted

insert into juncLocationLocation
        select @NewLocationID, L.ID,
         (DEGREES(
         ATN2(SIN(RADIANS(L.Longitude) - RADIANS(@iLongFrom)) *
         COS(RADIANS(L.Latitude)), (COS(RADIANS(@iLatFrom)) *
         SIN(RADIANS(L.Latitude))) - (SIN(RADIANS(@iLatFrom)) *
         COS(RADIANS(L.Latitude)) * COS(RADIANS(@iLongFrom) - RADIANS(L.Longitude))))))
         as [Bearing]
        from [Location] L where L.ID <> @NewLocationID

The equation above results in a negative value if the Bearing is more than 180°. So I want to add 360 to the result whenever the result is negative, but I don't want to do something like:

insert into juncLocationLocation
 select @NewLocationID, L.ID,
 (case when DEGREES(
     ATN2(SIN(RADIANS(L.Longitude) - RADIANS(@iLongFrom)) *
     COS(RADIANS(L.Latitude)), (COS(RADIANS(@iLatFrom)) *
     SIN(RADIANS(L.Latitude))) - (SIN(RADIANS(@iLatFrom)) *
     COS(RADIANS(L.Latitude)) * COS(RADIANS(@iLongFrom) - RADIANS(L.Longitude))))) < 0
 then 
 DEGREES(
     ATN2(SIN(RADIANS(L.Longitude) - RADIANS(@iLongFrom)) *
     COS(RADIANS(L.Latitude)), (COS(RADIANS(@iLatFrom)) *
     SIN(RADIANS(L.Latitude))) - (SIN(RADIANS(@iLatFrom)) *
     COS(RADIANS(L.Latitude)) * COS(RADIANS(@iLongFrom) - RADIANS(L.Longitude))))) + 360
 else
 DEGREES(
     ATN2(SIN(RADIANS(L.Longitude) - RADIANS(@iLongFrom)) *
     COS(RADIANS(L.Latitude)), (COS(RADIANS(@iLatFrom)) *
     SIN(RADIANS(L.Latitude))) - (SIN(RADIANS(@iLatFrom)) *
     COS(RADIANS(L.Latitude)) * COS(RADIANS(@iLongFrom) - RADIANS(L.Longitude))))))
 as [Bearing]
from [Location] L where L.ID <> @NewLocationID

As this would result in the equation being called twice - once for the negative value check and then to get the then/else result into the select statement as Bearing.

Is there some way to calculate the result - and then based on the answer to just add 360 to the result (if negative) and then return the modified result - so this equation is only done once?

I'm trying to improve the speed at which this process is done - and thus not wanting to calculate this twice on every insert iteration

Thanks!

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 110.7K Reputation points
    2021-04-14T12:24:46.21+00:00

    Try two statements:

    insert into juncLocationLocation  
             select @NewLocationID, L.ID,  
              ((DEGREES(  
              ATN2(SIN(RADIANS(L.Longitude) - RADIANS(@iLongFrom)) *  
              COS(RADIANS(L.Latitude)), (COS(RADIANS(@iLatFrom)) *  
              SIN(RADIANS(L.Latitude))) - (SIN(RADIANS(@iLatFrom)) *  
              COS(RADIANS(L.Latitude)) * COS(RADIANS(@iLongFrom) - RADIANS(L.Longitude))))))) + 360) % 360  
              as [Bearing]  
             from [Location] L where L.ID <> @NewLocationID  
    

    or

    insert into juncLocationLocation  
             select @NewLocationID, L.ID,  
             iif( d < 0, d + 360, d) as [Bearing]  
             from [Location] L   
             cross apply (values ( (DEGREES(  
          ATN2(SIN(RADIANS(L.Longitude) - RADIANS(@iLongFrom)) *  
          COS(RADIANS(L.Latitude)), (COS(RADIANS(@iLatFrom)) *  
          SIN(RADIANS(L.Latitude))) - (SIN(RADIANS(@iLatFrom)) *  
          COS(RADIANS(L.Latitude)) * COS(RADIANS(@iLongFrom) - RADIANS(L.Longitude)))))) )) t(d)  
             where L.ID <> @NewLocationID  
    

    Check if there are enough ‘( )’.


0 additional answers

Sort by: Most helpful