If you’re running a website that provides a service, it’s likely that it would be beneficial to know a user’s location (or have a rough idea) so that the content could be tailored to their specific geographic area. But how do you get their location, without having to ask them? By using their IP address, it’s possibly to determine their general area with fairly good accuracy. In this tutorial, I’ll explain how to do that using the free IP address geolocation database from IPInfoDB.

Motivation and Usage

There are numerous reasons for using the user’s location to improve the quality of a service. For example, the restaurant guide and review site, Restaurantica, automatically makes a guess as to your location and populates the “Search” field so that you are one click away from finding information tailored to your geographic area.

This determination is based on the user’s IP address. Usually, the lookup is done based on a static set of mappings from IP addresses to geographical locations, like cities. In fact, this is exactly what the IP address geolocation database (that we’ll use) does. It has the advantage of being easy to understand but unfortunately the accuracy can degrade over time, as IP addresses are usually not hardwired to specific locations. For example, most DSL users are subject to DHCP, so the same IP address may be re-used across different cities. (The IP address database from IPInfoDB is updated periodically to help reduce this inaccuracy)

Thus, you should not rely on the information provided to be 100% accurate; it should only be used as a suggestion. This is why Restaurantica only populates the search field with your estimated location, rather than giving you the results for that location right away; if it was wrong, the results for a different location would certainly be confusing to a user. This gives the user a chance to correct any inaccurate lookups.

Using the IP Address geolocation database

The first step is to download the database and import it into your local database; for this example I’ll use MySQL. Note that the site also offers an IP location Lookup API, which has the advantage of not requiring a local database but may be subject to usage limits; if you’ll be getting a lot of traffic you’ll be better off using the local database.

Import the contents into MySQL

I downloaded the Complete (City) database as one table in SQL format. Extracting the file from the archive, you’ll see that it’s well over 300 MB in size. If you’re fond of using phpMyAdmin, you unfortunately won’t be able to import such a large file using that tool, so we’ll have to rely on the command line to import the contents. Go to your mysql/bin folder and enter the following command, assuming you extracted the SQL file to the same location:

mysql -u root -p [name of database] < ipinfodb_one_table_full.sql

Running this command will take some time, as it won’t be fast having to import such a huge table. Take a break and come back in a few minutes. 🙂 (You may want to create a new database to hold just this table, do that before running this command) After that’s done, you should be able to see the contents in phpMyAdmin, like below:

Using the database

Querying the database is straightforward, and the IP Info DB website has plenty of examples at the bottom of the download page. Basically, once you have the user’s IP, you can use a query like this to find the most likely city it originated from:

SELECT * 
FROM `ip_group_city` 
WHERE `ip_start` <= INET_ATON([ip address as a string])
ORDER BY ip_start DESC
LIMIT 1;

The INET_ATON function converts a string that is the dotted-quad octet form of an IP address (the form you’re most used to seeing) into a 32-bit unsigned integer. This integer is then used to locate the record that most likely corresponds to the geographical location. The database is structured such that row with the highest ip_start value not greater than the given IP address is deemed to be the most likely location.

An example:

SELECT * FROM `ip_group_city` WHERE `ip_start` <= INET_ATON('69.156.150.155') ORDER BY ip_start DESC limit 1;

(This returns the city of Toronto, Ontario, Canada) If you want to get a list of likely locations, just increase the LIMIT argument to something like 10, and this will return 10 locations that are likely close to where the IP address is located. Note that there may be duplicates in this list.

A simple example

Here’s a simple PHP script that takes the user’s IP address and displays their most likely location on a map using the Google Static Maps API.

<?php
$ipGeolocationDatabaseName = 'ip_geolocation';

$link = mysql_connect('localhost', 'root', '');
$ipAddress = mysql_real_escape_string($_SERVER['REMOTE_ADDR']);

echo $ipAddress;

mysql_select_db($ipGeolocationDatabaseName, $link);

$query = "SELECT * FROM `ip_group_city` " . 
         "WHERE `ip_start` <= INET_ATON( '$ipAddress' ) " . 
         "ORDER BY ip_start DESC " . 
         "LIMIT 1";
$resultLocation = mysql_query($query);
  
$numRows = mysql_num_rows($resultLocation);
  
if (0 == $numRows)
{
  echo "No likely locations found.";
}

while ($row = mysql_fetch_array($resultLocation))
{
  echo "<pre>";
  print_r($row);
  echo "</pre>";

  $lat = urlencode($row['latitude']);
  $lng = urlencode($row['longitude']);
  if (!empty($lat) && !empty($lng))
  {
    // NOTE: Replace the `key` parameter with your own, obtained from:
    // http://code.google.com/apis/maps/signup.html
    // (The one in use is only good for URLs on `localhost`)
    $queryString = "center=$lat,$lng";
    $queryString .= "&zoom=10";
    $queryString .= "&size=300x300";
    $queryString .= "&markers=$lat,$lng,blue";
    $queryString .= "&key=ABQIAAAAicCf6MqoHlR-MsfivtVWPRT2yXp_ZAY8_ufC3CFXhHIE1NvwkxTNmqRdvnM9orG9QdyALHoUZfmFuQ";
    $queryString .= "&sensor=false";
    
    echo '<img src="http://maps.google.com/staticmap?' . htmlentities($queryString) . '" alt="location" />';
  }
}
?>

Here’s what it display for my IP address:

As you can see, it’s not always accurate, but it’s a good start to enhancing your website to offer localized services and features.