How to build IP GeoLocation Service with IP2Location database and SQLite
-
Overview of online IP Geolocation Services:
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 free database:
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:
How to convert IP2Location CSV file to SQLite database?
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