SQL query of Haversine formula in SQL server
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
haversine formula definition for sql
The order of lat/lngs don't matter. Think of it this way... the distance from point A to point B is that same as the distance from point B to point A.
In your code example, the 37 is a latitude point and the -122 is a longitude point.
acos is the arc cosine trigonemetric function. Explanation here: ArcCosine
SELECT id, ( 3959 * acos( cos( radians(Lat1) ) * cos( radians( Lat2 ) ) * cos( radians(Lng2) - radians(Lng1) ) + sin( radians(Lat1) ) * sin( radians(Lat2)))) AS distance
FROM markers
HAVING distance < 25
ORDER BY distance
LIMIT 0 , 20;
Haversine formula using SQL server to find closest venue - vb.net
I think you'd do best putting it in a UDF and using that in your query:
SELECT v.lat, v.lng, v.name, p.lat, p.lng, p.postcode, udf_Haversine(v.lat, v.lng, p.lat, p.lng) AS distance FROM venuepostcodes v, postcodeLngLat p WHERE p.outcode = 'CB6' ORDER BY distance
create function dbo.udf_Haversine(@lat1 float, @long1 float, @lat2 float, @long2 float) returns float begin
declare @dlon float, @dlat float, @rlat1 float, @rlat2 float, @rlong1 float, @rlong2 float, @a float, @c float, @R float, @d float, @DtoR float
select @DtoR = 0.017453293
select @R = 3937 --3976
select
@rlat1 = @lat1 * @DtoR,
@rlong1 = @long1 * @DtoR,
@rlat2 = @lat2 * @DtoR,
@rlong2 = @long2 * @DtoR
select
@dlon = @rlong1 - @rlong2,
@dlat = @rlat1 - @rlat2
select @a = power(sin(@dlat/2), 2) + cos(@rlat1) * cos(@rlat2) * power(sin(@dlon/2), 2)
select @c = 2 * atn2(sqrt(@a), sqrt(1-@a))
select @d = @R * @c
return @d
end
Having/Where clause not working for Haversine Formula using Microsoft SQL
You cannot use calculated fields on a where or having clause. Create a view or use a subquery
Try this:
select * FROM (SELECT *, ( 3960 * acos( cos( radians( 33.650800 ) ) *
cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians( -117.891729 ) ) +
sin( radians( 33.650800 ) ) * sin( radians( Latitude ) ) ) ) AS Distance
FROM test) as T WHERE T.Distance < 10
Grouping and counting of records in Haversine Formula SQL Query
You need to have the condition in where clause not having
SELECT *, count(*)
FROM
(SELECT *,ROUND(((ACOS(SIN(51.4811109 * PI() / 180) *
SIN(latitude * PI() / 180) + COS(51.4811109 *
PI() / 180) *
COS(latitude * PI() / 180) *
COS((-0.433641 - longitude) * PI() / 180)) *
180 / PI()) * 60 * 1.1515),2) AS distance
FROM `vehicle` `t`
WHERE (status='Active')) as v
WHERE distance<=10
GROUP BY make
MySQL Great Circle Distance (Haversine formula)
From Google Code FAQ - Creating a Store Locator with PHP, MySQL & Google Maps:
Here's the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37, -122 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.
SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) )
* cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin(radians(lat)) ) ) AS distance
FROM markers
HAVING distance < 25
ORDER BY distance
LIMIT 0 , 20;
Related Topics
Set Constraints All Deferred Not Working as Expected
MySQL Query to Update Field to Max(Field) + 1
Combining Union All and Order by in Firebird
Best to Use * When Calling a Lot of Fields in MySQL
How to Combine Two Rows and Calculate the Time Difference Between Two Timestamp Values in MySQL
SQL Server, Converting Seconds to Minutes, Hours, Days
Number of Fridays Between Two Dates
SQL Server 2008:Cannot Insert New Column in the Middle Position and Change Data Type
How to Calculate a Moving Average Using MySQL
Extract Phone Number from Noised String
Concatenate One Field After Group By
How to Generate All Possible Data Combinations in SQL
Psycopg2 Equivalent of MySQLdb.Escape_String
Invalid Column Name on SQL Server Update After Column Create