CODEWITHSUNDEEP
sqlsql-server
Saadb
Saadb

Reputation: 13

SQL query of Haversine formula in SQL server

I am trying to get drivers who visited certain area from SQL server 2014 database. The table is named DriverLocationHistory.

Here is the sql query I used : 

SELECT id, ( 6371 * acos( cos( radians(37) ) * cos( radians( latitude ) ) 
* cos( radians( Longitude ) - radians(-122) ) + sin( radians(37) ) * sin(radians(latitude)) ) ) AS distance 
FROM DriverLocationHistory 
HAVING distance < 5 
ORDER BY distance

When I execute the query I get this error : 

Msg 207, Level 16, State 1, Line 7
Invalid column name 'distance'.

Upvotes: 1

Views: 13351

Answers (2)

Ben Thul
Ben Thul

Reputation: 32707

I was asked to provide a way to do this with the built-in geography data type. That's a bit too long for a comment, so here goes:

ALTER TABLE [DriverLocationHistory] ADD geoLocation 
   AS geography::Point([Latitude], [Longitude], 4236);

declare @p geography = geography::Point(37, -122, 4236);

select * from [DriverLocationHistory]
where geoLocation.STDistance(@p) < 5000;

A spatial index should help with the SARGability of the where clause in the select.

Upvotes: 6

Charles Bretana
Charles Bretana

Reputation: 146499

You can't use an alias in a Where clause. This is because the where clause is processed before, or as, the result set is being generated, and the alias is not assigned until the result set has been generated. Only in an aggregate query (where there is a group By) can you do this, because then the alias is assigned to the value of some expression before the aggregation is processed. Your query must use the full expression in both the where clause (does not need to be a Having clause) and in the order by:

SELECT id, 
  ( 6371 * acos( cos( radians(37) )  
      * cos( radians( latitude ) ) 
      * cos( radians( Longitude ) - radians(-122) ) + sin( radians(37) ) 
      * sin(radians(latitude)) ) ) AS distance 
FROM DriverLocationHistory 
Where 6371 * acos( cos( radians(37) )  
      * cos( radians( latitude ) ) 
      * cos( radians( Longitude ) - radians(-122) ) + sin( radians(37) ) 
      * sin(radians(latitude)) ) < 5 
ORDER BY 6371 * acos( cos( radians(37) )  
      * cos( radians( latitude ) ) 
      * cos( radians( Longitude ) - radians(-122) ) + sin( radians(37) ) 
      * sin(radians(latitude)) )

as mentioned in comments, you can use the alias in the order by,

Or, you could also perform the computation and alias assignment in a subquery:

SELECT id, distance
From (Select ( 6371 * acos( cos( radians(37) )  
          * cos( radians( latitude ) ) 
          * cos( radians( Longitude ) - radians(-122) ) + 
             sin( radians(37) ) 
          * sin(radians(latitude)) ) ) distance 
      From DriverLocationHistory)z
Where distance < 5 
ORDER BY distance

Upvotes: 4

Related Questions