Ip address to country with database

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?

Answer 1

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

Answer 2

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)

Answer 3

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

Answer 4

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 quries.
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());      
}

Answer 5

I think you can find some geolocation solution from IP2Location.com. I have downloaded the sample database and PHP & ASP.Net source code from the Website and follow tutorial at here to implement into my websites, it's working for recording my visitor log, show where's my visitor came from, and redirect my webs visitor to correct language site.