Super fast IP to lat/lng in Rails

Posted by aaron
on Monday, October 22
In building the eye candy demo for the Graphing Social conference, I needed a quick way to geo-locate users by IP address. For Rails, GeoKit is an awesome plugin. It supports a list of providers, and overlays distance calculations, before_filter helpers, all sorts of good stuff.
It uses hostip.info to do IP to lat/lng, using their RESTful interface: 
http://api.hostip.info/get_html.php?ip=12.215.42.19&position=true
  Country: UNITED STATES (US)
  City: Sugar Grove, IL
  Latitude: 41.7696
  Longitude: -88.4588

This is great, but its just too darn slow to geolocate 12 users a second. All I needed was a FAST ip to lat/long lookup mechanism. Luckily, HostInfo provides the raw data. Here's how I built super fast GeoLoc by IP method.

Download, create a DB, and import the data. 
wget http://hostip.ww.com/hostip_current.sql.gz
gunzip hostip_current.sql.gz 
mysqladmin -uroot create hostip
mysql -uroot hostip < hostip_current.sql


Create a simple HostIp class. Note: I need to dynamically build this query b/c the data is sharded across tables by the A class of the IP. 
class Hostip < ActiveRecord::Base
  def self.geocode(ip)
    a,b,c,d = ip.split(".")
    self.set_table_name "hostip.ip4_#{a}"
    ip = find(:first, :select => "lat,lng",
                :joins => %Q{INNER JOIN hostip.cityByCountry 
                               ON hostip.ip4_#{a}.city = hostip.cityByCountry.city 
                               AND hostip.ip4_#{a}.country = hostip.cityByCountry.country},
                :conditions => ["b = ? and c = ?", b,c])
    if ip
      [ip.lat, ip.lng]            
    else 
      ["",""]
    end
  end
end


Usage 
>> Hostip.geocode("4.2.2.2")
=> ["39.944", "-105.062"]


Thats it! Now you can geolocate by IP in your own datacenter.
Thanks again to the guys at HostIp for sharing this data!