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 = 4881709You 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.
18 thoughts on “Free, unlimited IP address geolocation with MySQL”
Comments are closed.
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)
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.
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.
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)
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
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.
I found a new site to check out IP Address details through
http://www.ipaddressgeolocation.com
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;
The link at the end of article is broken…