SQL Server 2008 R2的地理距离是多少？

问题描述

我第一次尝试使用SQL Server 2008 R2的空间特征，所以请裸跟我来。

First time I'm trying to use the spatial features of SQL Server 2008 R2, so please bare with me.

我已经创建了一个表具有以下列：

I have created a table with the following columns:

Text:varchar(255)
Location:geography

它们包含一些城市的来自荷兰的数据（得到了谷歌地图的坐标）：

They contain a few city's from The Netherlands as data (got the coordinates from google maps):

Rotterdam - POINT (51.925637 4.493408 4326)
Utrecht - POINT (52.055868 5.103149 4326)
Nijmegen - POINT (51.801822 5.828247 4326)
Breda - POINT (51.542919 4.77356 4326)

我想知道在DB鹿特丹的所有城市之间的距离，让我执行此查询：

I want to know the distance between all city's in the DB from Rotterdam, so I perform this query:

Select
    Text, Location,
    Location.STDistance(geography::Point(51.925638, 4.493408, 4326)) as Distance
from Messages

但作为一个结果，我获得的距离接近680的每一个城市。

But as a result I get a distance close to 6800000 for every city.

什么引起的？

我能想到的唯一原因是，我使用了错误的SRID，但我不能找出哪一个，我应该使用来代替。

The only reason I can think of is that I'm using the wrong SRID, but I can't figure out which one I should use instead.

谢谢！

编辑：

只是为了它赫克我去的玩数字游戏，我得到了一些怪异的结果：

Just for the heck of it I went playing with the numbers and I got some weird results:

Distance from Rotterdam to Rotterdam: 6828459.57 (A) (weird but true)
Distance from Rotterdam to Breda: 6779956.10 (B)
Distance from Rotterdam to Nijmegen: 6695336.38 (C)

现在这里就是它变得有趣：

Now here's where it get interesting:

(A) - (B) = 48504 m = 48 km
(A) - (C) = 133123 m = 133 km

这些值是大约这些城市之间的距离。

These values are roughly the distances between these city's.

推荐答案

根据本次测试情况下，它似乎工作得很好：

According this test case it seems to work just fine:

DECLARE @rotterdam geography = geography::Point(51.925637, 4.493408,4326);
with tmp(txt, geo)
as
  (
   select 'Rotterdam',geography::Point(51.925637, 4.493408,4326)
   UNION ALL
   select 'Utrecht',geography::Point(52.055868, 5.103149,4326)
   UNION ALL
   select 'Nijmegen',geography::Point(51.801822, 5.828247,4326)
   UNION ALL
  select 'Breda',geography::Point(51.542919, 4.77356,4326)
  )
  SELECT t.txt, t.geo.STDistance(geography::Point(51.925637, 4.493408,4326)) from tmp t

所以，你的实际的查询看起来不错，这让我怀疑的问题是由于在表中不正确的数据。你能否确认数据被正确地存储在表中？

So your actual query looks fine, which makes me wonder if the problem is due to incorrect data in your table.Can you confirm that the data is correctly stored in the table?

此外，我建议存储，你在一个单独的值作为@TrickyNixons例如比较地理价值。

Also I recommend storing the geography value that you compare with in a separate value as in @TrickyNixons example.

这篇关于SQL Server 2008 R2的地理距离是多少？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！