SQL query of Haversine formula in SQL server

Question

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'.

If you have a version of SQL that's greater than SQL 2008 (which you do), consider using the built-in geography data type instead of rolling your own calculation. — Ben Thul, Sep 06 '16 at 17:28

score 6 · Answer 1 · answered Sep 07 '16 at 21:20

6

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.

answered Sep 07 '16 at 21:20

Ben Thul

31,080
4
45
68

Tell me more. https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/point-geography-data-type?view=sql-server-ver15 shows `Point ( Lat, Long, SRID )` – Ben Thul Mar 13 '20 at 16:47
[PostGIS says otherwise](https://gis.stackexchange.com/questions/217492/distance-calculation-using-st-distance) and they reference the WKT format for this srid which is lng,lat. Its crazy, but I just saw the note that explicity admits they have it the wrong way round. I wonder if that also applies to the Point type in NetTopologySuite, if you pass a Point in as a param. – gbjbaanb Mar 13 '20 at 17:02
The `Point` method from Microsoft specifically doesn't take WKT. It doesn't conform to OGC standards and is what they list as an "extended" method. If you have WKT, https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/stpointfromtext-geography-data-type?view=sql-server-ver15 is what you're looking for. – Ben Thul Mar 13 '20 at 19:44

Charles Bretana · Accepted Answer · 2016-09-05T22:39:11.390

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

You could also use a nested query to avoid the repetition: `select * from (select id, blahblahblah as distance) x where x.distance < 5` — Blorgbeard, Sep 05 '16 at 22:13
but you can use the aliased column in the order by which would be at least a little nicer code than repeating the formula 3 times — Matt, Sep 05 '16 at 22:23
I hardly ever do that, so I wasn't familiar with it, but yes, you are correct. — Charles Bretana, Sep 05 '16 at 22:34
@Charles Bretana Is the usage of alias in where clause accepted in MySql? — Saadb, Sep 05 '16 at 22:36
I am not familiar with vendor specific syntax in MySQL, I am a SQL Server/Oracle person, but you should b e able to google that. — Charles Bretana, Sep 05 '16 at 22:40

SQL query of Haversine formula in SQL server

2 Answers2

Linked