Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server 2008 R2 Geography Distance?

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)

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

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

What could be causing this?

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.

Thanks!

Edit:

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.

like image 767
SaphuA Avatar asked Jun 17 '11 13:06

SaphuA


People also ask

What is difference between geography and geometry in SQL Server?

The connecting edge between two vertices in a geometry type is a straight line. However, the connecting edge between two vertices in a geography type is a short great elliptic arc between the two vertices.

Is SQL Server 2008 R2 free?

Microsoft SQL Server 2008 R2 Express with Service Pack 2 is a free, feature-rich edition of SQL Server that is ideal for learning, developing, powering desktop, web & small server applications, and for redistribution by ISVs.

What is geography in SQL Server?

The geography spatial data type, geography, is implemented as a . NET common language runtime (CLR) data type in SQL Server. This type represents data in a round-earth coordinate system. The SQL Server geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.


1 Answers

Try a structure like this.

DECLARE @a geography, @b geography
SET @a = geography::Point(51.925637, 4.493408,4326)
SET @b= geography::Point(51.542919, 4.77356,4326)
SELECT @a.STDistance(@b)
like image 119
Bob Probst Avatar answered Oct 02 '22 19:10

Bob Probst