관련출처링크
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일 수요일 무지하게 더운 봄날 오후에 . . .
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일 수요일 무지하게 더운 봄날 오후에 . . .
'database > my-sql' 카테고리의 다른 글
Mysql 우편번호(주소) sql 파일 [zipcode.sql] (0) | 2008.04.18 |
---|