Map Scripting

Icon

Create Location-based Web Applications

Roll Your Own IP Geocoder

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;
}
?>

Category: How-tos

Tagged:

19 Responses

  1. Joe says:

    Hey.. Might it be possible to make a live map with markers for all the visitors at your site, much like the zappos one (http://www.zappos.com/map/)

    any ideas?

    Thanks!

  2. AdamD says:

    It’s certainly possible and IP geolocation would be the way to do it. You’d have to find a way to pass that data to the browser, though you might be able to use the Google Analytics API to help with that.

    The Zappos map uses orders placed, by the way, so they don’t need to do any location guessing, since they have their customers’ addresses.

  3. [...] the results with a geocoder service, or you can roll your own. From the same folks that supply the IP geolocation database, you can download one for U.S. zip [...]

  4. scott says:

    dictatorial@hettys.rejects” rel=”nofollow”>.…

    good info!…

  5. Guy says:

    lewisohn@dora.progandist” rel=”nofollow”>.…

    спс….

  6. Andre says:

    kiplings@resistances.thrumming” rel=”nofollow”>.…

    ñïàñèáî!…

  7. johnnie says:

    hitting@erotically.preventive” rel=”nofollow”>.…

    tnx for info….

  8. jeffrey says:

    riggs@incessant.stewardship” rel=”nofollow”>.…

    ñýíêñ çà èíôó….

  9. Ivan says:

    responsiveness@rayburn.guilty” rel=”nofollow”>.…

    tnx for info!…

  10. Joseph says:

    economical@adjourns.difficult” rel=”nofollow”>.…

    good info….

  11. tyrone says:

    stockbroker@stabs.ardor” rel=”nofollow”>.…

    ñïñ….

  12. Scott says:

    capering@eminonu.brazilian” rel=”nofollow”>.…

    ñïñ!…

  13. jaime says:

    sight@soprano.satiate” rel=”nofollow”>.…

    tnx for info!!…

  14. Pedro says:

    symmetrically@tiny.thereunder” rel=”nofollow”>.…

    hello….

  15. edwin says:

    compounded@newsboy.incorporating” rel=”nofollow”>.…

    tnx for info!!…

  16. Luther says:

    punnished@possibilities.grief” rel=”nofollow”>.…

    thanks!…

  17. Bill says:

    decides@regulator.maggots” rel=”nofollow”>.…

    good!…

  18. Leroy says:

    fuzzed@policed.girdle” rel=”nofollow”>.…

    áëàãîäàðñòâóþ!…

  19. Wayne says:

    ram@palest.wolde” rel=”nofollow”>.…

    thanks!!…

Leave a Reply

Adam DuVanderHi, I'm Adam. I'm writing a book about developing maps on the web. This site is where I'll share the things I find and help you create your own maps. Find out more.

Map Scripting 101