question

AndrewMeador-0563 avatar image
0 Votes"
AndrewMeador-0563 asked AndrewMeador-0563 edited

T-SQL Math problem in a Select statement

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!






sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered AndrewMeador-0563 edited

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 ‘( )’.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Excellent!

The first version requires casting as well since modulus won't work with the decimals - but I did get that approach to work before you posted your answer. But the second version works and I think will be faster since it won't require 2 casts and the additional math to make the first version work. I'll do some testing to see which is the faster. This is what the body of the calculation looked like that I had done:

 .
 .
 .
  (cast((cast(((DEGREES(
                 ATN2(SIN(RADIANS(L.Longitude) - RADIANS(I.Longitude)) *
                 COS(RADIANS(L.Latitude)), (COS(RADIANS(I.Latitude)) *
                 SIN(RADIANS(L.Latitude))) - (SIN(RADIANS(I.Latitude)) *
                 COS(RADIANS(L.Latitude)) * COS(RADIANS(I.Longitude)
                 - RADIANS(L.Longitude)))))) + 360) * 100 as int) % 36000) as decimal(5,0))
                 / 100)
 .
 .
 .

The second version (cross apply) also handles all the Location to Location combinations as well!

Awesome! Thanks!

0 Votes 0 ·