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