IP 순위 Top30 구글맵에 표시하기

중복된 IP 테이터가 있습니다. IP 를 기준으로 Group By 하면 순위가 되는 데이터인데 이러한 데이터를 세계지도에 표기하여 전체적인 흐름을 한눈에 보고 싶었습니다. 이미 많은 분들이 웹을 통해 관련 노하우들을 많이 공개 하셨는데요. 저는 조금 더 짬뽕으로 다음과 같은 기능들을 한번에 구현해 보았습니다.

1. IP에 해당하는 좌표데이터 얻기.
2. 좌표 데이터를 MySQL 에 집어 넣기
3. IP 와 좌표를 이용해 구글맵에 마크하기.
4. 순위에 따라 구글맵 마커 모양 변경하기
5. 마커 클릭시 풍선 도움말 띄우기

참고로 결과 화면을 먼저 보여드리면 아래와 같습니다.

0) 개발환경

- MySQL & Apache & PHP
- Google Map API
- MaxMind 의 GeoLiteCity 데이터

 

1) IP에 해당하는 좌표데이터 얻기.

무료로 제공되는 데이터가 있습니다. 물론 상용버전을 사용하면 보다 정확도가 높아진다고 하던군요. POC 정도 만들어 볼때는 무료도 훌륭합니다. 다운받기...

위에서 받을 파일의 압축을 풀면 CSV 형식의 파일 두 개가 존재합니다. 아래 스크립트를 이용해 쉽게 MySQL 로 옮길 수 있으니 걱정하지 마세요. 그리고 계속 업데이트 되고 있으니 이 글이 써진 후 어느정도 시간이 지났다면 여기를 방문해서 최신의 파일을 받으세요.

2) 좌표데이터를 MySQL 에 집어 넣기.

아래 SQL 스크립트는 관련 테이블 두 개를 만들고, 위에서 다운로드 받은 CSV 파일을 각각의 테이블에 입력합니다. 그 후 IP를 특정한 숫자로 변환해주는 MySQL 함수를 만드는 과정을 거치게 됩니다.

DROP TABLE IF EXISTS GeoLiteCityBlocks;
CREATE TABLE GeoLiteCityBlocks (
startIPNum int(10) UNSIGNED NOT NULL,
endIPNum int(10) UNSIGNED NOT NULL,
locID int(10) UNSIGNED NOT NULL,
PRIMARY KEY (startIPNum,endIPNum));

DROP TABLE IF EXISTS GeoLiteCityLocation;
CREATE TABLE GeoLiteCityLocation (
locID int(10) UNSIGNED NOT NULL,
country char(2) default NULL,
region char(2) default NULL,
city varchar(45) default NULL,
postalCode char(7) default NULL,
latitude double default NULL,
longitude double default NULL,
dmaCode char(3) default NULL,
areaCode char(3) default NULL,
PRIMARY KEY (locID),
KEY Index_Country (country));

LOAD DATA LOCAL INFILE '/root/GeoLiteCity-Blocks.csv'
INTO TABLE GeoLiteCityBlocks
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
IGNORE 2 LINES;

LOAD DATA LOCAL INFILE '/root/GeoLiteCity-Location.csv'
INTO TABLE GeoLiteCityLocation
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
IGNORE 2 LINES;

DELIMITER $$
DROP FUNCTION IF EXISTS asms.IPTOLOCID $$
CREATE FUNCTION asms.IPTOLOCID( ip VARCHAR(15)) RETURNS int(10) UNSIGNED
BEGIN
DECLARE ipn INTEGER UNSIGNED;
DECLARE locID_var INTEGER;
IF ip LIKE '192.168.%' OR ip LIKE '10.%' THEN
RETURN 0;
END IF;

SET ipn = INET_ATON(ip);
SELECT locID INTO locID_var
FROM GeoLiteCityBlocks
INNER JOIN
(SELECT MAX(startIPNum) AS start
FROM GeoLiteCityBlocks
WHERE startIPNum <= ipn) AS s
ON (startIPNum = s.start)
WHERE endIPNum >= ipn;
RETURN locID_var;
END $$

DELIMITER ;
SELECT IPTOLOCID('66.220.17.200');

 

3) IP와 좌표를 이용해 구글맵에 마크하기

4) 순위에 따라 구글맵 마커 모양 변경하기

5) 마커 클릭시 풍선 도움말 띄우기

위에 해당하는 소스를 블로그에 이쁘게 적어보려 했는데 잘 안되서 파일을 올립니다. 아래 소스에서는 구글맵(Google MAP) API 중에서 다음과 같은 객체를 사용하였습니다. GMap2, GLargeMapControl, GLatLng, GIcon, GMarkerOptions, GMarker

malurldistribution.php