使用 GEOGRAPHY 类型
您有重复的字段名称,因此我将它们重命名为 Lat1/Lon1 和 Lat2/Lon2。如果这是联接,请添加别名 a.lat/a.lon b.lat/b.lon。
Example
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
Returns
编辑 - 供您考虑
考虑将“地理”字段添加到源表中。这将消除多余的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