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

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.

我正在计算地图上两个位置之间的距离。我已经在我的数据中存储:经度、纬度、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.

请说明你的测量结果是什么单位。

6 个解决方案

#1

Since you're using SQL Server 2008, you have the geography data type available, which is designed for exactly this kind of data:

由于您使用的是SQL Server 2008，所以您可以使用地理数据类型，这正是为这类数据设计的:

DECLARE @source geography = 'POINT(0 51.5)'
DECLARE @target geography = 'POINT(-3 56)'

SELECT @source.STDistance(@target)

Gives

给了

----------------------
538404.100197555

(1 row(s) affected)

Telling us it is about 538 km from (near) London to (near) Edinburgh.

告诉我们从伦敦到爱丁堡大约有538公里。

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.

当然，首先会有大量的学习要做，但是一旦你知道了，这比实现你自己的Haversine计算要容易得多;另外，你还能得到很多功能。

---

If you want to retain your existing data structure, you can still use STDistance, by constructing suitable geography instances using the Point method:

如果您希望保留现有的数据结构，仍然可以使用STDistance，方法是使用点方法构造合适的地理实例:

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

#2

The below function gives distance between two geocoordinates in miles

下面的函数给出了以英里为单位的两个地理坐标之间的距离

create function [dbo].[fnCalcDistanceMiles] (@Lat1 decimal(8,4), @Long1 decimal(8,4), @Lat2 decimal(8,4), @Long2 decimal(8,4))
returns decimal (8,4) as
begin
declare @d decimal(28,10)
-- Convert to radians
set @Lat1 = @Lat1 / 57.2958
set @Long1 = @Long1 / 57.2958
set @Lat2 = @Lat2 / 57.2958
set @Long2 = @Long2 / 57.2958
-- Calc distance
set @d = (Sin(@Lat1) * Sin(@Lat2)) + (Cos(@Lat1) * Cos(@Lat2) * Cos(@Long2 - @Long1))
-- Convert to miles
if @d <> 0
begin
set @d = 3958.75 * Atan(Sqrt(1 - power(@d, 2)) / @d);
end
return @d
end 

The below function gives distance between two geocoordinates in kilometres

下面的函数给出了千米内两个地理坐标之间的距离。

CREATE FUNCTION dbo.fnCalcDistanceKM(@lat1 FLOAT, @lat2 FLOAT, @lon1 FLOAT, @lon2 FLOAT)
RETURNS FLOAT 
AS
BEGIN

    RETURN ACOS(SIN(PI()*@lat1/180.0)*SIN(PI()*@lat2/180.0)+COS(PI()*@lat1/180.0)*COS(PI()*@lat2/180.0)*COS(PI()*@lon2/180.0-PI()*@lon1/180.0))*6371
END

The below function gives distance between two geocoordinates in kilometres using Geography data type which was introduced in sql server 2008

下面的函数使用sql server 2008中引入的地理数据类型，给出两个以公里为单位的地理坐标之间的距离

DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);
SET @h = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);
SELECT @g.STDistance(@h);

Usage:

用法:

select [dbo].[fnCalcDistanceKM](13.077085,80.262675,13.065701,80.258916)

Reference: Ref1,Ref2

参考:Ref1 Ref2

#3

As you're using SQL 2008 or later, I'd recommend checking out the GEOGRAPHY data type. SQL has built in support for geospatial queries.

当您使用SQL 2008或更高版本时，我建议您检查地理数据类型。SQL内置了对地理空间查询的支持。

e.g. you'd have a column in your table of type GEOGRAPHY which would be populated with a geospatial representation of the coordinates (check out the MSDN reference linked above for examples). This datatype then exposes methods allowing you to perform a whole host of geospatial queries (e.g. finding the distance between 2 points)

例如，您的类型GEOGRAPHY表中有一个列，该列将使用坐标的地理空间表示形式填充(请查看上面链接的MSDN引用以获得示例)。然后，该数据类型公开了允许您执行一系列地理空间查询的方法(例如，查找两点之间的距离)

#4

Create Function [dbo].[DistanceKM] 
( 
      @Lat1 Float(18),  
      @Lat2 Float(18), 
      @Long1 Float(18), 
      @Long2 Float(18)
)
Returns Float(18)
AS
Begin
      Declare @R Float(8); 
      Declare @dLat Float(18); 
      Declare @dLon Float(18); 
      Declare @a Float(18); 
      Declare @c Float(18); 
      Declare @d Float(18);
      Set @R =  6367.45
            --Miles 3956.55  
            --Kilometers 6367.45 
            --Feet 20890584 
            --Meters 6367450 


      Set @dLat = Radians(@lat2 - @lat1);
      Set @dLon = Radians(@long2 - @long1);
      Set @a = Sin(@dLat / 2)  
                 * Sin(@dLat / 2)  
                 + Cos(Radians(@lat1)) 
                 * Cos(Radians(@lat2))  
                 * Sin(@dLon / 2)  
                 * Sin(@dLon / 2); 
      Set @c = 2 * Asin(Min(Sqrt(@a))); 

      Set @d = @R * @c; 
      Return @d; 

End
GO

Usage:

用法:

select dbo.DistanceKM(37.848832506474, 37.848732506474, 27.83935546875, 27.83905546875)

选择dbo.DistanceKM(37.848832506474, 37.848732506474, 27.83935546875, 27.83905546875)

Outputs:

输出:

0,02849639

0,02849639

You can change @R parameter with commented floats.

可以使用已注释的浮点数更改@R参数。

#5

In addition to the previous answers, here is a way to calculate the distance inside a SELECT:

除了前面的答案之外，这里还有一种方法可以计算SELECT内部的距离:

CREATE FUNCTION Get_Distance
(   
    @La1 float , @Lo1 float , @La2 float, @Lo2 float
)
RETURNS TABLE 
AS
RETURN 
    -- Distance in Meters
    SELECT GEOGRAPHY::Point(@La1, @Lo1, 4326).STDistance(GEOGRAPHY::Point(@La2, @Lo2, 4326))
    AS Distance
GO

Usage:

用法:

select Distance
from Place P1,
     Place P2,
outer apply dbo.Get_Distance(P1.latitude, P1.longitude, P2.latitude, P2.longitude)

Scalar functions also work but they are very inefficient when computing large amount of data.

标量函数也可以工作，但在计算大量数据时效率非常低。

I hope this might help someone.

我希望这能帮助某人。

#6

It looks like Microsoft invaded brains of all other respondents and made them write as complicated solutions as possible. Here is the simplest way without any additional functions/declare statements:

看起来微软侵入了所有其他受访者的大脑，让他们尽可能写出复杂的解决方案。这是最简单的方法，不需要任何附加函数/声明语句:

SELECT geography::Point(LATITUDE_1, LONGITUDE_1, 4326).STDistance(geography::Point(LATITUDE_2, LONGITUDE_2, 4326))

选择地理位置:点(LATITUDE_1 LONGITUDE_1,4326)。STDistance(地理:点(LATITUDE_2 LONGITUDE_2 4326))

Simply substitute your data instead of LATITUDE_1, LONGITUDE_1, LATITUDE_2, LONGITUDE_2 e.g.:

简单地用你的数据代替LATITUDE_1、纵向1、纵向2、纵向2，例如:

SELECT geography::Point(53.429108, -2.500953, 4326).STDistance(geography::Point(c.Latitude, c.Longitude, 4326))
from coordinates c