How to query by geolocation (latitude and longitude) with MySQL
In one of my posts, I explain How To Google Geocode an Address With PHP. Please refer to How To Google Geocode an Address With PHP for specifics on geocoding an address.
In this post, I’ll explain how to use those saved geocoordinates to search for properties within a distance from a specific zip code. This example assumes you have properties in a table named tbl_listings with columns for listing_zip, listing_geolatitude, and listing_geolongitude. Providing a search by zip code is very simple. Providing a search for a given distance from a specific zip code is also pretty simple if you have the geographic coordinates of the target zip code.
Here is an example query to search for properties within a given distance from a zip code. For this example, I have hardcoded the search zip and radius (in miles). You would likely have a search form prompting for these values. The query also uses the geocoordinates to calculate the distance (AS listing_distance) from the search zip to the listing zip.
You would need to include the geocode function fnGeocode on your page. I have it as an external include file so I can reuse it on multiple pages. Please refer to How To Google Geocode an Address With PHP for details.
include_once("geocode.php"); // external include that has the fnGeocode function
// search by zip and radius
$zip = 90210; // this would likely come from a seach form
$radius = 50; // this would likely come from a seach form
// get search from zip coordinates
$search_latitude = 0;
$search_longitude = 0;
if ($zip != "" && $radius != 0)
{
$coodinates = fnGeocode($zip);
$search_latitude = $coodinates[0];
$search_longitude = $coodinates[1];
}
// build query
$query = "SELECT tbl_listings.*";
if ($zip == "" || ($zip != "" && $radius == 0)) // handle no search zip or zero search radius specified
{
$query .= " , 0 AS listing_distance";
} else {
$query .= ", (3959";
$query .= " * acos(cos(radians($search_latitude))";
$query .= " * cos(radians(listing_geolatitude))";
$query .= " * cos(radians(listing_geolongitude)";
$query .= " - radians($search_longitude))";
$query .= " + sin(radians($search_latitude))";
$query .= " * sin(radians(listing_geolatitude)))) AS listing_distance";
}
$query .= " FROM tbl_listings";
$query .= " WHERE listing_active = 'Y'";
if ($zip != "")
{
if ($radius == 0)
{
$query .= " AND listing_zip = '$zip'"; // search a specific zip only
} else {
$query .= " AND (3959";
$query .= " * acos(cos(radians($search_latitude))";
$query .= " * cos(radians(listing_geolatitude))";
$query .= " * cos(radians(listing_geolongitude)";
$query .= " - radians($search_longitude))";
$query .= " + sin(radians($search_latitude))";
$query .= " * sin(radians(listing_geolatitude))))";
$query .= " <= $radius";
}
}