How to build IP GeoLocation Service with IP2Location database and SQLite

Junyi Zou - Mar 20, 2019

IP Geolocator can locate client's Geolocation using only it's IP address, it can provide information such as country, region, city, latitude and longitude. There are many online IP Geolocation API in the market, such as ip-api.com, ipstack.com. but they have many issues:

ip-api.com is free, but it will automatically ban any IP address doing over 150 requests per minute; not allow for non-commercial use, and it doesn't support HTTPS !!!

ipstack.com is another alternative, but it needs you to apply for API code, and it is expensive ($9.99 for 50,000 requests/month).

The best IP Geolocation Service I recommend is coding.tools IP Geo API, It's free to use, Unlimited, user friendly and fast.

Above all, build your own IP Geolocation Services is the most reliable choice (you need to depend on yourself, right?).

Lite IP2Location database is a large CSV file that has attributes include country, region or state, city, latitude, longitude, ZIP code and time zone. You can do search query in the database to find the match record for your IP Address.

Lite IP2Location database (51.23 MB) can be download from: Lite IP2Location.

It is accurate to class Type C IP Address (first 24 bits of IP Address), which is enough for small commercial use.

The CSV file looks like this:

Step 1: Create an SQLite Table.

CREATE TABLE IpGeo IF NOT EXISTS
(
   ip_from      INT NOT NULL,
   ip_to        INT NOT NULL,
   country_code CHAR(50) NOT NULL,
   country_name CHAR(50) NOT NULL,
   region_name  CHAR(50) NOT NULL,
   city_name    CHAR(50) NOT NULL,
   latitude     CHAR(50) NOT NULL,
   longitude    CHAR(50) NOT NULL,
   zip_code     CHAR(50) NOT NULL,
   time_zone    CHAR(50) NOT NULL
);

To achieve better performance, create INDEX on column ip_from and ip_to

CREATE INDEX ip_index on IpGeo (ip_from, ip_to);

Step 2: Insert all CSV records into SQLite database (python).

import sqlite3

conn = sqlite3.connect('IP2Location.db')

for line in open("IP2LOCATION-LITE-DB11.CSV", 'r').readlines():
    line = line.replace('\r', '').replace('\n', '').lstrip('"').rstrip('"')
    print(line)
    # split by comma for each line in CSV
    [ip_from, ip_to, country_code, country_name, region_name, city_name, latitude, longitude, zip_code, time_zone] = line.split('","')
    # insert sql
    sql = 'INSERT INTO IpGeo VALUES ("{0}","{1}","{2}","{3}","{4}","{5}","{6}","{7}","{8}","{9}");'.format(
        ip_from, ip_to, country_code, country_name, region_name, city_name, latitude, longitude, zip_code, time_zone)
    conn.execute(sql)

conn.commit()
conn.close()

Step 3: Design the search SQL.

To find matched record, you need to understand the meaning of ip_from and ip_to columns.

IP Number = 16777216*a + 65536*b + 256*c + d

(where IP Address = a.b.c.d)

import sqlite3

ip_str = '206.174.179.210'
ip_str = ip_str.replace(" ", "")
[a, b, c, d] = ip_str.split('.')
a = int(a)
b = int(b)
c = int(c)
d = int(d)
# check if IP Address is valid
assert 0 <= a <= 255
assert 0 <= b <= 255
assert 0 <= c <= 255
assert 0 <= d <= 255
ip_number = 16777216 * a + 65536 * b + 256 * c + d

conn = sqlite3.connect('/app/IP2Location/IP2Location.db')
c = conn.cursor()
sql = 'SELECT * FROM IpGeo where ip_to >= {0} and ip_from <= {0}'.format(ip_number)

c.execute(sql)

[ip_from, ip_to, country_code, country_name, region_name, city_name, latitude, longitude, zip_code, time_zone] = c.fetchone()
print([ip_from, ip_to, country_code, country_name, region_name, city_name, latitude, longitude, zip_code, time_zone])

conn.close()

Step 4: Check the result.

In this specific example, IP Address '206.174.179.210' is converted to 3467555794.

The search SQL is:

SELECT * FROM IpGeo where ip_to >= 3467555794 and ip_from <= 3467555794

The matched record is:

[3467555584, 3467556095, 'CA', 'Canada', 'Ontario', 'Kitchener', '43.427848', '-80.513516', 'N2E 3K2', '-04:00']

So GeoLocation for IP Address '206.174.179.210' is:

IP address: 206.174.179.210
Country: Canada
Region: Ontario
City: Kitchener
Latitude: 43.427848
Longitude: -80.513516
Zip Code: N2E 3K2
Timezone: -04:00