The database I’m going to use is freely available from MaxMind. They are a company that provides geolocation technologies and credit card fraud detection. We will be using their free database in this article.

Importing the Data

We will be importing the data into a MySQL database, so let’s prep the database first. Create an empty database on your server, and add the following tables:

--
-- Table structure for table `CityBlocks`
--

CREATE TABLE `CityBlocks` (
  `startIpNum` bigint(10) NOT NULL,
  `endIpNum` bigint(10) NOT NULL,
  `locId` mediumint(9) NOT NULL,
  KEY `startIpNum` (`startIpNum`,`endIpNum`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Table structure for table `CityLocation`
--

CREATE TABLE `CityLocation` (
  `locId` mediumint(9) NOT NULL,
  `country` varchar(2) NOT NULL,
  `region` varchar(55) NOT NULL,
  `city` varchar(55) NOT NULL,
  `postalCode` varchar(10) NOT NULL,
  `latitude` decimal(7,4) NOT NULL,
  `longitude` decimal(7,4) NOT NULL,
  `metroCode` varchar(3) NOT NULL,
  `areaCode` varchar(3) NOT NULL,
  KEY `locId` (`locId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

There are two tables because one location can have more than one IP address, or even an IP range. For all you database gurus, that would be a one (location) to many (IP addresses) relationship.

Once the database tables are in place, download the latest GeoLite City database in CSV Format. Extract the archive, and make note of where the CSV files are extracted. Next, import the files into their respective tables with these two queries:

--
-- Import data for `CityBlocks`
--
LOAD DATA LOCAL INFILE 'GeoLiteCity-Blocks.csv' 
INTO TABLE CityBlocks 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '\"' 
LINES TERMINATED BY '\n' 
IGNORE 2 LINES 
(startIpNum, endIpNum, locId)

--
-- Import data for `CityLocation`
--
LOAD DATA LOCAL INFILE 'GeoLiteCity-Location.csv' 
INTO TABLE CityLocation 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '\"' 
LINES TERMINATED BY '\n' 
IGNORE 2 LINES 
(locId,country,region,city,postalCode,latitude,longitude,metroCode,areaCode)

You may have to insert the absolute path to the data files, so ‘GeoLiteCity-Blocks.csv’ might be ‘/path/to/GeoLiteCity-Blocks.csv’ for a Linux enviornment.

Once these queries are processed, there should be ~3 million records in the CityBlocks table and ~200k records in the CityLocation table. Now we are ready to query this data.

Querying and Using the Data

The first thing we need is the visitor’s IP in long form. PHP has a simple function, ip2long(), that takes care of this. The long version of the IP address is stored in the database because a certain location can contain a range of IP addresses.

PHP will return a signed integer on some systems. I encountered this problem with I developed an application on a 64-bit server, and deployed it on a 32-bit server. When I moved the PHP application, IP addresses wern’t found that should be. Long story short, I learned from this post that 32-bit systems return a signed integer. We don’t want that, so you will see me use sprintf() with ‘%u’ to force an unsigned integer and eliminate this problem.

Once we get the visitor’s IP address, fetching the data here can be done in two simple queries:

SELECT locId FROM CityBlocks WHERE $ip BETWEEN startIpNum AND endIpNum LIMIT 1

-- PHP code to fetch the row and assign the locID to $locId --

SELECT * FROM CityLocation WHERE locId = $locId LIMIT 1

We will get a result like this after the second query:

array(9) {
  ["locId"]=>
  string(5) "14237"
  ["country"]=>
  string(2) "US"
  ["region"]=>
  string(2) "TX"
  ["city"]=>
  string(6) "Austin"
  ["postalCode"]=>
  string(5) "78773"
  ["latitude"]=>
  string(7) "30.3264"
  ["longitude"]=>
  string(8) "-97.7713"
  ["metroCode"]=>
  string(3) "635"
  ["areaCode"]=>
  string(3) "512"
}

So there you have it. That should be all you need to create your own geolocation app using PHP & MySQL. I will continue with a couple more advanced code samples to show how this can be expanded and applied.

Automating the Data Imports

The script is pretty self-explanatory and commented pretty well, so I won’t explain it line-for line. Replace line ~79 with your own DB connection code, and make sure that the database link is assigned to $db. Please post a comment if you have any questions.

It is not recommended to run this script repeatedly as it puts an unnecessary bandwidth load on maxmind.com when this script is run again and again. If you are using this script, and you have issues where it imports data into the database, comment line ~71 where wit_download_file() is called, so it’s not downloading that huge file every time you try it.

<?php
	#die('remove line 2 before running'); /* remove this line before running */
	
/**
 * Download any remote file using PHP's cURL extension
 * 
 * @param string $source the URL to download
 * @param string $destination where the file is to be saved
 * @return bool
 */
	function wit_download_file($source, $destination, &$err_str)
	{
		$destination = fopen($destination, 'w');
		
		if (!$destination)
		{
			return false;
		}
		
		$ch = curl_init();
		 
		curl_setopt($ch, CURLOPT_FILE, $destination);
		curl_setopt($ch, CURLOPT_HEADER, 0);
		curl_setopt($ch, CURLOPT_URL, $source);
		
		curl_exec($ch);
		$err_str = curl_error($ch);
		curl_close($ch);
		
		fclose($destination);
		
		return ($err_str == '') ? true : false;
	}
	
/**
 * actual script starts here
 */
	header('Content-type: text/plain');
	
	$link = 'http://geolite.maxmind.com/download/geoip/database/GeoLiteCity_CSV/GeoLiteCity-latest.zip';
	
	/* download the file - may take some time */
	echo "Downloading data file...\n";
	wit_download_file($link, 'GeoLiteCity.zip', $errstr) or die('Failed to download data file: ' . $errstr);
	
	/* extract the data file */
	echo "Extracting...\n";
	if (!is_dir('geoip')) mkdir('geoip', 0744);
	shell_exec('unzip -oj GeoLiteCity.zip -d geoip/');
	
	echo "Inserting into database...\n";
	require('../includes/database.php');
	$db = db_connect();
	
	/* load the CityBlocks data */
	mysql_query("TRUNCATE TABLE CityBlocks");
	$query = "LOAD DATA LOCAL INFILE 'geoip/GeoLiteCity-Blocks.csv' 
		INTO TABLE CityBlocks FIELDS TERMINATED BY ',' ENCLOSED BY '\"' 
		LINES TERMINATED BY '\n' IGNORE 2 LINES (startIpNum, endIpNum, locId)";
	$result = mysql_query($query);
	
	/* load the location data */
	mysql_query("TRUNCATE TABLE CityLocation");
	$query = "LOAD DATA LOCAL INFILE 'geoip/GeoLiteCity-Location.csv' 
		INTO TABLE CityLocation FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' 
		LINES TERMINATED BY '\n' IGNORE 2 LINES 
		(locId,country,region,city,postalCode,latitude,longitude,metroCode,areaCode)";
	$result = mysql_query($query);
	
	echo "Optimizing Tables...\n";
	mysql_query("OPTIMIZE TABLE CityBlocks");
	mysql_query("OPTIMIZE TABLE CityLocation");
	
	echo "Deleting temp directory...\n";
	shell_exec('rm -rf geoip/');
	
	echo "Done!\n";
?>

Geolocation Sample

Here is a simple example that gets the location of the current visitor, according to Maxmind’s free GeoCity database, and plots it on a Google Map. Please note that you will have to change the Google API Key if you plan on using this code on your own site.

Run This Example

<?php
	/* replace with your own DB connection code */
	require('../includes/database.php');
	$db = db_connect();
	
	/* get the IP address and make sure it is an unsigned integer */
	$ip = sprintf('%u', ip2long($_SERVER['REMOTE_ADDR']));
	
	/* fetch the location id */
	$query = "SELECT locId FROM CityBlocks WHERE $ip BETWEEN startIpNum AND endIpNum LIMIT 1";
	$result = mysql_query($query, $db) or die(mysql_error());
	$row = mysql_fetch_assoc($result);
	
	/* now fetch the location */
	$locId = $row['locId'];
	$query = "SELECT * FROM CityLocation WHERE locId = $locId LIMIT 1";
	$result = mysql_query($query, $db) or die(mysql_error());
	$location = mysql_fetch_assoc($result);
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>IP Geolocation Example</title>
<meta name="generator" content="Bluefish 1.0.7">
<meta name="author" content="Andrew Wells">
<meta name="date" content="2008-12-26T16:48:50-0600">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<meta http-equiv="content-type" content="application/xhtml+xml; charset=UTF-8">
<script src="http://maps.google.com/maps?file=api&amp;v=2&amp;key=ABQIAAAAx6XuOYsDLrUX-imS2wkJshQcGiGm0nt-HEPscddBWrEOoLM0sRSH68H2XyBqlLiKczSojl9wJMHkqw"type="text/javascript"></script>
<script type="text/javascript">
//<![CDATA[
	function load()
	{
		if (GBrowserIsCompatible())
		{
			var map = new GMap2(document.getElementById("map"));
			map.setCenter(new GLatLng(<?=$location['latitude']?>, <?=$location['longitude']?>), 10);
			map.addControl(new GMapTypeControl());
			map.addControl(new GSmallMapControl());
			
			var point = new GLatLng(<?=$location['latitude']?>, <?=$location['longitude']?>);
			marker = new GMarker(point);
			map.addOverlay(marker);
			marker.openInfoWindowHtml('Lat/Lon: <?=$location['latitude']?>, <?=$location['longitude']?><br />City: <?=$location['city']?><br />Region: <?=$location['region']?><br />');
		}
	}
//]]>
</script>

</head>
<body onload="load()" onunload="GUnload()">
<h2>Map for (<?=$_SERVER['REMOTE_ADDR']?>):</h2>
<div id="map" style="width:800px;height:600px"></div>

<h2>Details from DB Record:</h2>
<pre><?php var_dump($location); ?></pre>
</body>
</html>

The application possibilities with this data is limitless. Companies have been known to use this data for targeted advertising, usage statistics, and even fraud detection. How you will use it in your own application is up to you. 🙂