问题描述
我正在尝试计算地图上两个位置之间的距离.我在我的数据中存储了:经度、纬度、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
这篇关于计算两点之间的距离(纬度、经度)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！