MySQL PHP Geolocation - Zip codes: latitude, longitude, distance
This is one of my first really complex SQL query entries. The premise goes like this: you have a user database with a bunch of people who signed up for your service, which includes the user's zip code. Now you want to write a query so you can show user A all the other users in your database that are within, say, 10 miles of her zip code.
It's called Geolocation.
In order to do this, you need two things: 1. a database of all US zip codes, and their corresponding semi-exact latitudes/longitudes; 2. someone like me to teach you spherical geometry.
Lucky for you, you've come here, because not only do I have a nice 7-zipped archive of a US zip codes to latitude / longitude database for you to download, this also happens to be my website, so I can teach you geometry as well!
Now here's a little PHP/SQL for the actual mathematical processing (nearly all done in the database!):
-
-
// connect to the database
-
-
// we need the userid of the person who is doing the searching:
-
$userid = 1;
-
// and the max distance (in miles) you want to show results from:
-
$max_distance = 10; // in this case, 10 miles
-
-
// now we go out to the database and get that user's latitude and longitude based on the user's zipcode
-
$user_query = mysql_query("SELECT z.latitude, z.longitude FROM users u, zipcodes z WHERE u.id='$userid' AND u.zipcode=z.zipcode");
-
$latitude = $user_data['latitude'];
-
$longitude = $user_data['longitude'];
-
// then we use that latitude and longitude to write our complex SQL query:
-
$complex_query = mysql_query("SELECT u.id, truncate((degrees(acos(sin(radians(z.latitude)) * sin(radians(".$latitude.")) + cos(radians(z.latitude)) * cos(radians(".$latitude.")) * cos(radians(z.longitude - ".$longitude.")))) * 69.09),1) as distance FROM users u, zipcodes z WHERE u.id != '$userid' HAVING distance < $max_distance");
-
$i = 0;
-
// this is a nice array of userids that are within 10 miles:
-
$found_user_ids[$i] = $row['id'];
-
$i++;
-
}
-
} else {
-
// no results!
-
}
-
} else {
-
// that user's zipcode is not in the database
-
echo 'Sorry, that zipcode is not found in the database!';
-
}
-
There is one little chunk in the $complex_query that was new to me the first time I used this code, and that was the HAVING portion. In the past, I've always tried to use a WHERE statement to limit results based on a calculated column (in this case, the distance column that is the result of the complicated mathematical formula) but I would get an error since the WHERE portion of a SQL statement is actually required to gather the SELECT data, so data from the SELECT cannot be used to limit itself (take a breath and think about it, it does make sense). Enter HAVING.
I know I told you I'd teach you geometry, but I don't actually completely understand what the math above does on a figure-to-figure basis. I do know that is calculates the mean distances between every user in the database. And I know it does so taking the curvature of the Earth into consideration. Think of it like this: if you go near the North Pole, you can walk over every line of longitude (the vertical lines on the globe) in a few seconds by simply walking all the way around the North Pole. But as you get closer to the equator, the distance becomes greater between lines of longitude. So you need SINs and COSINs and Radians and stuff.
At the top, the lines are close together. In the middle, they are further apart. You can I are somewhere in the middle, unless you live in the tropics, or you're Santa Claus, which would be cool, I mean, how many websites does Santa actually visit?
