Find all your DIY electronics in the MakerShed. 3D Printing, Kits, Arduino, Raspberry Pi, Books & more!

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


Related

Comments

  1. ehrichweiss says:

    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)

  2. A says:

    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.

    1. ahamed says:

      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.

  3. 2bithacker says:

    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)

  4. Tim G. says:

    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

  5. kanaga.myopenid.com says:

    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.

  6. geolocation says:

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

  7. rg443 says:

    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;

  8. Maldini Antonio says:

    The link at the end of article is broken…

In the Maker Shed