SQL ServeR语言 使用 GEOGRAPHY 从纬度和经度计算初始方位角

Question

最近，在Stackoverflow成员的帮助下，我得到了以下计算两个经纬度点之间距离的解决方案：

GEOGRAPHY::Point(DepartureAirportLatitude, DepartureAirportLongitude, 4326)
        .STDistance(GEOGRAPHY::Point(ArrivalAirportLatitude, ArrivalAirportLongitude, 4326)) AS [Default],
        GEOGRAPHY::Point(DepartureAirportLatitude, DepartureAirportLongitude, 4326)
        .STDistance(GEOGRAPHY::Point(ArrivalAirportLatitude, ArrivalAirportLongitude, 4326)) / 1609.344 AS [Mi],
        GEOGRAPHY::Point(DepartureAirportLatitude, DepartureAirportLongitude, 4326)
        .STDistance(GEOGRAPHY::Point(ArrivalAirportLatitude, ArrivalAirportLongitude, 4326)) / 1000 AS [Km]

有没有一种方法，使用地理功能，计算相同数据点之间的初始方位，并将其显示为航向？

感谢

Matthew

Answer 1

感谢您的回复和信息。

经过几个小时的游戏和大量的谷歌搜索，我使用了以下解决方案，它给了我正确的结果：

DECLARE @Pi AS FLOAT
DECLARE @D2R AS FLOAT
SET @Pi = '3.14159265358979'
SET @D2R = @Pi / 180.0;

PICalculation AS
(
    SELECT FlightNo, DepartureAirportIATA, ArrivalAirportIATA,
        @D2R * DepartureAirportLatitude AS [DepartureAirportLatitude], @D2R * DepartureAirportLongitude AS [DepartureAirportLongitude],
        @D2R * ArrivalAirportLatitude AS [ArrivalAirportLatitude], @D2R * ArrivalAirportLongitude AS [ArrivalAirportLongitude]
    FROM RawData    
),
RadiansCalculation AS
(
    SELECT FlightNo, DepartureAirportIATA, ArrivalAirportIATA,
            Radians(ArrivalAirportLatitude - DepartureAirportLatitude) AS [DLat],
            Radians(ArrivalAirportLongitude - DepartureAirportLongitude) AS [DLon],
            Radians(DepartureAirportLatitude) AS [RLat1],
            Radians(ArrivalAirportLatitude) AS [RLat2]
    FROM PICalculation  
),
XYCalculation AS
(
    SELECT FlightNo, DepartureAirportIATA, ArrivalAirportIATA,
        SIN(DLon)*COS(RLat2) AS [Y],
        COS(RLat1)*SIN(RLat2)-SIN(RLat1)*COS(RLat2)*COS(DLon) AS [X]
    FROM RadiansCalculation
),
HeadingCalculation AS
(
    SELECT FlightNo, DepartureAirportIATA, ArrivalAirportIATA,
        CASE WHEN X = 0 AND Y = 0 THEN 0
            ELSE CAST((DEGREES(ATN2(Y,X)) + 360) AS DECIMAL(5,1)) % 360 
        END AS [InitialBearing]
    FROM XYCalculation
)
SELECT *
FROM DataJoin

希望这个解决方案对其他偶然发现这篇文章的人也有用：）

Matthew

Answer 2

这里有一个标量函数和使用它的测试点

CREATE FUNCTION dbo.Bearing (
  @point1 geography,
  @point2 geography  )
RETURNS float
AS
BEGIN
  DECLARE @Bearing decimal(18,15)
  DECLARE @Lat1 float = Radians(@point1.Lat)
  DECLARE @Lat2 float = Radians(@point2.Lat)
  DECLARE @dLon float = Radians(@point2.Long - @point1.Long)
  IF (@point1.STEquals(@point2) = 1)
    SET @Bearing = NULL
  ELSE
    SET @Bearing = ATN2(
      sin(@dLon)*cos(@Lat2),
     (cos(@Lat1)*sin(@Lat2)) - (sin(@Lat1)*cos(@Lat2)*cos(@dLon))
    )
    SET @Bearing = (Degrees(@Bearing) + 360) % 360
  RETURN @Bearing
END
GO
DECLARE @g  GEOGRAPHY = GEOGRAPHY::Point(43, 43, 4326);
DECLARE @g1 GEOGRAPHY = GEOGRAPHY::Point(42, 43, 4326);
DECLARE @g2 GEOGRAPHY = GEOGRAPHY::Point(44, 43, 4326);
DECLARE @g3 GEOGRAPHY = GEOGRAPHY::Point(43, 42, 4326);
DECLARE @g4 GEOGRAPHY = GEOGRAPHY::Point(43, 44, 4326);
SELECT dbo.[Bearing](@g, @g1),
    [dbo].[Bearing](@g, @g2),
    [dbo].[Bearing](@g, @g3),
    [dbo].[Bearing](@g, @g4);

如果在T-SQL中这样做的速度太慢，那么在该链接上也有几个CLR实现。