Difference between revisions of "HostIP for geolocation"
(New page: category:Engineering The hostip.info geolocation database appears to be gzipped multiple times. I don't know why, but if you have trouble opening it trying using the `file` command to...) |
|||
| Line 1: | Line 1: | ||
[[category:Engineering]] | [[category:Engineering]] | ||
| − | The hostip.info geolocation database appears to be gzipped multiple times. | + | The [http://www.hostip.info/ hostip.info] geolocation database is free |
| − | I don't know why, 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 | + | and fairly accurate. |
| + | |||
| + | When I downloaded the bz2 version. It appears to be gzipped multiple times. | ||
| + | I don't know why, 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 | $ bunzip2 hostip_current.sql.bz2 | ||
| Line 10: | Line 13: | ||
$ gunzip hostip_current.sql.gz | $ 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 thing here is what I to did. | ||
Create a database for hostip and load the data: | 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 -e "create database hostip;" | ||
$ mysql -u root -p ROOT_PASSWORD hostip < hostip_current.sql | $ 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, lat, lon). | ||
| + | |||
| + | <code> | ||
| + | #!/usr/bin/env python | ||
| + | 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() | ||
| + | cursor.execute(sql) | ||
| + | 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]) | ||
| + | </code> | ||
Revision as of 19:26, 26 July 2007
The hostip.info geolocation database is free
and fairly accurate.
When I downloaded the bz2 version. It appears to be gzipped multiple times. I don't know why, 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 thing 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, lat, lon).
- !/usr/bin/env python
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()
cursor.execute(sql)
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])
