Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Haversine formula in SQL
/*
6371 for Kms
3956 for Miles
$lat, $lng are passed in
*/
SELECT *,
( 3956 * acos( cos(radians($lat)) * cos(radians( lat.field_latitude_value )) *
cos(radians( lng.field_longitude_value ) - radians($lng)) + sin(radians($lat))
* sin(radians( lat.field_latitude_value )) ) )
AS distance_in_miles
FROM table_name
/* Override distance in mile as needed */
HAVING distance_in_miles < 50
WHERE "listings"."status" IN ('Open')
ORDER BY distance_in_kilometres, created_at DESC
LIMIT 0, 10
/* See: http://www.codecodex.com/wiki/Calculate_Distance_Between_Two_Points_on_a_Globe#PostgreSQL_PL.2FPERLU */
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment