I've got a working PHP script that gets Longitude and Latitude values and then inputs them into a MySQL query. I'd like to make it solely MySQL. Here's my current PHP Code:
if ($distance != "Any" && $customer_zip != "") { //get the great circle distance
//get the origin zip code info
$zip_sql = "SELECT * FROM zip_code WHERE zip_code = '$customer_zip'";
$result = mysql_query($zip_sql);
$row = mysql_fetch_array($result);
$origin_lat = $row['lat'];
$origin_lon = $row['lon'];
//get the range
$lat_range = $distance/69.172;
$lon_range = abs($distance/(cos($details[0]) * 69.172));
$min_lat = number_format($origin_lat - $lat_range, "4", ".", "");
$max_lat = number_format($origin_lat + $lat_range, "4", ".", "");
$min_lon = number_format($origin_lon - $lon_range, "4", ".", "");
$max_lon = number_format($origin_lon + $lon_range, "4", ".", "");
$sql .= "lat BETWEEN '$min_lat' AND '$max_lat' AND lon BETWEEN '$min_lon' AND '$max_lon' AND ";
}
Does anyone know how to make this entirely MySQL? I've browsed the Internet a bit but most of the literature on it is pretty confusing.
$greatCircleDistance = acos( cos($latitude0) * cos($latitude1) * cos($longitude0 - $longitude1) + sin($latitude0) * sin($latitude1));
with latitude and longitude in radian.
so
is your SQL query
to get your results in Km or miles, multiply the result with the mean radius of Earth (
3959
miles,6371
Km or3440
nautical miles)The thing you are calculating in your example is a bounding box. If you put your coordinate data in a spatial enabled MySQL column, you can use MySQL's build in functionality to query the data.
I have written a procedure that can calculate the same, but you have to enter the latitude and longitude in the respective table.
If you add helper fields to the coordinates table, you can improve response time of the query.
Like this:
If you're using TokuDB, you'll get even better performance if you add clustering indexes on either of the predicates, for example, like this:
You'll need the basic lat and lon in degrees as well as sin(lat) in radians, cos(lat)*cos(lon) in radians and cos(lat)*sin(lon) in radians for each point. Then you create a mysql function, smth like this:
This gives you the distance.
Don't forget to add an index on lat/lon so the bounding boxing can help the search instead of slowing it down (the index is already added in the CREATE TABLE query above).
Given an old table with only lat/lon coordinates, you can set up a script to update it like this: (php using meekrodb)
Then you optimize the actual query to only do the distance calculation when really needed, for example by bounding the circle (well, oval) from inside and outside. For that, you'll need to precalculate several metrics for the query itself:
Given those preparations, the query goes something like this (php):
EXPLAIN on the above query might say that it's not using index unless there's enough results to trigger such. The index will be used when there's enough data in the coordinates table. You can add FORCE INDEX (lat_lon_idx) to the SELECT to make it use the index with no regards to the table size, so you can verify with EXPLAIN that it is working correctly.
With the above code samples you should have a working and scalable implementation of object search by distance with minimal error.
for distance of 25 km
I can't comment on the above answer, but be careful with @Pavel Chuchuva's answer. That formula will not return a result if both coordinates are the same. In that case, distance is null, and so that row won't be returned with that formula as is.
I'm not a MySQL expert, but this seems to be working for me:
I thought my javascript implementation would be a good reference to: