Delivering content relative to the physical location of your users is an excellent (and fairly easy) way to fine-tune the content you’re delivering to be most relevent to the people visiting your site. Two simple ways of doing this are to use an IP-based geolocation lookup, or to do a manual radius search (like a “store finder” type of functionality), where the user manually enters a postal code. Both function on the same logic – the real difference is that one requires a third-party service that can fetch the user’s latitude and longitude based on IP address. This functionality can be used to show content such as local news, local store branches, etc – as soon as the user visits your page.
The nuts and bolts of IP-based geolocation is as follows:
- User lands on the web page
- User’s IP is captured and posted to a third-party geolocation service, which returns a latitude and longitude for the IP address
- Website performs a radius database search using the latitude and longitude provided by the third party service and returns content relative to that location
Bear in mind, your database must contain data that is stored with a latitude and longitude for this to work. You cannot compare a user’s latitude and longitude to the lat/long of a piece of data if you have no lat/long associated to your content.
Also, please note: the services mentioned here – and the code provided – is intended for US-based geolocation. The code will remain basically the same for non-US lookups, but you may need to use alternate third-party services if your country is not covered by the ones mentioned here.
1. Getting Latitude/Longitude for Your Database Data
The first part of this process starts with you tagging your existing or newly added database data with the correct latitude and longitude. Each item you wish to include in the radius search should have a valid lat/long value in the latitude and longitude fields. If you are modifying an existing database, you would execute an alter table command to add the new lat/long fields. For this example, we’ll be using a table called stores:
[sourcecode language=’sql’]CREATE TABLE `stores` (`store_id` INT NOT NULL AUTO_INCREMENT ,
`store_address` VARCHAR( 40 ) NULL ,
`store_city` VARCHAR( 40 ) NULL ,
`store_state` VARCHAR( 2 ) NULL ,
`store_country` VARCHAR( 2 ) NULL ,
`store_phone` VARCHAR( 15 ) NULL ,
UNIQUE (`store_id`));[/sourcecode]
And then we add the new latitude and longitude fields to our stores table, using the DOUBLE datatype:
[sourcecode language=’sql’]ALTER TABLE `stores` ADD `latitude` DOUBLE NULL ,ADD `longitude` DOUBLE NULL ;[/sourcecode]
Now our table is set up to store the latitude and longitude data, but from where do we actually get the data, short of manually looking it up for each row of data in our stores table? Easy. There is a fabulous free service available at rpc.geocoder.us that lets you post an address to their API, to which it responds with the latitude and longitude values for that address. The easiest way to handle this process is to set up a cURL request in the admin area where you’re managing your data – otherwise you’ll need to write a script to cycle through the rows of data, fetching the latitude and longitude.(You will, of course, need to have PHP configured with cURL support for this to work.) Using this service, free lookups are throttled by your IP address to one request every 15 seconds – this may cause issues when you’re initially trying to get the lat/long data into your database for existing records, but shouldn’t be much of an issue afterwards.
First let’s set up a small function to handle the cURL request:
[sourcecode language=’php’]function curl_string ($url){$ch = curl_init();
curl_setopt ($ch, CURLOPT_URL, $url);
curl_setopt ($ch, CURLOPT_HEADER, 0);
curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt ($ch, CURLOPT_FOLLOWLOCATION, 1);
curl_setopt ($ch, CURLOPT_TIMEOUT, 120);
$result = curl_exec ($ch);
curl_close($ch);
return $result;
[/sourcecode]
The format you will be sending the query to the rpc.geocoder.us API is: http://rpc.geocoder.us/service/csv?address=1600+Pennsylvania+Ave%2C+Washington+DC
[sourcecode language=’php’] // set the url of the API using the address, city and state we want to query$url_page = “http://”.”rpc.”.”.geocoder”.”.us/service/csv”;
$url_page .=”?address=”.urlencode($address).”,”.urlencode($city).”,”.$state;
// execute the cURL request
$string = curl_string($url_page);
// turn the comma separated csv data turned into an array,
// so we can easily see that a match was found and use the pieces
$address_pieces= explode(“,”, $string);
// make sure the array contains data
if (count($address_pieces) > 0) {
// update the database
$sql = “update stores set latitude='”.$address_pieces[0].”‘, “;
$sql .=”longitude='”.$address_pieces[1].”‘, “;
$sql .=”zip='”.$address_pieces[5].”‘ where id='”.$store_id.”‘”;
if ($update_latlong = mysql_query($sql)) {
echo ‘Lat/long updated!’;
} else {
// if the query failed, print out an error
echo mysql_error();
}
// if the array does not contain data, no match was found in geocoder.us, so suggest using google maps to find the lat/long manually
} else {
echo ‘No geolocation match.’;
}[/sourcecode]
The code above will help you update your existing data if you were to use it in a script that cycles through your database records, or you can use it as part of your store administration area, doing the cURL request every time a new store is saved to the database.
2. Getting the Latitude and Longitude of the User
Now that you have base lat/long data for the data in your database, you have to obtain the lat/long for the user visiting the site. For this next part, you will again need to access a third-party service, this time to get the latitude and longitude based on the user’s IP address. I use a commercial service available from MaxMind.Com. It’s not free, but their prices are very reasonable ($20 per 50,000 queries) – and by using a cookie to store whether or not the user’s lat/long has already been returned, you can save on the number of accesses you use up on a busy site.
[sourcecode language=’php’]// begin the sessionsession_start();
$expireTime = 60*60*24*30; // 30 days
session_set_cookie_params($expireTime);
if ((!isset($_SESSION[‘geo_country’])) || ($_SESSION[‘geo_country’]==”)) {
// enter your MaxMind license key here
$license_key=’XXXXXXXXXXXXXXX’;
$ip = $_SERVER[‘REMOTE_ADDR’];
$query = “http://”.”geoip1.”.”maxmind”.”.com/b?l=” . $license_key . “&i=” . $ip;
$url = parse_url($query);
$host = $url[“host”];
$path = $url[“path”] . “?” . $url[“query”];
$timeout = 1;
$fp = fsockopen ($host, 80, $errno, $errstr, $timeout)
or die(‘Can not open connection to server.’);
if ($fp) {
fputs ($fp, “GET $path HTTP/1.0\nHost: ” . $host . “\n\n”);
while (!feof($fp)) {
$buf .= fgets($fp, 128);
} // endwhile
// split the output into an array
$lines = split(“\n”, $buf);
$data = $lines[count($lines)-1];
fclose($fp);
$geo = explode(“,”,$data);
$user_geo_country = $geo[0];
$user_geo_state = $geo[1];
$user_geo_city = $geo[2];
$user_geo_lat = $geo[3];
$user_geo_lon = $geo[4];
$_SESSION[‘geo_country’] = $user_geo_country;
$_SESSION[‘geo_state’] = $user_geo_state;
$_SESSION[‘geo_city’] = $user_geo_city;
$_SESSION[‘geo_lat’] = $user_geo_lat;
$_SESSION[‘geo_lon’] = $user_geo_lon;
setcookie(“geolocCookieCountry”, $user_geo_country, time()+$expireTime, “/”);
setcookie(“geolocCookieCity”, $user_geo_city, time()+$expireTime, “/”);
setcookie(“geolocCookieState”, $user_geo_state, time()+$expireTime, “/”);
setcookie(“geolocCookieLat”, $user_geo_lat, time()+$expireTime, “/”);
setcookie(“geolocCookieLon”, $user_geo_lon, time()+$expireTime, “/”);
} // endif $fp
} // endif session set
[/sourcecode]
Now, you’ll only be querying MaxMind if the user hasn’t already accessed the site before and had their latitude and longtude stored in the cookie. Note the $license variable in the code above. When you sign up for the MaxMind Web Service, you will be given a license number, which you’ll insert there.
3. Putting the Two Together to Return Results Within X Miles
To tie the two together and query the database, returning only results that are within a specified number of miles of the user’s IP address, we’ll need two classes:
[sourcecode language=’php’]class RadiusCheck {var $maxLat;
var $minLat;
var $maxLong;
var $minLong;
function RadiusCheck($Latitude, $Longitude, $Miles) {
global $maxLat,$minLat,$maxLong,$minLong;
$EQUATOR_LAT_MILE = 69.172;
$maxLat = $Latitude + $Miles / $EQUATOR_LAT_MILE;
$minLat = $Latitude – ($maxLat – $Latitude);
$maxLong = $Longitude + $Miles / (cos($minLat * M_PI / 180) * $EQUATOR_LAT_MILE);
$minLong = $Longitude – ($maxLong – $Longitude);
}
function MaxLatitude() {
return $GLOBALS[“maxLat”];
}
function MinLatitude() {
return $GLOBALS[“minLat”];
}
function MaxLongitude() {
return $GLOBALS[“maxLong”];
}
function MinLongitude() {
return $GLOBALS[“minLong”];
}
}[/sourcecode]
and
[sourcecode language=’php’]class DistanceCheck {function DistanceCheck() {
}
function Calculate(
$dblLat1,
$dblLong1,
$dblLat2,
$dblLong2
) {
$EARTH_RADIUS_MILES = 3963;
$dist = 0;
//convert degrees to radians
$dblLat1 = $dblLat1 * M_PI / 180;
$dblLong1 = $dblLong1 * M_PI / 180;
$dblLat2 = $dblLat2 * M_PI / 180;
$dblLong2 = $dblLong2 * M_PI / 180;
if ($dblLat1 != $dblLat2 || $dblLong1 != $dblLong2)
{
//the two points are not the same
$dist =
sin($dblLat1) * sin($dblLat2)
+ cos($dblLat1) * cos($dblLat2)
* cos($dblLong2 – $dblLong1);
$dist =
$EARTH_RADIUS_MILES
* (-1 * atan($dist / sqrt(1 – $dist * $dist)) + M_PI / 2);
}
return $dist;
}
}[/sourcecode]
And then, to perform the actual query:
[sourcecode language=’php’] // set a default number of miles to search within$Miles = ’50’;
// set the user’s latitude and longitude as the one to search against
$Latitude = $user_geo_lat;
$Longitude = $user_geo_lon;
$zcdRadius = new RadiusCheck($Latitude,$Longitude,$Miles);
$minLat = $zcdRadius->MinLatitude();
$maxLat = $zcdRadius->MaxLatitude();
$minLong = $zcdRadius->MinLongitude();
$maxLong = $zcdRadius->MaxLongitude();
$sql = “SELECT store_address, store_city, store_state, store_phone, “;
$sql .= “SQRT((((69.1*(latitude-$Latitude))*(69.1*(latitude-$Latitude)))+((53*(longitude-$Longitude))*(53*(longitude-$Longitude))))) “;
$sql .= “AS calc FROM stores where “;
$sql .= “latitude >= ‘$minLat’ “;
$sql .= “AND latitude <= '$maxLat' ";
$sql .= "AND longitude >= ‘$minLong’ “;
$sql .= “AND longitude <= '$maxLong' ";
$get_data = mysql_query($sql);
// loop through the matching database results
while($storedata = mysql_fetch_assoc($get_data)) {
// calculate the number of miles away the result is
$zcdDistance = new DistanceCheck;
$Distance = $zcdDistance->Calculate($Latitude,$Longitude,$storedata[‘latitude’],$storedata[‘longitude’]);
// and for the non-US people, here’s the km calculation
$calc_km = round(($Distance * 1.609344),2);
echo ‘
‘.$storedata[‘store_city’].’, ‘;
echo $storedata[‘store_state’].’ ‘.$storedata[‘store_country’].’
‘;
echo $storedata[‘store_phone’].’
‘;
echo ‘Distance: ‘.$Distance.’ (‘.$calc_km.’ km)’;
}[/sourcecode]
And that’s really all there is to it.
Store Locator Only
If you want to create a store-locator style script without automagically getting the user’s current location, you’d use the code above, almost verbatim. The difference is that you’d need an additional table of zipcodes with associated lat/long, available for purchase (again, not very expensive) from ZipCodeDownload.Com. The process would go as follows:
- User arrives at your site, and enters a zip code and mile radius they wish to search using your search form – clicks submit
- The script queries the zip code table to find the latitude and longitude associated with the postal code the user has entered, and uses THAT latitude and longitude as the values for $Latitude and $Longitude in the code above, and uses the user-entered values for $Miles from the search form.
Everything else stays exactly the same. Naturally, you’ll probably want to add some sanity checks in your own code (gracefully displaying default content if no latitude and longitude is returned, etc) but I decided to skip that here so as not to confuse anyone.
Enjoy, and if you end up using this anywhere, let me know how it works out.
