使用GEOGRAPHY类型
您有重复的字段名称,所以我将它们重命名为Lat1/Lon1和Lat2/Lon2。如果这是一个连接操作,请添加别名a.lat/a.lon b.lat/b.lon。
示例
Declare @YourTable Table ([StationNum] varchar(50),[lat1] float,[lon1] float,[Zip] varchar(50),[lat2] float,[lon2] float)
Insert Into @YourTable Values
(123,34.66,98.32,12345,33.78,91.91)
,(456,33.03,96.8,23456,35.23,92.23)
,(789,32.29,96.85,34567,33.09,92.68)
Select *
,InMeters = GEOGRAPHY::Point([Lat1], [Lon1], 4326).STDistance(GEOGRAPHY::Point([Lat2], [Lon2], 4326))
,InMiles = GEOGRAPHY::Point([Lat1], [Lon1], 4326).STDistance(GEOGRAPHY::Point([Lat2], [Lon2], 4326)) / 1609.344
from @YourTable
返回
![在此输入图片描述](https://istack.dev59.com/qio6j.webp)
编辑 - 供参考
考虑向您的源表添加一个地理字段。这将消除冗余的GEOGRAPHY::Point()
函数调用。
Update YourTable Set GeoPoint = GEOGRAPHY::Point([Lat], [Lon], 4326)
那么距离的计算公式就是:
,InMeters = A.GeoPoint.STDistance(B.GeoPoint)
,InMiles = A.GeoPoint.STDistance(B.GeoPoint) / 1609.344