计算两点之间的距离(纬度、经度)

2021-12-02 00:00:00 math sql tsql sql-server sql-server-2008-r2

我正在尝试计算地图上两个位置之间的距离.我在我的数据中存储了:经度、纬度、X POS、Y POS.

I am trying to calculate the distance between two positions on a map. I have stored in my data: Longitude, Latitude, X POS, Y POS.

我以前一直在使用以下代码段.

I have been previously using the below snippet.

DECLARE @orig_lat DECIMAL
DECLARE @orig_lng DECIMAL
SET @orig_lat=53.381538 set @orig_lng=-1.463526
SELECT *,
    3956 * 2 * ASIN(
          SQRT( POWER(SIN((@orig_lat - abs(dest.Latitude)) * pi()/180 / 2), 2) 
              + COS(@orig_lng * pi()/180 ) * COS(abs(dest.Latitude) * pi()/180)  
              * POWER(SIN((@orig_lng - dest.Longitude) * pi()/180 / 2), 2) )) 
          AS distance
--INTO #includeDistances
FROM #orig dest

但是我不相信由此产生的数据，它似乎给出了稍微不准确的结果.

I don't however trust the data coming out of this, it seems to be giving slightly inaccurate results.

一些示例数据以备不时之需

Some sample data in case you need it 

Latitude        Longitude     Distance 
53.429108       -2.500953     85.2981833133896

有人能帮我解决我的代码吗，如果你有一种新的方法来实现这一点，我不介意你是否想修复我已经拥有的东西.

Could anybody help me out with my code, I don't mind if you want to fix what I already have if you have a new way of achieving this that would be great.

请说明您的结果采用的计量单位.

Please state what unit of measurement your results are in.

推荐答案

由于您使用的是 SQL Server 2008，因此您拥有 geography 数据类型可用，专为此类数据而设计:

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，通过使用 geography 实例.microsoft.com/en-us/library/bb933811%28v=sql.100%29.aspx">Point 方法:

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

相关文章