IP주소로 국가를 알아보자. (GeoIP library 이용)

invalid-file

관련출처링크
http://vincent.delau.net/php/geoip.html [php 로 예를든 IP주소로 국가알아보는법 블로그 글]
http://www.maxmind.com/app/geoip_country  [csv파일 다운로드 받는 사이트]


먼저 위 두번째 사이트접속 후 Binary Format과 CSV Format 중 csv형식의 파일을 다운로드 받는다.

(이글의 첨부파일로 있으니 여기서 다운받아서 써도 되지만 간간히 업뎃이 이루어지고 있는것 같으니
 
 이글을쓴 시간보다 오랜시간이 지난후라면 다시 최신버젼으로 다운받는걸 추천한다.)


여기선 My-SQL DBMS를 사용한다는 전제하에 설명하도록 한다.(내가 쓰고있는거라서 어쩔수 없다.ㅋ)

먼저 현재 사용중인 database에 총 3개의 테이블을 생성한다.

아래 create table query 문을 보고 형식에 맞추어서 테이블 생성.(My-sql은 Copy & paste를 이용하자)

CREATE TABLE csv (
        start_ip CHAR(15) NOT NULL,
        end_ip CHAR(15) NOT NULL,
        start INT UNSIGNED NOT NULL,
        end INT UNSIGNED NOT NULL,
        cc CHAR(2) NOT NULL,
        cn VARCHAR(50) NOT NULL
        );

 CREATE TABLE cc (
        ci TINYINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
        cc CHAR(2) NOT NULL,
        cn VARCHAR(50) NOT NULL
        );

CREATE TABLE ip (
        start INT UNSIGNED NOT NULL,
        end INT UNSIGNED NOT NULL,
        ci TINYINT UNSIGNED NOT NULL
        );

세개의 테이블을 다 생성했다면 이제 csv파일을 csv테이블에 import해야한다.

import 하는 방법은 나같은 경우는 SQLyog이라는 tool을 사용하는데

SQLyog는 MySQL을 쉽게 다를 수 있게 해주는 My-sql GUI Tool이고 엔터프라이즈 에디션 말고는 무료이다.

http://webyog.com/en/ 이곳에서 커뮤니티버젼을 무료로 다운로드 받을 수 있다.

이러한 툴을 쓰면 import data from csv 라는 메뉴가 있어 쉽게 csv파일을 import 할 수 있다.

터미널이나 콘솔창에서 작업하는게 멋지긴 하지만 -_-ㅋ

유용하고 편리한 툴이 있을땐 툴을 쓰는게 정신건강에 좋다고 보니 왠만하면 쓰도록 하자.

각설하고 만약 이러한 툴을 사용하지 않는다면 직접명령어로 import 한다.

명령어는 위 cvs파일의 경우엔

mysqlimport --fields-terminated-by="," --fields-optionally-enclosed-by="\"" --lines-terminated-by="\n" -u root -p work C:\csv.csv

나 같은 경우는 이와같다.
위에서 work는 database name 이고 csv.csv는 파일이름이다 . 처음 다운로드 받을땐 저 이름이

아니지만 바꿔주도록 하자. 어차피 csv테이블은 나중에 삭제해도 된다. (파일의 경로는 C드라이브다)

그리고 첫번째 필드옵션은 필드 구분이 쉼표로 되어있다는 것이고.
두번째필드옵션은 \" <- 큰따옴표를 나타낸다. 무슨말인고 하니. csv파일을 열어보면 각 필드값들은

큰따옴표로 묶여있다는것이다.

마지막옵션은 말안해도 아리라 본다. (모르면 ...이글을 읽지도 않을듯하다;;)

위 옵션들을 써주어야 다운받은 csv파일이 에러나지 않고 곱게곱게 말잘듣는 새색시마냥

정해준 테이블로 잘 들어간다. (-0-표현이...음..하하하.)

자 이제 마지막으로 cc와 ip 테이블에 정보를 저장하자.

먼저 아래 query를 실행시킨다.

INSERT INTO cc SELECT DISTINCT NULL,cc,cn FROM csv;

cc 테이블에 국가코드(cc)와 국가이름(cn)을 중복된값들을 제거한후 유니크한 값들 insert 시킨다.

아래 query로 실행시킨다.

INSERT INTO ip SELECT start,end,ci FROM csv NATURAL JOIN cc;

ip테이블에 국가가 가지고 있는 ip대역값(start, end)값을 모두 구해서 저장한다.

이로써 모든 준비가 끝났다.(의외로 간단하다.-ㅁ-)

이제 써먹는 일만 남았다.

ip주소로 국가를 알아내는 방법은 ip대역을 구해서 위 ip테이블과 cc테이블을 이용하는것이다.

ip대역을 구하는 수식은 아래와 같다.

IP Address = w.x.y.z (IP주소)

ipnum = 16777216*w + 65536*x + 256*y + z (IP대역)

각 주소값을 위 수식대로 계산하여주고 나오는 ipnum이라는 값이 IP대역값이다.

이 값을 가지고 select해보자.

SELECT cc, cn FROM ip NATURAL JOIN cc WHERE 1039932929 >= start and 1039932929 <= end

위query문에 나오는 1039932929는 우리 회사서버주소의 IP대역값이다.

물론 위 query를 실행하면 korea, republic of 가 검색된다. 국가코드(cc)는 KR 이다.

팀장님의 지시로 지금 JSP로 작업하는 홈페이지에 접속통계기능을 만들라고 하셨는데.

다른건 회사홈페이지 기본틀(API, source등등)을 보면서 참조해서 만들수 있겠는데

국가도 알아야 된다고 하셔서 검색하다가 이방법을 찾았다.

그리고 나중에 또 까먹었을때 보기위해서이기도 하고 나와같이 이런 정보를 필요로 하는 사람이

있을듯하여 이렇게 정리를 해놓기로 했다...

(근데 힘들다..시간도 오래걸리고.-_-킁킁)

이만 끝!

2008년 04월 30일 수요일 무지하게 더운 봄날 오후에 . . .