ToC
グローバルIPアドレスの管理
グローバルIPアドレスは、Internet Assigned Numbers Authorityによって管理され、 その下部組織によって国ごとに割り当てが決められています。 この割り当て表を使って、Webサイトへアクセスしてきたユーザーの国を割り出してみるという データに関連するTipsを紹介します。
MaxMind GeoLite Databases
今回は、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アドレスの第１オクテット×256×256×256
＋IPアドレスの第２オクテット×256×256
＋IPアドレスの第３オクテット×256
＋IPアドレスの第４オクテット
MySQLで検索
今回は、このデータをMySQLに登録して検索してみることにします。 事前にテーブルを作成した上で、CSVの登録を行ってみます。 MySQLに登録する際には、クエリで登録ができるので超簡単です。82690行が１秒たらずで登録されました。
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()という擬似コードと アドレス間の相互変換関数が標準関数として組み込まれている部分です。 このデータと関数を使えば、簡単にいろいろなことが実現できそうです。
[amazon_link asins=‘4797393114,4873116384,4774142948’ template=‘ProductCarousel’ store=‘lunalab-22’ marketplace=‘JP’ link_id=‘cbdf1e9d-9c60-11e7-8aff-b9b28ee4b9ce’]