2/1/2022

My Ip2location

  1. Ip2location Api Free
  2. Check Ip2location
  3. Ip2location Lite
  4. Ip2location Vs Maxmind

Here is a guide for users to fully automate the updating of their IP2Location DB24 database. We will show how to download the IP2Location DB24 csv data from the web server and then unzip it before loading the data into a MySQL server on a Windows platform. We will explain what each part of the code does as we go through the whole process. The scripts will work for Windows 7 or later versions of Windows.

Download the scripts

Please download the zip file containing the scripts. Extract the 3 files inside to a folder. The ip2location-update.bat is a batch file which you will run later to automate the downloading, unzipping and loading of the DB24 data. The download.ps1 and unzip.ps1 are Powershell scripts which will be used by the batch file. Make sure all 3 files are in the same folder.

Files for ip2location-python, version 0.1.3; Filename, size File type Python version Upload date Hashes; Filename, size ip2location-python-0.1.3.tar.gz (2.5 kB) File type Source Python version None Upload date Jun 19, 2015 Hashes View. This IP2Location App is a handy IP tool that allows you to instantly retrieve the IP location information of an IP address. Below are the IP info provided in this IP lookup app.

Python

Ip2location Api Free

Configure login details

Before you run the batch file, you will need to setup the login details for the IP2Location web server as well as the MySQL server information. Open the ip2location-update.bat file in a text editor like Notepad. Do NOT double click on the batch file as this will run the code. Instead, right click on it and click Edit. Then look for the below and replace with your actual details then save the file:

SET LOGIN=IP2LOCATION_WEBSITE_LOGIN
SET PASS=IP2LOCATION_WEBSITE_PASSWORD
SET DBHOST=DATABASE_HOSTNAME
SET DBUSER=DATABASE_USERNAME
SET DBPASS=DATABASE_PASSWORD
SET DBNAME=DATABASE_NAME

For most cases, you shouldn’t have to modify anything else in the batch file but we will still explain what is going on in case you want to modify the code.

Creating the temporary folder for storing the downloaded zipped data file

If the temporary folder does not exist, it will be created.

Checking for pre-requisites

As mentioned earlier, the batch file will require both download.ps1 and unzip.ps1 to be in the same folder. If any of these 2 files are missing, an error will be shown and the batch file will terminate itself.

Granting permission for the Powershell scripts to run

Before we can run the Powershell scripts, permission must be granted with the following:

Downloading the DB24 data from the IP2Location

Download the DB24 data by connecting to IP2Location website and passing all the login parameters to it. Save the zip file to the specified download folder. An error will be shown if the download is not successful.

Unzipping the downloaded zip file

Decompress the zip file to get the CSV data file. An error will be shown if the decompression is not successful.

Creating a temporary table in MySQL to load the data

Drop the temporary table if it already exists and then creates the table. If an error is encountered, an error message will be shown and the script will terminate itself.

Loading the CSV data into the MySQL temporary table

Load the CSV data into the temporary table. If an error is encountered, an error message will be shown and the script will terminate itself.

Note: Please make sure the local_inline option has been enabled in MySQL.

Dropping the existing data table

Drop the existing data table. If an error is encountered, an error message will be shown and the script will terminate itself.

Rename the temporary table to become the live data table

Rename the temporary table to become the live data table. If an error is encountered, an error message will be shown and the script will terminate itself.

Remove temporary download folder and the downloaded data file

Perform final clean up by removing the download folder and all files in it.

The complete script can be downloaded below

We sincerely thank IP2Location for allowing us to use their database on this site. IP2Location is a company that provides mapping between an IP address and a physical location. They offer several databases proving information like country, city, zip code, latitude and longitude and weather for a given IP address.

For more information visit http://www.ip2location.com.

The database from IP2Location is available in two different formats:

  • Binary File - You will need an API from IP2Location to read this file
  • CSV File - comma separated values
Live Demo

Note: This demo uses the same technique as described in this article to fetch location based on an IP address.

Usage example

The remainder of this article talks about using the database from IP2Location with a relational database. In the following example, we will be using:
  • DB3 - IP-Country-Region-City Database from IP2Location
  • Microsoft SQL Server - to store data
  • WinSQL - a database querying tool for importing data into SQL Server

Step 1 - Downloading the CSV file

Visit IP2Location to download the CSV file. This file is in the following format.

Field Name
Field Description
IP_FROM
First IP address in Netblock.
IP_TO
Last IP address in Netblock.
COUNTRY_CODE
Two-character country code based on ISO 3166.
COUNTRY_NAME
Country name based on ISO 3166.
REGION
Region or state name.
CITY
City name.

The actual data in the file looks like:
'IPFROM','IPTO','COUNTRYSHORT','COUNTRYLONG','REGION','CITY'
'67297904','67297911','US','UNITED STATES','MASSACHUSETTS','BEDFORD'
'67297912','67297919','US','UNITED STATES','TEXAS','FLOWER MOUND'
'67297920','67297927','US','UNITED STATES','TENNESSEE','MEMPHIS'

Step 2 - Importing into the database

The easiest way to import a .CSV (comma separated value) file into any database is to drag a file from any folder to WinSQL's catalog window. Click here if you are not familiar with WinSQL. Although I am using Microsoft SQL Server in this example, WinSQL can be used to import/export data from any database using simple mouse clicks. If you run into problems or need more information on how to import this file into SQL Server, click here to watch a video tutorial.

During the import process WinSQL will prompt you to change field names and datatypes for the target table. It is important you use bigint instead of integer when creating a new table. This is because a normal integer is not large enough to hold an IP address.

Once the data has been imported, you will have a table with the following script in the database.
createtableip2Location(
IPFrombigint,
IPTobigint,
CCodechar(2),
CNamevarchar(68),
Regionvarchar(128),
Cityvarchar(128)
)

Step 3 - Quering the database

The newly imported table contains IP addresses in a numerical format, which is different from the more commonly used string format. The best way to convert the string representation of an IP address to a numeric value is to write a function in SQL Server. The following script shows a function that accomplishes this task.
-- Converts a string based IP to Integer
-- For example: 192.168.1.200 --> 3232235976
createfunctionIP2INT(@ipAddressvarchar(20))
RETURNSbigint
AS
BEGIN
DECLARE @FirstDotint, @SecondDotint, @ThirdDotint,
@FirstOctetvarchar(3), @SecondOctetvarchar(3),
@ThirdOctetvarchar(3), @FourthOctetvarchar(3),
@Resultbigint
SET @FirstDot = CHARINDEX('.', @ipAddress)
SET @SecondDot = CHARINDEX('.', @ipAddress, @FirstDot + 1)
SET @ThirdDot = CHARINDEX('.', @ipAddress, @SecondDot + 1)
SET @FirstOctet = SUBSTRING(@ipAddress, 1, @FirstDot - 1)
SET @SecondOctet = SUBSTRING(@ipAddress, @FirstDot + 1, @SecondDot - @FirstDot - 1)
SET @ThirdOctet = SUBSTRING(@ipAddress, @SecondDot + 1, @ThirdDot - @SecondDot - 1)
SET @FourthOctet = SUBSTRING(@ipAddress, @ThirdDot + 1, 3)
SET @Result = 16777216 * CAST(@FirstOctetasbigint) +
65536 * CAST(@SecondOctetasbigint) +
256 * CAST(@ThirdOctetasbigint) +
CAST(@FourthOctetasbigint)
RETURN(@Result);
END
Fetching data from the database
The easiest way to fetch the location of an IP address is the write the following query:
-- Simple but not very efficient query. May take a long
-- time to run.
select *
fromip2Location
wheredbo.IP2INT('209.178.205.30') betweenIPFromandIPTo

There is a significant problem with this query: it runs very slow. The machine we ran this query on took about almost 1 minute to run. This is because the table contains almost 3 million entries and the SELECT query above does not use any index. In fact, it cannot use any index.


Optimizing the SQL to run faster
The first step in optimization is to create a couple of indexes. The following script will create two indexes on the table, one on each field.
-- Create index on IPFrom
CREATEINDEXstart_idx
ONdbo.ip2Location(IPFrom)
go
-- Create index on IPTo
CREATEINDEXend_idx
ONdbo.ip2Location(IPTo)

Next, we have to re-write the SELECT statement so it uses the newly created index. One important information to keep in mind is that field names should always appear on the LHS (left hand side) of the equal sign. When field names appear on the RHS, most RDBMS won't be able to use an index.

-- The two embedded SELECT statements
-- will use the newly created indexes to
-- pull one IPFrom and one IPTo value
select *
fromdbo.ip2Location
whereIPFrom = (
selectmax(IPFrom)
fromdbo.ip2Location
whereIPFrom <= dbo.ip2int(@ipAddress)
-- Using IPFrom on the LHS will cause
-- SQL Server to use an index
)
andIPTo = (
selectmin(IPTo)
fromdbo.ip2Location
whereIPTo >= dbo.ip2int(@ipAddress)
)

Check Ip2location

The above query will now run significantly faster. In our testing, the response time reduced from 1 minute to about 68 milliseconds.

Ip2location Lite

Making it even better
Optionally, you can create either a stored procedure or a function to make life even easier. The following script show how to write them.
-- A stored procedure that returns every field
-- for an IP address
createprocedureGetCity(@ipAddressvarchar(16))
as
begin
select *
fromdbo.ip2Location
whereIPFrom = (
selectmax(IPFrom)
fromdbo.ip2Location
whereIPFrom <= dbo.ip2int(@ipAddress)
)
andIPTo = (
selectmin(IPTo)
fromdbo.ip2Location
whereIPTo >= dbo.ip2int(@ipAddress)
)
end
go
-- A function returning one string containing
-- Country, region and city for an ip address
createfunctionGetCityFunction(@ipAddressvarchar(16))
RETURNSvarchar(325)
as
begin
DECLARE @FinalAnswervarchar(330)
select @FinalAnswer = CName + ', ' + Region + ', ' + City
fromdbo.ip2Location
whereIPFrom = (
selectmax(IPFrom)
fromdbo.ip2Location
whereIPFrom <= dbo.ip2int(@ipAddress)
)
andIPTo = (
selectmin(IPTo)
fromdbo.ip2Location
whereIPTo >= dbo.ip2int(@ipAddress)
)
RETURN (@FinalAnswer)
end

Once a stored procedure is created, you can simply type the following to get the location of an IP address.

Ip2location Vs Maxmind


-- Fetch IP address using a stored procedure
execGetCity'218.111.3.212'
-- If you create a function, you can write a query similar to:
selecturlString, referer, GetCityFunction(IPAddress)
fromWebLog