CODEWITHSUNDEEP
sql-server
LT999
LT999

Reputation: 47

sort by distance using latitude and longitude

I am trying to build a store locator, and am having trouble forming my sql statements. I have the following so far:

SELECT TOP 3 Custno
    , ( 3959 
         * acos( cos( radians(36) ) 
           * cos( radians( Latitude ) ) 
           * cos( radians( Longitude )  - radians(120) )
           + sin( radians(120) ) * sin( radians( Latitude ) ) 
         ) 
       ) AS distance  
FROM Customers 
ORDER BY distance

When I run that statement I get:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  
The results, if any, should be discarded.

However the query works when I remove the order by clause and when I change the order by clause to use Custno. What is causing this error and how can I avoid it?

Upvotes: 3

Views: 2311

Answers (1)

Vlad G.
Vlad G.

Reputation: 2147

Starting with SQL Server 2008 there's a Geography data type which is designed for things like this. Here's a couple links:

http://msdn.microsoft.com/en-us/library/ff929109.aspx http://blogs.msdn.com/b/isaac/archive/2008/10/23/nearest-neighbors.aspx

Upvotes: 2

Related Questions