Using Google Maps to show the geographical location of your website's visitors
Part III – IP to Geocode
In order to plot our visitors on the map, we’ll need the their Latitude and Longitude (Geocode). There are several services you can use and databases you can buy. For this, we’ll use MaxMind’s ASP-based service. Its rather easy to use, pretty accurate, and relatively inexpensive – many places charge hundreds of dollars for the database, plus more for updates, MaxMind’s service charges per lookup ($20 per 50,000 lookups) and is constantly updated. You can certainly use anything else you find, but the code below is for the MaxMind service.
Since, in this case, we are paying per lookup, we’ll want to cache the results – IP locations don’t really change that much, so it is safe to cache them for a year or even longer.
Now, when do we do the actual lookups? There are a few different ways we could approach this. First, we could just parse our server’s access logs every hour or so and lookup the IPs from there. Second, we could lookup their IP immediately upon hitting certain pages and insert it into our database. The method you use is up to you, but I because I have relatively few pages and rarely get more than a few hundred visitors per day to the site, I chose the second option. Please note that there are pitfalls with this method, and I encourage you to think very hard about which method you employ.
With that in mind, the following code will be placed in a file that is included in all the pages I want to track visitors to. It does the following:
1. Gets their IP and looks to see if it is in the database already.
2. If it is, it simply copies the info to a new visit.
3. If not, it calls the MaxMind API to get the geocode and then inserts that into the database.
Here is the code (note: Some of this code comes from MaxMind):
<?php
mysql_connect( 'localhost', 'username', 'password' );
$db = mysql_select_db( 'db' );
$license_key = 'xxx';
$ipaddress = $_SERVER['REMOTE_ADDR'];
$sql = "SELECT *
FROM visitor_log
WHERE ip = '{$ipaddress}'";
$c = mysql_num_rows(mysql_query($sql));
if( $c > 0 ){
$info = mysql_fetch_array(mysql_query($sql));
$sql = "INSERT
INTO visitor_log
(ip,lat,lng,city,state,country)
VALUES('{$ipaddress}','{$info['lat']}','{$info['lng']}', '{$info['city']}', '{$info['state']}','{$info['country']}')";
} else{
$query = "http://maxmind.com:8010/f?l=" . $license_key . "&i=" . $ipaddress;
$url = parse_url($query);
$host = $url["host"];
$path = $url["path"] . "?" . $url["query"];
$timeout = 1;
$fp = fsockopen ($host, 8010, $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);
}
$lines = split("\n", $buf);
$data = $lines[count($lines)-1];
fclose($fp);
} else {
# enter error handing code here
}
$dataArray = split( ",", $data );
$lat = $dataArray[4];
$lng = $dataArray[5];
$city = $dataArray[2];
$state = $dataArray[1];
$country = $dataArray[0];
$sql = "INSERT
INTO visitor_log
(ip,lat,lng,city,state,country)
VALUES ('{$ipaddress}','{$lat}','{$lng}','{$city}','{$state}','{$country}')";
}
mysql_query($sql);
?>
See, nothing too fancy. There are probably ways you could improve upon it though. Now we’ll need to have a way to get that data out and into XML that we can use.
Part IV – Creating the XML >>
|
