计算两点之间的距离(纬度，经度) [英] Calculating distance between two points (Latitude, Longitude)

问题描述

我正在尝试计算地图上两个位置之间的距离. 我已经在数据中存储了:经度，纬度，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，因此具有

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

这篇关于计算两点之间的距离(纬度，经度)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！