8

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.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
SaphuA
  • 3,092
  • 3
  • 39
  • 58

2 Answers2

4

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)
Bob Probst
  • 9,533
  • 8
  • 32
  • 41
2

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?

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

Tomas
  • 3,573
  • 2
  • 20
  • 25
  • I'm still not sure what I did wrong, but your code works so I'll go from that. Thanks =) – SaphuA Jun 17 '11 at 21:17
  • 2
    @SaphuA You're welcome. As a sidenote be VERY carefull of using a spatial index on a nullable GEOGRAPHY datatype column. There are some serious performance issue, so make that GEOGRAPHY column non-nullable even if you have to remodel your schema. – Tomas Jun 18 '11 at 11:18
  • 1
    Tomas' comment about performance issues with a spatial index on nullable geography types is great. More info here: http://stackoverflow.com/questions/4954875/sql-server-2008-performance-on-nullable-geography-column-with-spatial-index – TTT Mar 24 '14 at 15:28