FAQs for IP2Location™ IP-Country-Region-City-ISP-Domain Database [DB7]
- What is the database format?
- How do I convert a IP Address to a IP Number?
- How do I retrieve the country, region or state, city, Internet Service Provider (ISP) or company name, and domain name data from the IP Number?
- What is the minus sign "-" in country name or country code?
- How do I use this database?
- Can I automate the database update download process?
- How do I retrieve visitor's IP address using ASP, ASP.NET, C#, VB.NET, PHP, JSP & ColdFusion?
- How do I create the SQL table?
- How do I import the database into MS-SQL/MySQL/PostgreSQL database?
- Will I receive monthly notification when the update is available?
- How many records are available in this demo version?
- Where can I test the demo version for free?
- Why do we need to update this database periodically?
- How do I perform monthly database update?
- I want to download the full version. What should I do now?
- What do I get if I purchase the full version?
- Can I resell or reuse the database in my client application?
- How do I upgrade to other package?
- How can I use the Weather Station Code to get the live weather data or forecast?
- What is the IP2Location updates schedule for year 2013-2016?
- How many countries are included in the database? What is the accuracy?
- How accurate is the ISP name in every IP address block?
- How do I test the location using multiple IP address?
The database format is known as Comma Separated Values (CSV). All fields are separated by a comma and each individual line is a record by itself.
IP2Location is also available in binary format which works together with the IP2Location API in several programming languages.
IP address (IPV4) is divided into 4 sub-blocks. Each sub-block has a different weight number each powered by 256. IP number is being used in the database because it is more efficient to search between a range of numbers in a database.
The Beginning IP number and Ending IP Number are calculated based on the following formula:
IP Number = 16777216*w + 65536*x + 256*y + z (1)
where
IP Address = w.x.y.z
For example, if the IP address is "202.186.13.4", then its IP Number will be "3401190660", based on the formula (1).
IP Address = 202.186.13.4
So, w = 202, x = 186, y = 13 and z = 4
IP Number = 16777216*202 + 65536*186 + 256*13 + 4
= 3388997632 + 12189696 + 3328 + 4
= 3401190660
To reverse IP number to IP address,
w = int ( IP Number / 16777216 ) % 256
x = int ( IP Number / 65536 ) % 256
y = int ( IP Number / 256 ) % 256
z = int ( IP Number ) % 256
where
%
is the modulus operator and
int
returns the integer part of the division.
Example ASP Function To Convert IP Address to IP NumberFunction Dot2LongIP (ByVal DottedIP)
Dim i, pos
Dim PrevPos, num
If DottedIP = "" Then
Dot2LongIP = 0
Else
For i = 1 To 4
pos = InStr(PrevPos + 1, DottedIP, ".", 1)
If i = 4 Then
pos = Len(DottedIP) + 1
End If
num = Int(Mid(DottedIP, PrevPos + 1, pos - PrevPos - 1))
PrevPos = pos
Dot2LongIP = ((num Mod 256) * (256 ^ (4 - i))) + Dot2LongIP
Next
End If
End Function
Example PHP Function To Convert IP Address to IP Number
function Dot2LongIP ($IPaddr)
{
if ($IPaddr == "") {
return 0;
} else {
$ips = split ("\.", "$IPaddr");
return ($ips[3] + $ips[2] * 256 + $ips[1] * 256 * 256 + $ips[0] * 256 * 256 * 256);
}
}
Example ColdFusion Function To Convert IP Address to IP Number
function Dot2LongIP(ipAddress) { if(arguments.ipAddress EQ "") { return 0; } else { ips = ListToArray( arguments.ipAddress, "." ); return( ( 16777216 * ips[1] ) + ( 65536 * ips[2] ) + ( 256 * ips[3] ) + ips[4] ); } }
Example C# Function To Convert IP Address to IP Number
public double Dot2LongIP(string DottedIP)
{
int i;
string [] arrDec;
double num = 0;
if (DottedIP == "")
{
return 0;
}
else
{
arrDec = DottedIP.Split(".");
for(i = arrDec.Length - 1; i >= 0 ; i --)
{
num += ((int.Parse(arrDec[i])%256) * Math.Pow(256 ,(3 - i )));
}
return num;
}
}Example VB.NET Function To Convert IP Address to IP Number
Public Function Dot2LongIP(ByVal DottedIP As String) As Double
Dim arrDec() As String
Dim i As Integer
Dim intResult As Long
If DottedIP = "" then
Dot2LongIP = 0
Else
arrDec = DottedIP.Split(".")
For i = arrDec.Length - 1 To 0 Step -1
intResult = intResult + ((Int(arrDec(i)) Mod 256) * Math.Pow(256, 3 -i))
Next
Dot2LongIP = intResult
End If
End function
Example MS SQL Function To Convert IP Address to IP Number
Create FUNCTION [dbo].[Dot2LongIP]( @IP VarChar(15) )
RETURNS BigInt
AS
BEGIN
DECLARE @ipA BigInt,
@ipB Int,
@ipC Int,
@ipD Int,
@ipI BigInt
SELECT @ipA = LEFT(@ip, PATINDEX('%.%', @ip) - 1 )
SELECT @ip = RIGHT(@ip, LEN(@ip) - LEN(@ipA) - 1 )
SELECT @ipB = LEFT(@ip, PATINDEX('%.%', @ip) - 1 )
SELECT @ip = RIGHT(@ip, LEN(@ip) - LEN(@ipB) - 1 )
SELECT @ipC = LEFT(@ip, PATINDEX('%.%', @ip) - 1 )
SELECT @ip = RIGHT(@ip, LEN(@ip) - LEN(@ipC) - 1 )
SELECT @ipD = @ip
RETURN ( @ipA * 256*256*256 ) + ( @ipB * 256*256 ) + ( @ipC * 256 ) + @ipD
END
RETURN @ipI
END
Example PostgreSQL Function To Convert IP Address to IP Number
CREATE OR REPLACE FUNCTION Dot2LongIP(text) RETURNS bigint AS ' SELECT split_part($1,''.'',1)::int8*(256*256*256)+ split_part($1,''.'',2)::int8*(256*256)+ split_part($1,''.'',3)::int8*256+ split_part($1,''.'',4)::int8; ' LANGUAGE 'SQL';
Example Microsoft(r) Excel Function To Convert IP Address to IP Number
Convert IPv4 IP Address to IP Number in Decimal Integer (IPv4 IP Address is in cell A1):
=((VALUE(LEFT(A1, FIND(".", A1)-1)))*256^3)+((VALUE(MID(A1, FIND(".", A1)+1, FIND(".", A1, FIND(".", A1)+1)-FIND(".", A1)-1)))*256^2)+((VALUE(MID(A1, FIND(".", A1, FIND(".", A1)+1)+1, FIND(".", A1, FIND(".", A1, FIND(".", A1)+1)+1)-FIND(".", A1, FIND(".", A1)+1)-1)))*256)+(VALUE(RIGHT(A1, LEN(A1)-FIND(".", A1, FIND(".", A1, FIND(".", A1)+1)+1))))
Convert IP Number in Decimal Integer to IPv4 IP Address (Decimal Integer is in cell A2):
=IF(A2<>"", CONCATENATE(INT(A2/256^3), ".", INT(MOD(A2, (256^3))/(256^2)), ".", INT(MOD(MOD(A2, 256^3), 256^2)/256), ".", MOD(MOD(MOD(A2, 256^3), 256^2), 256)), "")
Firstly, convert the IP address to IP number format. Search IP-Country-Region-City-ISP-Domain Database using IP number to match a record that has the IP Number between the Beginning IP Number and the Ending IP Number.
For example, IP Address "72.77.138.60" is "1213041212" in IP Number. It matched the following recordset in the database.
"1213041208","1213041215","US","UNITED STATES","FLORIDA","SARASOTA","VERIZON INTERNET SERVICES INC","VERIZON.NET"
The IP2Location will display the "-" in country field when the IP address range is still unallocated to any countries. It is also known as reserved IP address range.
First, import this database into your MSSQL, MS-ACCESS, PL/SQL, MYSQL or other RDMS. Use an SQL query to get the matching recordset.
Example of SQL Query (MSSQL)SELECT [IP_FROM COLUMN], [IP_TO COLUMN], [COUNTRY_CODE COLUMN], [COUNTRY_NAME COLUMN], [REGION_NAME COLUMN], [CITY_NAME COLUMN], [ISP COLUMN], [DOMAIN COLUMN] FROM [IP-COUNTRY-REGION-CITY-ISP-DOMAIN TABLE] WHERE [SEARCH IP NO] BETWEEN [IP FROM COLUMN] AND [IP TO COLUMN]
Example of SQL Query (MYSQL)
SELECT [IP_FROM COLUMN], [IP_TO COLUMN], [COUNTRY_CODE COLUMN], [COUNTRY_NAME COLUMN], [REGION_NAME COLUMN], [CITY_NAME COLUMN], [ISP COLUMN], [DOMAIN COLUMN] FROM [IP-COUNTRY-REGION-CITY-ISP-DOMAIN TABLE] WHERE ([IP FROM COLUMN] <= [SEARCH IP NO]) AND ([IP TO COLUMN] >= [SEARCH IP NO])
Our subscribers can automate the download process using the free command-line script written in Perl which can be downloaded from our Web site. Please visit the following Web page for more information such as command syntax.
http://www.ip2location.com/free/downloader
Since the database is being updated every month at the beginning of the month, please download the database only once a month.
ASP without Proxy detection
<%
ipaddress = Request.ServerVariables("REMOTE_ADDR")
%>
ASP with Proxy detection
<%
ipaddress = Request.ServerVariables("HTTP_X_FORWARDED_FOR")
if ipaddress = "" then
ipaddress = Request.ServerVariables("REMOTE_ADDR")
end if
%>
PHP without Proxy detection
<?
$ipaddress = getenv(REMOTE_ADDR);
?>
PHP with Proxy detection
<?
if (getenv(HTTP_X_FORWARDED_FOR)) {
$ipaddress = getenv(HTTP_X_FORWARDED_FOR);
} else {
$ipaddress = getenv(REMOTE_ADDR);
}
?>JSP without Proxy detection
<%
String ipaddress = request.getRemoteAddr();
%>
JSP with Proxy detection
<%
if (request.getHeader("X_FORWARDED_FOR") == null) {
String ipaddress = request.getRemoteAddr();
} else {
String ipaddress = request.getHeader("X_FORWARDED_FOR");
}
%>
ColdFusion without Proxy detection
<CFCOMPONENT> <CFSET ipaddress="#CGI.Remote_Addr#"> </CFCOMPONENT>
ColdFusion with Proxy detection
<CFCOMPONENT> <CFIF #CGI.HTTP_X_Forwarded_For# EQ ""> <CFSET ipaddress="#CGI.Remote_Addr#"> <CFELSE> <CFSET ipaddress="#CGI.HTTP_X_Forwarded_For#"> </CFIF> </CFCOMPONENT>
ASP.NET (C#) without Proxy detection
public string IpAddress()
{
return Request.ServerVariables["HTTP_X_FORWARDED_FOR"];
}
ASP.NET (C#) with Proxy detection
public string IpAddress()
{
string strIp;
strIp = Request.ServerVariables["HTTP_X_FORWARDED_FOR"];
if (strIp == null)
{
strIp = Request.ServerVariables["REMOTE_ADDR"];
}
return strIp;
}
ASP.NET (VB.NET) without Proxy detection
Public Function IpAddress()
IpAddress = Request.ServerVariables("HTTP_X_FORWARDED_FOR")
End Function
ASP.NET (VB.NET) with Proxy detection
Public Function IpAddress()
Dim strIp As String
strIp = Request.ServerVariables("HTTP_X_FORWARDED_FOR")
If strIp = "" Then
strIp = Request.ServerVariables("REMOTE_ADDR")
End If
IpAddress = strIp
End Function
MS-SQL
CREATE DATABASE ip2location GO USE ip2location GO CREATE TABLE [ip2location].[dbo].[ip2location_db7]( [ip_from] float NOT NULL, [ip_to] float NOT NULL, [country_code] nvarchar(2) NOT NULL, [country_name] nvarchar(64) NOT NULL, [region_name] nvarchar(128) NOT NULL, [city_name] nvarchar(128) NOT NULL, [isp] nvarchar(255) NOT NULL, [domain] nvarchar(128) NOT NULL, ) ON [PRIMARY] GO CREATE INDEX [ip_from] ON [ip2location].[dbo].[ip2location_db7]([ip_from]) ON [PRIMARY] GO CREATE INDEX [ip_to] ON [ip2location].[dbo].[ip2location_db7]([ip_to]) ON [PRIMARY] GO
MySQL
CREATE DATABASE ip2location; USE ip2location; CREATE TABLE `ip2location_db7`( `ip_from` INT(10) UNSIGNED, `ip_to` INT(10) UNSIGNED, `country_code` CHAR(2), `country_name` VARCHAR(64), `region_name` VARCHAR(128), `city_name` VARCHAR(128), `isp` VARCHAR(255), `domain` VARCHAR(128), INDEX `idx_ip_from` (`ip_from`), INDEX `idx_ip_to` (`ip_to`), INDEX `idx_ip_from_to` (`ip_from`, `ip_to`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
PostgreSQL
CREATE DATABASE ip2location WITH ENCODING 'UTF8'; CREATE TABLE ip2location_db7( ip_from integer(10) NOT NULL, ip_to integer(10) NOT NULL, country_code character(2) NOT NULL, country_name character varying(64) NOT NULL, region_name character varying(128) NOT NULL, city_name character varying(128) NOT NULL, isp character varying(255) NOT NULL, domain character varying(128) NOT NULL, ip2location_db7_pkey PRIMARY KEY (ip_from, ip_to) );
MS-SQL
BULK INSERT [ip2location].[dbo].[ip2location_db7]
FROM 'C:\[path to your CSV file]\IP-COUNTRY-REGION-CITY-ISP-DOMAIN.CSV'
WITH
(
FORMATFILE = 'C:\[path to your DB7.FMT file]\DB7.FMT'
)
GODB7.FMT
NOTE: You will need to copy the FMT code below and save it as a file named DB7.FMT on your computer.
10.0 9 1 SQLCHAR 0 1 "\"" 0 first_double_quote Latin1_General_CI_AI 2 SQLCHAR 0 20 "\",\"" 1 ip_from "" 3 SQLCHAR 0 20 "\",\"" 2 ip_to "" 4 SQLCHAR 0 2 "\",\"" 3 country_code Latin1_General_CI_AI 5 SQLCHAR 0 64 "\",\"" 4 country_name Latin1_General_CI_AI 6 SQLCHAR 0 128 "\",\"" 5 region_name Latin1_General_CI_AI 7 SQLCHAR 0 128 "\",\"" 6 city_name Latin1_General_CI_AI 8 SQLCHAR 0 255 "\",\"" 7 isp Latin1_General_CI_AI 9 SQLCHAR 0 128 "\"\r\n" 8 domain Latin1_General_CI_AI
MySQL
LOAD DATA LOCAL INFILE 'IP-COUNTRY-REGION-CITY-ISP-DOMAIN.CSV' INTO TABLE `ip2location_db7` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
PostgreSQL
COPY ip2location_db7 FROM 'IP-COUNTRY-REGION-CITY-ISP-DOMAIN.CSV' WITH CSV QUOTE AS '"';
Yes, we will deliver the notification via email when the update is available in the download area. We usually release the update on the first day of the calendar month.
There are only 100 records in the demo version for evaluation purpose. The full version of the database has more than 6,200,000 records.
You can subscribe to any free 3rd party web-hosting that supports server-side scripting. One example is Brinkster.com. If you do not want to install this demo, you can visit our pre-installed demo at http://www.ip2location.com/demo
Ownership of IP addresses change hands from time to time. Therefore, a small percentage of IP address blocks needs to be updated every year. Our database is updated monthly to make sure it is always accurate.
You need to download the latest monthly database from our server using the subscription account and password. The database is available in complete format. Therefore, you just need to drop the old database and replace it with the new one.
Youn can purchase it from here. We will generate an unique login/password to allow you to download the database for one year after we have processed your order.
You will receive your login and password through email immediately after payment is authorized. You can use your credentials to download the database from our website at anytime. The database is in a ZIP compressed format to save your bandwidth and downloading time.
You can resell our databases provided you purchase a separate license for each client. For example, if you are a developer and purchase a license for your client, you can make whatever changes you need and deliver it to your client - provided you transfer the license to your client (as easy as notification through email). In other words, one copy cannot be sold to multiple parties. You can resell the database for whatever price you wish.
If you are an existing subscriber of IP2Location database, you can upgrade to a higher package. You just need to pay for the difference in price instead of the full amount again. The subscription period will remain unchanged based on the old subscription package. Please login and click on the upgrade button inside the customer account area.
NOTES: If your current subscription has less than 6 months left, it will be renewed first before the upgrade is performed.
The IP2Location databases supply the nearest Weather Station Codes and Names only. It does NOT provide live weather data or forecast data feed. However, it is possible to collect the weather information by using paid subscription from 3rd-party Web sites such as Weather.com. Please refer to their license agreement and contact them if you have any questions regarding XML weather data feed.
Weather.com XML Data Feed Registration (Free for Personal Use or Paid for Commercial use)
https://registration.weather.com/ursa/profile/new?
For example, if the nearest Weather Station Code for one IP address is AAXX0001 in Aruba. The following are some sample sites with custom URL to retrieve the weather information and forecast.
Sample XML Data Feed using Weather.com
http://xoap.weather.com/weather/local/AAXX0001?cc=*&dayf=1&unit=m
Web-based Weather Information from Other Sites
http://www.weather.com/outlook/travel/businesstraveler/wxdetail/AAXX0001?dayNum=7
http://www.theweathernetwork.com/index.php?product=weather&placecode=aaxx0001
http://www.intellicast.com/Global/Satellite/Infrared.aspx?location=AAXX0001&lien=8
http://weather.aol.com/main.adp?location=AAXX0001
http://weather.msn.com/tenday.aspx?wealocations=wc:AAXX0001
http://weather.yahoo.com/forecast/AAXX0001.html
The IP2Location database will be released on the 1st day of the calendar month and uploaded to the customer download area for immediate download.
| 2013 | 2014 | 2015 | 2016 |
| 1st January, 2013 | 1st January, 2014 | 1st January, 2015 | 1st January, 2016 |
| 1st February, 2013 | 1st February, 2014 | 1st February, 2015 | 1st February, 2016 |
| 1st March, 2013 | 1st March, 2014 | 1st March, 2015 | 1st March, 2016 |
| 1st April, 2013 | 1st April, 2014 | 1st April, 2015 | 1st April, 2016 |
| 1st May, 2013 | 1st May, 2014 | 1st May, 2015 | 1st May, 2016 |
| 1st June, 2013 | 1st June, 2014 | 1st June, 2015 | 1st June, 2016 |
| 1st July, 2013 | 1st July, 2014 | 1st July, 2015 | 1st July, 2016 |
| 1st August, 2013 | 1st August, 2014 | 1st August, 2015 | 1st August, 2016 |
| 1st September, 2013 | 1st September, 2014 | 1st September, 2015 | 1st September, 2016 |
| 1st October, 2013 | 1st October, 2014 | 1st October, 2015 | 1st October, 2016 |
| 1st November, 2013 | 1st November, 2014 | 1st November, 2015 | 1st November, 2016 |
| 1st December, 2013 | 1st December, 2014 | 1st December, 2015 | 1st December, 2016 |
The IP2Location is supporting 249 countries as recognized in ISO 3166. The IP2Location database has over 99.5% accuracy in country level detection and >75% of accuracy in city level. The inaccuracy is due to the dynamic IP address allocation by large ISPs such as AOL, MSN TV and other proxies. Due to the fact that AOL uses a network that routes all of the company's Internet traffic through Reston, Virginia, all IP-based geo-location services, including IP2Location, are unable to determine the state and city for people who dial into the AOL network. You can get the complete accuracy and coverage from data accuracy report page.
We are monitoring all new IP address range assigned. We will scan through all new IP address range once reported for its network name and location. This is a manual process and required actual usage from any IP address in order for us to determine the related ISP name and location. Therefore, there might be delay in the database in reporting any new assigned IP address range. We strive to provide the information as accurate as possible.
LocaProxy.com. It provides multi-location HTTP proxies to help businesses test geolocation applications. This solution reduces the total cost of testing by supplying the Distributed Infrastructure as a Service.