views:

894

answers:

4

I found the solution for this question in C#, but I can't translate it to a single query T-SQL, since my C# implementation requires branching (if then else).

I also found the following C# solution, which could be translated to a single query T-SQL but it doesn't produce the correct results

public static double GetAzimuth(WGSCoord c1, WGSCoord c2) { 
     var lat1 = DegToRad(c1.Latitude); 
     var lon1 = DegToRad(c1.Longitude); 
     var lat2 = DegToRad(c2.Latitude); 
     var lon2 = DegToRad(c2.Longitude);

     return RadToDeg(Math.Asin(Math.Sin(lon1 – lon2) * Math.Cos(lat2) / Math.Sin(Math.Acos(Math.Sin(lat2) * Math.Sin(lat1) + Math.Cos(lat1) * Math.Cos(lat2) * Math.Cos(lon2 – lon1))))); 
}

Code from Tamir Khason – Just code

Could someone correct the code above or provide an alternate solution?

+1  A: 

Have you considered creating an assembly with a SP in C# for sql server? Thats the route I'd probably go.

Chris Klepeis
would you have a link to a better tutorial?
Jader Dias
sure do. http://chiragrdarji.wordpress.com/2008/03/11/clr-stored-procedure-in-sql-server-2005/
Chris Klepeis
+1  A: 

In T-SQL, you could use the CASE expression

e.g.

SELECT ...
CASE 
    WHEN latD = 0 AND longD < 0 THEN ....
    WHEN latD < 0 AND longD = 0 THEN ....

etc.

Jeremy Smyth
+3  A: 

Replace ifs with CASE expressions:

   if (latitudinalDifference == 0)
            {
                if (longitudinalDifference != 0)
                {
                    azimuth = Math.PI / 2d;
                }
            }

replace with:

SELECT CASE WHEN @latitudinalDifference = 0 AND @longitudinalDifference <> 0 THEN ...
 ELSE ... END AS azimuth

replace consecutive ifs with nested selects:

if(some condition)
{
  i=1; 
}
else
{
 i=2;
}
if(some other condition)
{
  i++; 
}

replace with

SELECT i + CASE WHEN (some other condition) THEN 1 ELSE 0 END
FROM(
SELECT CASE WHEN (some condition) THEN 1 ELSE 2 END AS i
) AS t
AlexKuznetsov
+2  A: 

There's quite a lot of the necessary spherical trigonometry in the answer to SO 389211. Copying and modifying what I wrote there:

Consider a sperical triangle with angles A, B, C at the vertices and sides a, b, c opposite those vertices (that is, side a is from B to C, etc.). Applying this to the problem, we can call the two points given B and C, and we create a right spherical triangle with a right angle at A.

Consider this diagram:

                  + C
                 /|
                / |
            a  /  | b
           |  /   |
           |X/    |
           |/     |
         B +------+ A
              c

You are given two points B and C, and you want to determine the angle X = 90º - B. The side c is equal to the difference in longitude, Δλ; the side b is equal to the difference in latitude, Δφ; the angle A is 90º, so sin A = 1 and cos A = 0. To determine X, we want the value of B given b, c and A.

Looking at the problem from first principles, we need the two main spherical trigonometry equations:

  1. The Sine Formula:

    sin A   sin B   sin C
    ----- = ----- = -----
    sin a   sin b   sin c
    
  2. The Cosine Formula:

    cos a = cos b . cos c + sin b . sin c . cos A
    

Therefore, I believe an equation for a is:

cos a = cos Δλ . cos Δφ + sin Δλ . sin Δφ . cos 90º

a = arccos (cos Δλ . cos Δφ)

Given a, b and A, we can use the Sine Formula to determine B:

sin a   sin b
----- = ----
sin A   sin B

Or

        sin b . sin A
sin B = -------------
            sin a

Or, since A = 90º, sin A = 1, and sin B = sin (90º - X) = cos X:

        sin b
cos X = -----
        sin a

I rather suspect that if I bent my mind to it (or you bent your mind to it), you could come up with an answer that didn't involve using arccos followed by sin.

Jonathan Leffler
Good stuff. Thanks.
Scott