IPアドレスからアクセス元の国名を割り出す

グローバルIPアドレスは、Internet Assigned Numbers Authorityによって管理され、その下部組織によって国ごとに割り当てが決められています。
この割り当て表を使って、Webサイトへアクセスしてきたユーザーの国を割り出してみるというデータに関連するTipsを紹介します。

今回は、MaxMind社が提供しているGeoLite Free Downloadable DatabasesのCSVファイルを利用してみます。
MaxMind社では毎月データを更新しており、より詳細なレベルのデータについても有償で提供していたりするようです。(利用に際しては、ライセンスを守った上で利用してください)
データをダウンロードしてみるとこのような形のデータが取得できます。

"1.0.0.0","1.0.0.255","16777216","16777471","AU","Australia"
"1.0.1.0","1.0.3.255","16777472","16778239","CN","China"
"1.0.4.0","1.0.7.255","16778240","16779263","AU","Australia"
"1.0.8.0","1.0.15.255","16779264","16781311","CN","China"
(続く)

データカラムは、下記のようになっており開始IPアドレス〜終了IPアドレスの範囲に含まれるIPアドレスが割り当て国となるデータ構造をしています。

Noデータ種別
開始IPアドレス
終了IPアドレス
開始IP擬似コード
終了IP擬似コード
国コード(ISO 3166-1に準拠したコード)
国名

擬似コードは、IPアドレスを数値化した値となっていて計算式は下記のような形になっています。

擬似コード = 
  IPアドレスの第1オクテット×256×256×256
 +IPアドレスの第2オクテット×256×256
 +IPアドレスの第3オクテット×256
 +IPアドレスの第4オクテット

今回は、このデータをMySQLに登録して検索してみることにします。
事前にテーブルを作成した上で、CSVの登録を行ってみます。
MySQLに登録する際には、クエリで登録ができるので超簡単です。82690行が1秒たらずで登録されました。

LOAD DATA LOCAL INFILE "/GeoIPCountryWhois.csv(CSVファイル)" INTO Table GeoIpCountry(テーブル名) FIELDS TERMINATED BY ',' ENCLOSED BY '";';

次に、特定のIPアドレスの存在する国を検索してみます。
例えば、74.125.235.87というアドレスからアクセスがあったとした場合には、下記のようなクエリで簡単に検索することができます。

SELECT * FROM GeoIpCountry WHERE INET_ATON('74.125.235.87') BETWEEN StartPseudoCode(開始IP擬似コード) AND EndPseudoCode(終了IP擬似コード)

MySQLが優れているのは、INET_ATON()、INET_NTOA()という擬似コードとアドレス間の相互変換関数が標準関数として組み込まれている部分です。
このデータと関数を使えば、簡単にいろいろなことが実現できそうです。