我得到的角度是向北的,但我的正北方是90度,而我从你的点中得到的角度大约是84度,因此稍微往东走一点就可以朝北了。
注意:这可能不正确,只是尝试。
我首先使用split来获取单独的点,然后使用xml来获取每个东部和北部点,
然后重新排列数据,以便我可以在同一行上看到每个点与下一个点。
然后计算每个点之间的差值和方位角,最后求平均值。希望这有所帮助。
declare @data nvarchar(max)='-100.06372406847015 25.4583895045113,-95.14184906847015 30.197590679284158,-100.67895844347015 33.335334871825495,-95.40552094347015 36.576044042090466,-100.59106781847015 39.14304403636714,-95.40552094347015 41.61974326920709,-100.15161469347015 43.56067943577098,-95.66919281847015 45.87106856382069,-110.08325531847015 48.44030667059785,-85.38598969347015 48.73100364391479'
declare @points table (seq int,x float,y float)
;with points as (select ROW_NUMBER() over (order by (select 1) desc) seq,* from string_split(@data,','))
,xmldata as (select points.seq,CONVERT(XML,'<Points><Point>'+ REPLACE(points.value,' ', '</Point><Point>') + '</Point></Points>') AS xmldataPoints
from points),pointXY as (
SELECT seq,
xmldata.xmldataPoints.value('/Points[1]/Point[1]','float') AS [x],
xmldata.xmldataPoints.value('/Points[1]/Point[2]','float') AS [y]
FROM xmldata
),nextPoint as (
select *,LEAD(x,1,null) over (order by seq) x2,LEAD(y,1,null) over (order by seq) y2 from pointXY
),delta as (
select *,(x2-x) dNorth,(y2-y) dEast from nextPoint
),bearing as (
select * ,
DEGREES( IIF(dEast=0,
IIF(dNorth<0,PI(),0),
IIF(dEast<0,(-aTan(dNorth / dEast) + PI() / 2.0+ PI()),(-aTan(dNorth / dEast) + PI() / 2.0)))) bearing
from delta
)
select AVG(bearing) from bearing
结果
84.5262691250142