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:

Lite IP2Location database csv

How to convert IP2Location CSV file to SQLite database?

Step 1: Create an SQLite Table.

  1. CREATE TABLE IpGeo IF NOT EXISTS
  2. (
  3.    ip_from      INT NOT NULL,
  4.    ip_to        INT NOT NULL,
  5.    country_code CHAR(50) NOT NULL,
  6.    country_name CHAR(50) NOT NULL,
  7.    region_name  CHAR(50) NOT NULL,
  8.    city_name    CHAR(50) NOT NULL,
  9.    latitude     CHAR(50) NOT NULL,
  10.    longitude    CHAR(50) NOT NULL,
  11.    zip_code     CHAR(50) NOT NULL,
  12.    time_zone    CHAR(50) NOT NULL
  13. );

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

  1. CREATE INDEX ip_index on IpGeo (ip_from, ip_to);

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

  1. import sqlite3
  2.  
  3. conn = sqlite3.connect('IP2Location.db')
  4.  
  5. for line in open("IP2LOCATION-LITE-DB11.CSV", 'r').readlines():
  6.     line = line.replace('\r', '').replace('\n', '').lstrip('"').rstrip('"')
  7.     print(line)
  8.     # split by comma for each line in CSV
  9.     [ip_from, ip_to, country_code, country_name, region_name, city_name, latitude, longitude, zip_code, time_zone] = line.split('","')
  10.     # insert sql
  11.     sql = 'INSERT INTO IpGeo VALUES ("{0}","{1}","{2}","{3}","{4}","{5}","{6}","{7}","{8}","{9}");'.format(
  12.         ip_from, ip_to, country_code, country_name, region_name, city_name, latitude, longitude, zip_code, time_zone)
  13.     conn.execute(sql)
  14.  
  15. conn.commit()
  16. 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)

  1. import sqlite3
  2.  
  3. ip_str = '206.174.179.210'
  4. ip_str = ip_str.replace(" ", "")
  5. [a, b, c, d] = ip_str.split('.')
  6. a = int(a)
  7. b = int(b)
  8. c = int(c)
  9. d = int(d)
  10. # check if IP Address is valid
  11. assert 0 <= a <= 255
  12. assert 0 <= b <= 255
  13. assert 0 <= c <= 255
  14. assert 0 <= d <= 255
  15. ip_number = 16777216 * a + 65536 * b + 256 * c + d
  16.  
  17. conn = sqlite3.connect('/app/IP2Location/IP2Location.db')
  18. c = conn.cursor()
  19. sql = 'SELECT * FROM IpGeo where ip_to >= {0} and ip_from <= {0}'.format(ip_number)
  20.  
  21. c.execute(sql)
  22.  
  23. [ip_from, ip_to, country_code, country_name, region_name, city_name, latitude, longitude, zip_code, time_zone] = c.fetchone()
  24. print([ip_from, ip_to, country_code, country_name, region_name, city_name, latitude, longitude, zip_code, time_zone])
  25.  
  26. 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:

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

The matched record is:

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

So GeoLocation for IP Address '206.174.179.210' is:

  1. IP address: 206.174.179.210
  2. Country: Canada
  3. Region: Ontario
  4. City: Kitchener
  5. Latitude: 43.427848
  6. Longitude: -80.513516
  7. Zip Code: N2E 3K2
  8. Timezone: -04:00

More Blog: