HostIP for geolocation

The hostip.info geolocation database is free and accurate. You can use this to turn an IP address into a geographic coordinates. It also includes city, state, and country information.

When I downloaded the bz2 version it appeared to be gzipped multiple times. I don't know why that is, but if you have trouble opening it trying using the `file` command to see what format it is in. Keep gunzipping it until your get ascii text. Here is what I had to do:

 $ bunzip2 hostip_current.sql.bz2
 $ mv hostip_current.sql hostip_current.sql.gz
 $ gunzip hostip_current.sql.gz
 $ mv hostip_current.sql hostip_current.sql.gz # yes, do it again
 $ gunzip hostip_current.sql.gz

I knew I was done when `file` gave me this results:

 $ file hostip_current.sql 
 hostip_current.sql: ASCII text, with very long line

Now, to actually use this data here is what I to did. Create a database for hostip and load the data:

 $ mysql -u root -p ROOT_PASSWORD -e "create database hostip;"
 $ mysql -u root -p ROOT_PASSWORD hostip < hostip_current.sql

That gave me a nice database. Each A block of an IP address is stored in a different table. It was pretty easy to write a Python script that would decode an IP address into {city, state, country, country_code lat, lon}.

#!/usr/bin/env python
# hostipgeo.py
# HostIP.info geolocation lookup.
# Run this script with an IP address as an argument and it will return
# the following information: {city, state, country, country_code lat, lon}.
# Noah Spurrier 2007
import MySQLdb
import os, sys, urllib
ip = sys.argv[1].split('.')
con = MySQLdb.Connect(host="127.0.0.1", port=3306, user="root", passwd="", db="hostip") #, unix_socket='/var/lib/mysql/mysql.sock') 
cursor = con.cursor()
table_a = "ip4_" + ip[0]
net_b = ip[1]
net_c = ip[2]
sql = """SELECT cityByCountry.name as city, cityByCountry.state as state,
    countries.name as country, countries.code as country_code,
    cityByCountry.lat as lat, cityByCountry.lng as lon
FROM %(table_a)s, countries, cityByCountry
WHERE %(table_a)s.city=cityByCountry.city AND
    %(table_a)s.country=cityByCountry.country AND
    %(table_a)s.country=countries.id AND
    b=%(net_b)s AND c=%(net_c)s;"""
sql = sql % locals()
if cursor.execute(sql) == 0:
    print "That IP address was not found in the database."
    sys.exit(1)
con.close()
results = list(cursor.fetchall()[0])
results[0] = urllib.unquote(results[0])
keys = ['city','state','county','country_code','lat','lon']
results_dict = dict(zip(keys, results))
for key in keys:
    print "%12s: %s" %(key, results_dict[key])
sys.exit(0)

Running this script on the IP address 69.80.208.1 gives the following:

$ ./hostipgeo.py 69.80.208.1
        city: San Francisco, CA
       state: California
      county: UNITED STATES
country_code: US
         lat: 37.8133
         lon: -122.50