Skip to content

Instantly share code, notes, and snippets.

@maciakl
Last active March 1, 2017 03:35
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save maciakl/4586924 to your computer and use it in GitHub Desktop.
Save maciakl/4586924 to your computer and use it in GitHub Desktop.
Haversine formula for finding all the rows in your database which are within N miles from given coordinates. SQL and PHP implementation below. http://en.wikipedia.org/wiki/Haversine_formula
$lat = 0; // some latitude
$lng = 0; // some longitude
$dst = 100; // some distance in miles
// get the contents of the file in the gist below
$sql = file_get_contents('haversine.sql')
// prepare PDO query
$statement = $db->prepare($sql);
// bind parameters
$statement->bindValue(1, $lat, PDO::PARAM_STR);
$statement->bindValue(2, $lng, PDO::PARAM_STR);
$statement->bindValue(3, $lat, PDO::PARAM_STR);
$statement->bindValue(4, $lng, PDO::PARAM_STR);
$statement->bindValue(5, $lat, PDO::PARAM_STR);
$statement->bindValue(6, $dst, PDO::PARAM_INT);
// execute query
$db->execute();
-- The ? parameters will be supplied at run time
SELECT username, firstname, lastname, zip, lat, lng, town,
( 3959 *
acos(
COS(RADIANS(?)) *
COS(RADIANS(?)) *
COS(RADIANS(lat)) *
COS(RADIANS(lng))
+
COS(RADIANS(?)) *
SIN(RADIANS(?)) *
COS(RADIANS(lat)) *
SIN(RADIANS(lng))
+
SIN(RADIANS(?)) *
SIN(RADIANS(lat))
)
) AS distance
FROM employees
HAVING distance <= ?
ORDER BY distance
LIMIT 0 , 20;
@batata004
Copy link

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment