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

我正在试图计算地图上两个位置之间的距离。 我已经存储在我的数据: 经度，纬度，X POS，Y POS。

我以前一直在使用下面的代码片段。

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

然而，我并不相信这些数据，它似乎给出了稍微不准确的结果。

一些样本数据，以备不时之需

Latitude        Longitude     Distance
53.429108       -2.500953     85.2981833133896

有人能帮我解决代码的问题吗? 我不介意你们修复我已经有的东西，如果你们有一个新的方法来实现这个，那就太好了。

请说明您的测量结果的单位。

236827 次浏览
小开

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.

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)

小开
最佳答案

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)

Gives

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


(1 row(s) affected)

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

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.

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
小开

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

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

小开 
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)

Outputs:

0,02849639

You can change @R parameter with commented floats.

小开

In addition to the previous answers, here is a way to calculate the distance inside a 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.

小开

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_2 e.g.:

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

提交答案