How to Get the Geographic Location of an IP Address

Published: December 26th, 2008 by:

An IP address used to mean nothing to us, other than where they are in the cyber world, but it is now possible to find out where they are in the real world, using databases that are freely available. In this article, I will disclose where you can get a free database that will tell you the geographic location of every IP address and how to use it.


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. :)


8 Responses to “How to Get the Geographic Location of an IP Address”

  • Chris

    Andrew,

    Thanks for this article – best and most comprehensive post that I have found on the subject. Cheers.

     

  • El Khalifa Karim

    In the Geolocation Sample code,
    you can do the same with only one SQL request like this


    line 10 : SELECT * FROM CityLocation WHERE locId = (SELECT locId FROM CityBlocks WHERE $ip BETWEEN startIpNum AND endIpNum) LIMIT 1;

    you can delete line 11 -> 16

     

  • diego

    Fatal error: Call to undefined function db_connect() in geolocation.php on line 4

    geolocation.php: your geolocation sample

    line 03 -> require(‘conectar.php’);

    _________________________________________

    conectar.php:

    What could i change to use the sample exemple???

    very thx!!!

     

  • fastreplies

    database.php says: Connected successfully but that is as far as we can get.

    What is the problem?

    cheers

     

  • GalTa

    gr8, 10x !!!

    Gr8 article. like it, and it helped me a lot. 

    what can i do in order to make if faster?
    I’m talking about the first SQL working with Range: “SELECT locId FROM CityBlocks WHERE $ip BETWEEN startIpNum AND endIpNum LIMIT 1

    I have index on startIpNum and endIpNum, but it’s taking something like 10-15 seconds !!!
    Any idea? someone?

    10x, 
    GalTa.

     

     

  • Andrew

    Ya, it’s a large table to search through. You may want to try indexing the IpNum columns. On one of my websites, I ended up caching the location in a smaller table of the frequently accessed IP addresses, so only new visitors hit a slow page, and only once.

     

  • sweety

    it’s cannot work for me(Geolocation). pls help me thanks.

     

  • Topala Bogdan

    Fast way
    alter table maxmind_blocks add index (startIPNum);
    alter table maxmind_location add index (locID);
     
    $sql = “SELECT * FROM maxmind_location WHERE locId = (SELECT locId FROM `maxmind_blocks` WHERE startIpNum <= “.$ip.” ORDER BY startIpNum DESC LIMIT 1) LIMIT 1″;
     
    thanks 
     

     

Leave a Reply





Wordpress doesn't like it when you post PHP code. Go save your code at pastebin, and post the link here.

About the Author

Andrew has been coding PHP applications since 2006, and has plenty of experience with PHP, MySQL, and Apache. He prefers Ubuntu Linux on his desktop and has plenty of experience at managing CentOS web servers. He is the owner of Wells IT Solutions LLC, and develops PHP applications full time for anyone that needs it as well as does desktop computer support locally in the local area. He spends most of his free time exploring new programming concepts and posting on The Webmaster Forums.