Ip address to country with database [closed]

Question

I have downloaded ip-to-country.csv that has ip ranges that are mapped to countries. How should I store this data to database and how can I query in what range Ip address is to know where Ip address is coming from?

Orelee

Answer 1

I wrote a small lib called ip2c to do just that. it uses the database from webhosting.info but also supports that from Software77.

It converts the CSV info a compact binary format and can do the search straight on the file, in memory or in a memory mapped file.

The Java API usage is similar to this:

String ip = 85.64.225.159;
int caching1 = IP2Country.NO_CACHE;  // Straight on file, Fastest startup, slowest queries
int caching2 = IP2Country.MEMORY_MAPPED; // Memory mapped file, fast startup, fast queries.
int caching3 = IP2Country.MEMORY_CACHE; // load file into memory, slowerst startup, fastest queries
IP2Country ip2c = new IP2Country(caching1);
Country c = ip2c.getCountry(ip);
if (c == null)
{
        System.out.println("UNKNOWN");                          
}
else
{
        // will output IL ISR ISRAEL
        System.out.println(c.get2cStr() + " " + c.get3cStr() + " " + c.getName());      
}

Royden

that is great tool, but sadly my server returns ipv6 addresses – Orelee

You will have problems with ipv6 addresses in pretty much any geoip solution right now. it's just not there yet (and I don't think there are ipv6 geoip databases). if you are not really using ipv6, see if you can convince your server to talk ipv4 (by blacklisting ipv6 module on linux, for example). – Royden

Hi, I found this ip2location.com/IPV6-COUNTRY.aspx , i don't know if that is compitable with your solution .... – Orelee

it wont be. my solution will only work with those two ipv4 databases. other ipv4 database may be possible to support, but not ipv6 (without a major rewrite). – Royden

@Omry its very late but i was trying to use your project in my spring mvc application but i am getting a file not found link please see if you can help, i have looked around a lot and no success – Baldridge

Answer 2

Have a look to the IP-to-Country Handbook
The ip-to-country.csv file contains five fields:

* Begining of IP address range
* Ending of IP address range
* Two-character country code based on ISO 3166
* Three-character country code based on ISO 3166
* Country name based on ISO 3166

You can import this data into any database by creating a table with the following fields:

FIELD           DATA TYPE           FIELD DESCRIPTION
IP_FROM         NUMERICAL (DOUBLE)  Beginning of IP address range.
IP_TO           NUMERICAL (DOUBLE)  Ending of IP address range.
COUNTRY_CODE2    CHAR(2)            Two-character country code based on ISO 3166.
COUNTRY_CODE3    CHAR(3)            Three-character country code based on ISO 3166.
COUNTRY_NAME     VARCHAR(50)        Country name based on ISO 3166

You can query the above table, after you have imported the data into it, to find the country of a corresponding IP Number by issuing the following Select statement:

SELECT COUNTRY_NAME FROM <TableName> WHERE IP_FROM <= IP Number and IP_TO >= IP Number

where IP Number of a given A.B.C.D IP, is calculated by :

IP Number = A x (256*256*256) + B x (256*256) + C x 256 + D

Monagan

Answer 3

For IPv4 you can store in the following format:

1. Convert IP addresses to integers. So 127.0.0.1 will become 2 147 483 649
2. Store your table as triple IPFrom IPTo Country
3. Build indices for IpFrom and IpTo

When you need to look up the IP address execute the following query

SELECT Country from GeoIP where  IpFrom < $IP  and $IP < $IpTo

This will give you the country for the IP address

Spritsail

Hit: ...WHERE $ip BETWEEN ipFrom AND ipTo - just better looking ;) – Peewee

Answer 4

You can build index only for ipTo (high boundary) values of ranges and use query:

  select country from geoip where $ip <= ipTo limit 1

(it's assumed that ranges do not overlap as in MaxMind database)

Sothena

At least for the MaxMind database, this works (there's an explanation here: andy.wordpress.com/2007/12/16/…), and it is around 2 orders of magnitude faster than the BETWEEN query with the indices needed there. Thanks oraz! I only saw this after reading your anwer. – Outspoken