更新时间:2022-02-22 22:49:53
Since you're using SQL Server 2008, you have the geography
data type available, which is designed for exactly this kind of data:
DECLARE @source geography = 'POINT(0 51.5)'
DECLARE @target geography = 'POINT(-3 56)'
SELECT @source.STDistance(@target)
给予
----------------------
538404.100197555
(1 row(s) affected)
告诉我们,从(伦敦附近)到(爱丁堡附近)约538公里.
Telling us it is about 538 km from (near) London to (near) Edinburgh.
自然,首先需要学习很多东西,但是一旦您知道,这比实现自己的Haversine计算要容易得多.再加上您还有很多功能.
Naturally there will be an amount of learning to do first, but once you know it it's far far easier than implementing your own Haversine calculation; plus you get a LOT of functionality.
如果要保留现有数据结构,仍可以使用STDistance
,方法是使用
If you want to retain your existing data structure, you can still use STDistance
, by constructing suitable geography
instances using the Point
method:
DECLARE @orig_lat DECIMAL(12, 9)
DECLARE @orig_lng DECIMAL(12, 9)
SET @orig_lat=53.381538 set @orig_lng=-1.463526
DECLARE @orig geography = geography::Point(@orig_lat, @orig_lng, 4326);
SELECT *,
@orig.STDistance(geography::Point(dest.Latitude, dest.Longitude, 4326))
AS distance
--INTO #includeDistances
FROM #orig dest