如何在单个T-SQL查询中计算两个WGS84坐标之间的方位角(与北方的夹角)? [英] How do I calculate the Azimuth (angle to north) between two WGS84 coordinates in a single T-SQL query?

问题描述

我在C#中找到了该问题的解决方案，但是由于我的C#实现需要分支(如果不是这样)，则无法将其转换为单个查询T-SQL.

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

我还发现了以下C#解决方案，可以将其转换为单个查询T-SQL，但不会产生正确的结果

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))))); 
}

来自有人可以更正上面的代码或提供替代解决方案吗?

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

推荐答案

用CASE表达式替换ifs:

Replace ifs with CASE expressions:

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

替换为:

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

使用嵌套选择替换连续ifs:

replace consecutive ifs with nested selects:

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

替换为

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

这篇关于如何在单个T-SQL查询中计算两个WGS84坐标之间的方位角(与北方的夹角)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！