Geolocation HomeProductsGeolocation Online DemoIPligence Community EditionContactFAQfreetools
FAQ - Frequently Asked Questions

1- How to convert a normal IP Address (192.168.0.1) to IP Long/decimal (3232235521) Format?

2- How to convert a IP Long/decimal (3232235521) address to a normal IP address (192.168.0.1) Format?

3- How to create the database table

4- How to import the data into the database

5- How to use and query the database

1- How to convert a normal IP Address (192.168.0.1) to IP Long/decimal (3232235521) Format?
An IP address (Internet Protocol address) is a unique number that devices use in order to identify and communicate with each other on a computer network utilizing the Internet Protocol standard (IP). IPv4 uses 32-bit (4 byte) addresses, which limits the address space to 4,294,967,296 (2^32) possible unique addresses.

Example of manual conversion:
A.B.C.D = D + (C * 256) + (B * 256 * 256) + (A * 256 * 256 * 256) =

192.168.0.1 = 1 + (0 * 256) + (168 * 256 * 256) + (192 * 256 * 256 * 256) = 3232235521

Example in ASP, converting IP dotted address to IP Long/decimal

 function IP2Long(ipadr)

result=0

faktorer=split(ipadr,".")

for ix=0 to 3

expn=3-ix

result=result + faktorer(ix) * 256 ^ expn

next

IP2Long=result

end function
Example in PHP, converting IP dotted address to IP Long/decimal

 function IP2LONG($a){

$d = 0.0;

$b = explode(".", $a,4);

for ($i = 0; $i < 4; $i++) {

$d *= 256.0;

$d += $b[$i];
};

return $d;
}
Example in PERL, converting IP dotted address to IP Long/decimal

 sub IP2LONG {

my $address = @_[0];

($a, $b, $c, $d) = split '\.', $address;

$decimal = $d + ($c * 256) + ($b * 256**2) + ($a * 256**3);

return $decimal;

}
Example in VB.NET, converting IP dotted address to IP Long/decimal

 Private Function IP2LONG(ByVal IPAddress As Object) As Object

Dim x As Integer
Dim Pos As Integer
Dim PrevPos As Integer
Dim Num As Integer

If UBound(Split(IPAddress, ".")) = 3 Then

' On Error Resume Next

For x = 1 To 4

Pos = InStr(PrevPos + 1, IPAddress, ".", 1)

If x = 4 Then Pos = Len(IPAddress) + 1

Num = Int(Mid(IPAddress, PrevPos + 1, Pos - PrevPos - 1))

If Num > 255 Then

ConvertToLong = "0"

Exit Function

End If

PrevPos = Pos

ConvertToLong = ((Num Mod 256) * (256 ^ (4 - x))) + ConvertToLong

Next

End If

End Function

2- How to convert a IP Long/decimal (3232235521) address to a normal IP address (192.168.0.1) Format?
Example in ASP, converting IP Long / decimal to IP dotted address

 Function LONG2IP(ByVal asNewIP)

Dim lnResults
Dim lnIndex
Dim lnIpAry

lnIpAry = Split(asNewIP, ".", 4)

For lnIndex = 0 To 3

If Not lnIndex = 3 Then

lnIpAry(lnIndex) = lnIpAry(lnIndex) * (256 ^ (3 - lnIndex))

End If

lnResults = lnResults + lnIpAry(lnIndex)

Next
CLngIP = lnResults
End Function
Example in PHP, converting IP Long / decimal to IP dotted address

 function LONG2IP($a){
$b=array(0,0,0,0);

$c = 16777216.0;

$a += 0.0;

for ($i = 0; $i < 4; $i++) {

$k = (int) ($a / $c);

$a -= $c * $k;

$b[$i]= $k;

$c /=256.0;
};

$d=join('.', $b);

return($d);
}

Example in PERL, converting IP Long / decimal to IP dotted address

 sub dec2dot {

my $address = @_[0];

$d = $address % 256; $address -= $d; $address /= 256;

$c = $address % 256; $address -= $c; $address /= 256;

$b = $address % 256; $address -= $b; $address /= 256;

$a = $address;

$dotted="$a.$b.$c.$d";

return $dotted;

}

3- How to create the database table
Creating the tables is your first step towards using any IPligence product, in case you are using mysql you can avoid this step by using the mysql-ready dump available for download, the dump will create any necessary tables for you.

We strongly recommend the use of Indexes for our BASIC and MAX products due to the quantity of records these data sets feature, the mysql examples shown bellow contain the index over the ip_to field. Using indexes correctly should benefit you and reduce the query time down to miliseconds.


 Create table for IPligence LITE/COMMUNITY using MYSQL:

 create table ipligence
(
ip_from int UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
ip_to int UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
country_code varchar(10) NOT NULL,
country_name varchar(255) NOT NULL,
continent_code varchar(10) NOT NULL,
continent_name varchar(255) NOT NULL,
PRIMARY KEY( ip_to)
);

Create table for IPligence BASIC using MYSQL:

 create table ipligence
(
ip_from int UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
ip_to int UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
country_code varchar(10) NOT NULL,
country_name varchar(255) NOT NULL,
continent_code varchar(10) NOT NULL,
continent_name varchar(255) NOT NULL,
time_zone varchar(10) NOT NULL,
region_code varchar(10) NOT NULL,
region_name varchar(255) NOT NULL,
owner varchar(255) NOT NULL,
PRIMARY KEY( ip_to)
);

Create table for IPligence MAX using MYSQL:

 create table ipligence
(
ip_from int UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
ip_to int UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
country_code varchar(10) NOT NULL,
country_name varchar(255) NOT NULL,
continent_code varchar(10) NOT NULL,
continent_name varchar(255) NOT NULL,
time_zone varchar(10) NOT NULL,
region_code varchar(10) NOT NULL,
region_name varchar(255) NOT NULL,
owner varchar(255) NOT NULL,
city_name varchar(255) NOT NULL,
county_name varchar(255) NOT NULL,
latitude double NOT NULL,
longitude double NOT NULL,
PRIMARY KEY( ip_to)
);

Create table for IPligence LITE/COMMUNITY using Microsoft SQL:

 CREATE TABLE [dbo].[ipligence]
(
[ip_from] NUMERIC(11) NOT NULL,
[ip_to] NUMERIC(11) NOT NULL,
[country_code] NVARCHAR(10) NOT NULL,
[country_name] NVARCHAR(255) NOT NULL,
[continent_code] NVARCHAR(10) NOT NULL,
[continent_name] NVARCHAR(255) NOT NULL
);

Create table for IPligence BASIC using Microsoft SQL:

 CREATE TABLE [dbo].[ipligence]
(
[ip_from] NUMERIC(11) NOT NULL,
[ip_to] NUMERIC(11) NOT NULL,
[country_code] NVARCHAR(10) NOT NULL,
[country_name] NVARCHAR(255) NOT NULL,
[continent_code] NVARCHAR(10) NOT NULL,
[continent_name] NVARCHAR(255) NOT NULL,
[time_zone] NVARCHAR(10) NOT NULL,
[region_code] NVARCHAR(10) NOT NULL,
[region_name] NVARCHAR(255) NOT NULL,
[owner] NVARCHAR(255) NOT NULL
);

Create table for IPligence MAX using Microsoft SQL:

 CREATE TABLE [dbo].[ipligence]
(
[ip_from] NUMERIC(11) NOT NULL,
[ip_to] NUMERIC(11) NOT NULL,
[country_code] NVARCHAR(10) NOT NULL,
[country_name] NVARCHAR(255) NOT NULL,
[continent_code] NVARCHAR(10) NOT NULL,
[continent_name] NVARCHAR(255) NOT NULL,
[time_zone] NVARCHAR(10) NOT NULL,
[region_code] NVARCHAR(10) NOT NULL,
[region_name] NVARCHAR(255) NOT NULL,
[owner] NVARCHAR(255) NOT NULL,
[city_name] NVARCHAR(255) NOT NULL,
[county_name] NVARCHAR(255) NOT NULL,
[latitude] FLOAT NOT NULL,
[longitude] FLOAT NOT NULL
);

Create table for IPligence LITE/COMMUNITY using ORACLE:

 create table ipligence
(
ip_from NUMBER NOT NULL,
ip_to int NUMBER NOT NULL,
country_code varchar(10) NOT NULL,
country_name varchar(255) NOT NULL,
continent_code varchar(10) NOT NULL,
continent_name varchar(255) NOT NULL
);

Create table for IPligence BASIC using ORACLE:

 create table ipligence
(
ip_from NUMBER NOT NULL,
ip_to int NUMBER NOT NULL,
country_code varchar(10) NOT NULL,
country_name varchar(255) NOT NULL,
continent_code varchar(10) NOT NULL,
continent_name varchar(255) NOT NULL,
time_zone varchar(10) NOT NULL,
region_code varchar(10) NOT NULL,
region_name varchar(255) NOT NULL,
owner varchar(255) NOT NULL
);

Create table for IPligence MAX using ORACLE:

 create table ipligence
(
ip_from NUMBER NOT NULL,
ip_to int NUMBER NOT NULL,
country_code varchar(10) NOT NULL,
country_name varchar(255) NOT NULL,
continent_code varchar(10) NOT NULL,
continent_name varchar(255) NOT NULL,
time_zone varchar(10) NOT NULL,
region_code varchar(10) NOT NULL,
region_name varchar(255) NOT NULL,
owner varchar(255) NOT NULL,
city_name varchar(255) NOT NULL,
county_name varchar(255) NOT NULL,
latitude NUMBER NOT NULL,
longitude NUMBER NOT NULL

);

4- How to import the data into the database
All IPligence products are available in two downloadable formats, CSV (comma separated values) and MYSQL DUMP.

If you are using Microsoft SQL you should be able to use the SQL Server import wizard available from tools->Import Data. We strongly recommend reading our Microsoft SQL step by step guide on how successfully importing the data, you can download this guide in PDF format here: http://www.ipligence.com/pdf/howto-mssql2000.pdf

If you are using Oracle, please reffer to the following FAQ, http://www.orafaq.com/faqloadr.htm

For Mysql users, you can choose either to use the LOAD DATA INFILE command ( LOAD DATA INFILE "/path to file.csv" INTO TABLE ipligence FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';) , (http://dev.mysql.com/doc/refman/5.1/en/load-data.html).

Or by using the Mysql specific dump file and issuing the following shell command "mysql -u user -p db_name < backup-file.sql"

5- How to use and query the database
We want to know the owner, latitude, longitude, city_name,coutry_name details for the ip address 209.132.177.50, after converting the ipaddress to IP Number/Decimal with any of the methods described above (question #1) the result is 3515134258, we have now to query the database for the fields ip_from and ip_to, where ip_from is equal or bigger than the ipaddress and ip_to equal or lower than the ip address.

This translates to a query like this one (example in mysql format):

 SELECT owner,latitude,longitude,city_name,country_name FROM ipligence WHERE ip_from <= '3515134258' and ip_to >= '3515134258' LIMIT 1;
As result the dataset in IPligence Max gives us back the following:

+------------------+----------+-----------+-----------+---------------+
| owner | latitude | longitude | city_name | country_name |
+------------------+----------+-----------+-----------+---------------+
| RED HAT SOFTWARE | 33.4481 | -112.0732 | PHOENIX | UNITED STATES |
+------------------+----------+-----------+-----------+---------------+
1 row in set (0.00 sec)
Alternatively, you can take advantage of the sql function INET_ATON(), which translates the IP address to IP Number/Decimal for you, check if your SQL database supports such function.

The query using INET_ATON() looks like this:

SELECT owner,latitude,longitude,city_name,country_name FROM ipligence WHERE ip_from <= INET_ATON('209.132.177.50') and ip_to >= INET_ATON('209.132.177.50') LIMIT 1;
You can also use INET_NTOA() to translate a IP Number/Decimal to a normal IP address dotted format:

select INET_NTOA('3515134258');
The result is:
+-------------------------+
| INET_NTOA('3515134258') |
+-------------------------+
| 209.132.177.50 |
+-------------------------+
1 row in set (0.00 sec)
2006 (c) Copyright IPligence. All rights reserved.