在SQL Server 2008R2中,是否有一种方法可以知道一个点位于另一个点的南部、东部等位置?
例如,我有一个起点point(lat1,lng1)
,我想知道point(lat2,lng2)
相对于该起点的位置:北、西等。
我正在尝试构建一张风向图,这可能对我很有用。
在SQL Server 2008R2中,是否有一种方法可以知道一个点位于另一个点的南部、东部等位置?
例如,我有一个起点point(lat1,lng1)
,我想知道point(lat2,lng2)
相对于该起点的位置:北、西等。
我正在尝试构建一张风向图,这可能对我很有用。
我想出了一种使用标准 SQL 函数相当简单的计算方位角的方法。 ATAN 函数完成了大部分实际工作;两个 CASE 语句只是特殊情况的修正。1 是源,2 是目的地。
atan(([Longitude2]-[Longitude1])/(10e-10+[Latitude2]-[Latitude1]))*360/pi()/2
+case when [Latitude2]<[Latitude1] then 180 else 0 end
+case when [Longitude2]<[Longitude1] and [Latitude2]>[Latitude1] then 360 else 0 end
CREATE FUNCTION [dbo].[CalculateBearing]
(
@pointA as geography
,@pointB as geography
)
RETURNS decimal(18,12)
AS
BEGIN
-- Declare the return variable
DECLARE @bearing decimal(18,12)
-- Declare the local variables
DECLARE @x decimal(18,12)
DECLARE @y decimal(18,12)
DECLARE @dLat decimal(18,12)
DECLARE @dLong decimal(18,12)
DECLARE @rLat1 decimal(18,12)
DECLARE @rLat2 decimal(18,12)
IF(@pointA.STIsEmpty() = 1 OR @pointB.STIsEmpty() = 1)
set @bearing = null
ELSE
BEGIN
-- Calculate delta between coordinates
SET @dLat = RADIANS(@pointB.Lat - @pointA.Lat)
SET @dLong = RADIANS(@pointB.Long - @pointA.Long)
-- Calculate latitude as radians
SET @rLat1 = RADIANS(@pointA.Lat)
SET @rLat2 = RADIANS(@pointB.Lat)
SET @y = SIN(@dLong)*COS(@rLat2)
SET @x = COS(@rLat1)*SIN(@rLat2)-SIN(@rLat1)*COS(@rlat2)*COS(@dLong)
IF (@x = 0 and @y = 0)
SET @bearing = null
ELSE
BEGIN
SET @bearing = CAST((DEGREES(ATN2(@y,@x)) + 360) as decimal(18,12)) % 360
END
END
-- Return the result of the function
RETURN @bearing
END
GO
在此之后,您可以像这样使用此函数:
DECLARE @pointA as geography
DECLARE @pointB as geography
SET @pointA = geography::STGeomFromText('POINT(3 45)', 4326)
SET @pointB = geography::STGeomFromText('POINT(4 47)', 4326)
SELECT [dbo].[CalculateBearing](@pointA, @pointB)
更新: 添加模式
CREATE FUNCTION [dbo].[CalculateCardinalDirection]
(
@pointA as geography
,@pointB as geography
)
RETURNS varchar(2)
AS
BEGIN
DECLARE @bearing decimal(18,12)
-- Bearing calculation provided by https://dev59.com/WG7Xa4cB1Zd3GeqPmidS#14781032
SELECT @bearing = dbo.CalculateBearing(@pointA, @pointB)
RETURN CASE WHEN @bearing BETWEEN 0 AND 22.5 THEN 'N'
WHEN @bearing BETWEEN 22.5 AND 67.5 THEN 'NE'
WHEN @bearing BETWEEN 67.5 AND 112.5 THEN 'E'
WHEN @bearing BETWEEN 112.5 AND 157.5 THEN 'SE'
WHEN @bearing BETWEEN 157.5 AND 202.5 THEN 'S'
WHEN @bearing BETWEEN 202.5 AND 247.5 THEN 'SW'
WHEN @bearing BETWEEN 247.5 AND 292.5 THEN 'W'
WHEN @bearing BETWEEN 292.5 AND 337.5 THEN 'NW'
ELSE 'N' -- Catches NULL bearings and the 337.5 to 360.0 range
END
END
GO
X=X2-X1
和 Y=Y2-Y1。
这是一个公式,可以计算出从0度(正Y轴)开始顺时针旋转的方位角,单位为360度。
f(X,Y)=180-90*(1+SIGN(Y))*(1-SIGN(X^2))-45*(2+SIGN(Y))*SIGN(X)-180/PI()*SIGN(Y*X)*ATAN((ABS(Y)-ABS(X))/(ABS(Y)+ABS(X)))