Skip to content

Instantly share code, notes, and snippets.

@brandedoutcast

brandedoutcast/Calculate distance using Latitude & Longitude.sql

Last active August 29, 2015 14:01
Show Gist options
  • Save brandedoutcast/3f21dbb264828fbc37af to your computer and use it in GitHub Desktop.
Save brandedoutcast/3f21dbb264828fbc37af to your computer and use it in GitHub Desktop.
Download ZIP
Calculates distance between two points with their latitudes and longitudes.
Raw
Calculate distance using Latitude & Longitude.sql
SELECT l.Loc_Name, 6371.0E * ( 2.0E * asin(
case when 1.0E < (sqrt(square(sin(((RADIANS(CAST(36.5696 AS FLOAT)))-(RADIANS(CAST(l.Loc_Latitude AS FLOAT))))/2.0E))
+ (cos(RADIANS(CAST(l.Loc_Latitude AS FLOAT)))
* cos(RADIANS(CAST(36.5696 AS FLOAT)))
* square(sin(((RADIANS(CAST(87.1003 AS FLOAT)))
-(RADIANS(CAST(l.Loc_Longitude AS FLOAT))))/2.0E)))))
then 1.0E
else (sqrt(square(sin(((RADIANS(CAST(36.5696 AS FLOAT)))
-(RADIANS(CAST(l.Loc_Latitude AS FLOAT))))/2.0E))
+ (cos(RADIANS(CAST(l.Loc_Latitude AS FLOAT)))
* cos(RADIANS(CAST(36.5696 AS FLOAT)))
* square(sin(((RADIANS(CAST(87.1003 AS FLOAT)))
-(RADIANS(CAST(l.Loc_Longitude AS FLOAT))))/2.0E))))) end )) AS distance
FROM Location l --WHERE distance <= 5
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment