SQL 计算两点之间的距离（纬度、经度）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13026675/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculating distance between two points (Latitude, Longitude)
提问by Waller
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.
请说明您的结果采用的计量单位。
回答by AakashM
Since you're using SQL Server 2008, you have the geographydata type available, which is designed for exactly this kind of data:
由于您使用的是 SQL Server 2008，因此您有geography可用的数据类型，它专为此类数据而设计：
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 geographyinstances using the Pointmethod:
如果您想保留现有的数据结构，您仍然可以使用STDistance，通过geography使用以下Point方法构造合适的实例：
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
回答by Durai Amuthan.H
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 kilometresusing Geographydata type which was introduced in sql server 2008
下面的函数 使用sql server 2008 中引入的Geography数据类型给出两个地理坐标之间的距离（以公里为单位）
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)
回答by Stalinko
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))
Simply substitute your data instead of LATITUDE_1, LONGITUDE_1, LATITUDE_2, LONGITUDE_2e.g.:
只需替换您的数据而不是LATITUDE_1, LONGITUDE_1, LATITUDE_2，LONGITUDE_2例如：
SELECT geography::Point(53.429108, -2.500953, 4326).STDistance(geography::Point(c.Latitude, c.Longitude, 4326))
from coordinates c
回答by AdaTheDev
As you're using SQL 2008 or later, I'd recommend checking out the GEOGRAPHYdata type. SQL has built in support for geospatial queries.
当您使用 SQL 2008 或更高版本时，我建议您查看GEOGRAPHY数据类型。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 参考以获取示例）。然后，此数据类型公开允许您执行大量地理空间查询的方法（例如，查找 2 点之间的距离）
回答by Fatih K.
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 参数。
回答by Thurfir
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.
我希望这可以帮助某人。
