IP address geolocation SQL database

Introduction

The SQL database behind ipinfodb.com is offered for free. We offer the database in different formats (SQL, CSV), city or country precision, 3 or 4 IP digits precision and data in single or multiple tables. Available information in the database : ISO country code, country name, FIPS region code, region name, city, zipcode, latitude, longitude and GMT/DST timezone. The database is updated during the first week of each month.

If you use the database on a regular basis, please consider making a donation. Everything on this site is totally free but we need donations in order to pay for hosting and dedicated servers.

IP geolocation databases download

Updated Feb 8 2010.

Database	Data Tables	Uncompressed Size with indexes (MB)	IP Precision	IP records	Format
Small (City)	One	103.3	123.123.123	1.3M	SQL CSV
Small (City)	Multiple	34.1	123.123.123	1.3M	SQL CSV
Complete (City)	One	284.5	123.123.123.123	3.7M	SQL CSV
Complete (City)	Multiple	96.6	123.123.123.123	3.7M	SQL CSV
Complete (Country)	One	11.2	123.123.123.123	173K	SQL CSV
Complete (Country)	Multiple	9.3	123.123.123.123	173K	SQL CSV

Don't want to store the database locally? We offer a free high availability IP Geolocation API

Database versions

The database is offered in 6 flavors. First, all databases are either in one or multiple tables. Single tables make queries faster (no join) but they take more disk space. Second, you can choose between 123.123.123.0 or 123.123.123.123 precision. The 3 digit precision database is 65% smaller and still offers a pretty good accuracy, the last digit IPs (123.123.123.0-255) are normally very close to each other. Third, you can choose city or country precision. City database includes the country database with IP CIDR (eg : 123.123.123.0/24).

Data accuracy

Over 99.5% on a country level and around 80% on a city level for the US within a 25 mile radius with the complete database. With the small version you get very good accuracy while having 65% less row. Most IP in the 123.123.123.0-255 range are very close to each others.

You can try a few lookups on this page.

The data is compiled from the free Maxmind CSV database (Geolite City) and rearranged with many scripts. We use other sources such as Geonames for timezones, FIPS for regions name and IANA for IP assignement. If you wan't better precision on a city level, MaxMind offers great commercial products. In no way our site is related to Maxmind but since we use their free database to build ours, we wanted to thank them by putting a link to their site for those that can afford their commercial products.

Usage

The IP addresses are listed in table ip_group_city.

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

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

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 + 100 = 1249717604

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` <= 1249717604 order by ip_start desc limit 1;

and the result would be (with one table DB):

ip_start|country_code|country_name|region_code|region_name|city|zipcode|latitude|longitude|gmtOffset|dstOffset

1249717504|US|United States|06|California|Mountain View|94043|37.4192|-122.057|-8.0|-7.0

IP 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;

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 :

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
[...]

Timezones

Timezone data is from Geonames. Basically, the timezone field is the standard timezone. The gmtOffset/dstOffset fields names are a little misleading --> gmtOffset: offset to GMT at 1st January and dstOffset: offset to GMT at 1st July. Please refer to Geonames.org for more info about timezones.

Keeping in touch for updates

The best way to get updates and news is with our RSS feed or on Twitter. Also, we now offer rsync at this address :

rsync://rsync.ipinfodb.com/ipinfodb

IP Geolocation API

For those that don't want to store the database locally, we have an IP geolocation API. Please visit this page

Support

For all non googable questions, visit the forum. We also offer custom integration.

Free IP address geolocation tools