IP address geolocation SQL database :: IP Location Tools - Sent using Google Toolbar

IP address geolocation SQL database :: IP Location Tools

IP address geolocation SQL database

Introduction

The SQL database behind iplocationtools.com is offered for free. You get a table with city precision (1.4M rows) and another with country precision including CIDR (80k rows).

The database

SQL format Download link
CSV format Download link
Updated April 10 2009
(for those upgrading from the March release, please read this post)

How accurate is the data?

Very accurate and updated once a month. The data is partially from the free Maxmind geoiplookup command line tool while not being a clone or a copy of their database (around 50% smaller with same accuracy). Six scripts (48h!) are used to generate the database in batch and remove duplicate rows. You can test with your IP address on this page.

To comply with Maxmind licence : This product includes GeoLite data created by MaxMind, available from http://www.maxmind.com/.

Usage

The IP addresses are listed in table ip_group_city. The data is not in the 1.1.1.1 format since it would need to be stored as text and we dont want that for obvious reasons.

Let say for ip A.B.C.D, the formula is :

ip = ((A*256+B)*256+C)*256

(I assume A.B.C.0 is at the same location than A.B.C.255)

For example, if you have an ip of 74.125.45.100 (google.com), the formula would give a result of :

ip = ((74*256+125)*256+45)*256 = 1249717504

You would search for the IP address using MySQL by doing :

SELECT * FROM `ip_group_city` where `ip_start` <= INET_ATON('74.125.45.100') order by ip_start desc limit 1;

Or :

SELECT * FROM `ip_group_city` where `ip_start` <= 1249717504 order by ip_start desc limit 1;

and the result would be :

ip_start|country_code|region_code|city|zipcode|latitude|longitude
   1249717504|US|CA|Mountain View|94043|37.4192|-122.057

How about IP address by country?

Note : The ISO 3166 format is used for country code.

Using table ip_group_country you can do 2 things.

First, would be to locate an IP simply by country.

You would search this way :

SELECT * FROM `ip_group_country` where `ip_start` <= INET_ATON('74.125.45.100') order by ip_start desc limit 1;

Or

SELECT * FROM `ip_group_country` where `ip_start` <= 1249717504 order by ip_start desc limit 1;

Second, you might want to get the IP of a specific country to generate a blocklist with iptable, htaccess file or whatever you use. It would be done like this :

SELECT `ip_cidr` FROM `ip_group_country` WHERE `country_code` = 'AF' order by ip_start;

which would give you something like this :

63.243.149.0/24
  67.212.160.0/24
80.247.139.0/24
82.205.190.0/21
82.205.198.0/23
  [...]

How come there is only 1.4 millions records in the database?

Let me explain. Let say ips 74.125.0.XXX to 74.125.32.XXX have the same location (owned by google for example). I didn't store 32 times the same information, the database would have been huge for no reason. I stored only 74.125.0.XXX. The end of the block is the next row which would be 74.125.33.XXX

I don't want to store the database on my server, do you have an API?

Yes we do. Please visit this page

What is the difference between this database and Maxmind Geolite City?

Most of the data in Blogama IP Geolocation database is from Geolite City but it's optimized without compromising accuracy. The difference is 1.4 millions entries in our database compared to 3 millions in Maxmind one which means faster queries. Also, there is a table in our database with IP by country grouping (around 75000 entries) if you need less details on the geolocation. It takes around 2 days to generate the complete database.