Free, unlimited IP address geolocation with MySQL

There are a lot of services and datasets that provide IP address geolocation, allowing you to detect a web user’s city of origin based on their incoming IP. Unfortunately, most of these services cost quite a bit of money, impose limits on how many lookups you can do over a period of time, or aren’t kept up to date with accurate information.

I came across a great resource today, put together by Marc-Andre Caron. He’s done all the necessary legwork to solve this problem, putting together a free, monthly-updated MySQL dataset that will allow you to derive country, region, city, zip, latitude, and longitude from an IP address.

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
(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 = 4881709

You would search for the IP address using MySQL by doing :
SELECT * FROM `ip_group_city` where `ip_start` <= 4881709 order by ip_start desc limit 1;

Keep in mind that the accuracy of the data is usually down to the location of a user’s ISP. Don’t expect this to get you down to a street address, but if you want to display relevant content at a city, state, or country level, this will do the trick the vast majority of the time.

IP address geolocation SQL database

9 Responses to Free, unlimited IP address geolocation with MySQL

ehrichweiss on March 24th, 2009 at 7:57 am said:

I’ve seen these but never got to use them since they’re usually expensive. There are free ones but they don’t usually tell more than the country an IP is in. This one is awesome. The guy even gives a way to block entire countries so maybe those Turkish website defacers will have some more probs(at least until they turn to Tor I guess)

Reply ↓
A on March 24th, 2009 at 11:09 am said:

I found a great free one: ip-adress.com (only one d in address). It lets you have 3 lookups per day free, but if you get an account, you get 50. It’s fairly accurate, and it’s what I use. Although, if you need to look up more than 50, or want some way to automate it, then that’s not the site for you.

Reply ↓
- ahamed on April 20th, 2009 at 10:18 pm said:
  
  hi, I found a free checkup ip details from http://www.ip-details.com. Its very easy to checkup our ip address, downloading and uploading speed, etc.
  
  Reply ↓
2bithacker on March 24th, 2009 at 2:49 pm said:

You know, IP addresses are just numbers to start with. The dotted-quad notation is just to make them easier for humans to deal with. MySQL has a built-in set of functions, inet_aton() and inet_ntoa() which convert IP address strings to/from their corresponding integers.

To get the equivalent integer from an IP using these functions;

mysql> select truncate( inet_aton(“74.125.45.100″) / 256, 0 );
+————————————————-+
| truncate( inet_aton(“74.125.45.100″) / 256, 0 ) |
+————————————————-+
| 4881709 |
+————————————————-+
1 row in set (0.00 sec)

Reply ↓
Tim G. on April 10th, 2009 at 12:10 pm said:

From:
The formula would give a result of :
ip = (74*256+125)*256+45 = 4881709

To:
The formula would give a result of :
ip = ((74*256+125)*256+45) * 256 = 1249717504

Reply ↓
kanaga.myopenid.com on June 18th, 2009 at 11:31 pm said:

hai,
It’s nice way to find the geolocation using my sql.
There is a site called http://www.ip-details.com/. It is used to find the geo location using ip address.

Reply ↓
geolocation on January 18th, 2010 at 1:30 pm said:

I found a new site to check out IP Address details through
http://www.ipaddressgeolocation.com

Reply ↓
rg443 on December 31st, 2012 at 7:36 am said:

Reblogged this on rg443blog and commented:
Free, unlimited IP address geolocation with MySQL
ip = (A*256+B)*256+C
ip = (74*256+125)*256+45 = 4881709
SELECT * FROM `ip_group_city` where `ip_start` <= 4881709 order by ip_start desc limit 1;

Reply ↓
Maldini Antonio on May 14th, 2013 at 2:52 am said:

The link at the end of article is broken…

Reply ↓