Map Scripting

If you’ve ever seen a site correctly guess your location, chances are good it used your IP address and a big ol’ database to make an educated stab at it. Now you can do the same and do it for free.

IP addresses are numeric identifiers for every computer connected to the Internet. They look like 68.180.206.184 and 206.190.60.37. With great accuracy, these can be traced down to the city where they originate, assuming you have the data necessary.

Marc-Andre Caron compiled a MySQL IP database that you can install on your own server. Read on for some basic instructions to roll your own geocoder. If you’d rather use a web service to get at the data, check out my post at ProgrammableWeb.

Wait… How Does This Work?

The database catalogs known blocks of IPs by their location. For example, Comcast cable might own 71.59.208.0 – 71.59.208.255 and use them (along with many others) to hand out to Portland residents.

To conserve database space, the IP is converted to a unique number. Where the IP is A.B.C.D, the number is ((A*256+B)*256+C)*256. So, 71.59.208.255 would become 1195102208. As would anything in the 71.59.208.X block, because notice that there is no D in the above formula. That’s because IPs come in blocks, so everything in the range will likely have the same location.

Download and Decompress the Database
Caron’s MySQL database comes compressed using bzip2, a free utility available on most Unix-ish systems. While phpMyAdmin accepts imports directly in the compressed format, it is likely beyond the capacity, so you will need to have SSH access to your server and the MySQL command tool.

Download the SQL to your server and unzip the archive:
bunzip2 ipinfodb.sql.bz2

You should now have a much larger file, likely called ipinfodb.sql.

Install the Database

Now you’ll need to use the MySQL tool on your server. You may need to ask your host for specifics, but often the command to bring it up will be similar to this:
mysql -u username -p databasename

You’ll be asked for a password. If all is well, you’ll see the prompt, where you can enter SQL commands. This is a less graphical phpMyAdmin, a great place to test out queries.

To import the data, run this command in the MySQL tool:
\. ipinfodb.sql

This will read in the large SQL file and start creating the tables and adding its necessary data. The process will take many minutes. Go take a break!

When you see the MySQL prompt again, you know it is finished. To quit the tool, use the \q command.

Run a Lookup from PHP

There are two ways to query the database. You can convert the IP to a number yourself, or you can use the INET_ANON function in MySQL:
SELECT * FROM `ip_group_city` where `ip_start` <= INET_ATON('71.59.208.255') order by ip_start desc limit 1

The PHP code below shows both options. Before you add it to your own server, see it in action. If you remove the query string (that stuff after the question mark) you can geocode your own IP.

Here is the code listing for my example PHP (be sure to fill in your DB values at the top):

<?
  // Config values -- FILL THEM IN
  $dbserver = "";
  $dbname = "";
  $dbuser = "";
  $dbpass = "";

  // Setup variables
  $ip = $_GET["ip"];
  if ($ip == "") { $ip = $_SERVER["REMOTE_ADDR"]; }
  list($lat, $lon)  = lookup_ip($ip);
  if ($lat && $lon) {
    print "$lat, $lon";
  }

function lookup_ip($ip) {
  global $dbserver, $dbuser, $dbpass, $dbname;
  if (!preg_match("/^\d+\.\d+\.\d+\.\d+$/", $ip)) {
    return;
  }
  $d = mysql_connect($dbserver, $dbuser, $dbpass);
  mysql_select_db($dbname, $d);
  $ipnum = ip_to_number($ip);
  // The numeric option...
  $s = "select latitude, longitude from ip_group_city where ip_start";
       $s .= "<= $ipnum order by ip_start desc limit 1";
  // Or the INET_ANON option...
  $s = "select latitude, longitude from ip_group_city where ip_start";
       $s .= "<= INET_ATON('$ip') order by ip_start desc limit 1";
  $res = mysql_query($s, $d);
  $ll = mysql_fetch_array($res, MYSQL_NUM);
  mysql_close($d);

  return $ll;
}
function ip_to_number($ip) {
  list($a, $b, $c, $d) = split("\.", $ip);
  return (($a*256 $b)*256 $c)*256;
}
?>