hx5kkXTf

1/*
2curl -O http://geolite.maxmind.com/download/geoip/database/GeoLiteCity_CSV/GeoLiteCity-latest.zip
3unzip -j GeoLiteCity-latest.zip
4
5*/
6
7drop table if exists geoipci1;
8CREATE TABLE geoipci1 (
9	i1 INT(10) UNSIGNED NOT NULL primary key,
10   i2 INT(10) UNSIGNED NOT NULL,
11   loc MEDIUMINT(6) UNSIGNED NOT NULL,
12   index loc(loc)
13)
14ENGINE=MyISAM;
15
16truncate table geoipci1;
17load data infile '/tmp/GeoLiteCity-Blocks.csv' into table geoipci1
18FIELDS TERMINATED BY ','
19OPTIONALLY ENCLOSED BY '"'
20LINES TERMINATED BY '\n'
21IGNORE 2 lines
22(i1,i2,loc);
23
24drop table if exists geoipci2;
25CREATE TABLE geoipci2 (
26	locId MEDIUMINT(6) UNSIGNED NOT NULL primary key,
27   cc ENUM('A1','A2','AD','AE','AF','AG','AI','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AW','AX','AZ','BA','BB','BD','BE','BF','BG','BH','BI','BJ','BL','BM','BN','BO','BQ','BR','BS','BT','BV','BW','BY','BZ','CA','CC','CD','CF','CG','CH','CI','CK','CL','CM','CN','CO','CR','CU','CV','CW','CX','CY','CZ','DE','DJ','DK','DM','DO','DZ','EC','EE','EG','EH','ER','ES','ET','EU','FI','FJ','FK','FM','FO','FR','GA','GB','GD','GE','GF','GG','GH','GI','GL','GM','GN','GP','GQ','GR','GS','GT','GU','GW','GY','HK','HM','HN','HR','HT','HU','ID','IE','IL','IM','IN','IO','IQ','IR','IS','IT','JE','JM','JO','JP','KE','KG','KH','KI','KM','KN','KP','KR','KW','KY','KZ','LA','LB','LC','LI','LK','LR','LS','LT','LU','LV','LY','MA','MC','MD','ME','MF','MG','MH','MK','ML','MM','MN','MO','MP','MQ','MR','MS','MT','MU','MV','MW','MX','MY','MZ','NA','NC','NE','NF','NG','NI','NL','NO','NP','NR','NU','NZ','O1','OM','PA','PE','PF','PG','PH','PK','PL','PM','PN','PR','PS','PT','PW','PY','QA','RE','RO','RS','RU','RW','SA','SB','SC','SD','SE','SG','SH','SI','SJ','SK','SL','SM','SN','SO','SR','SS','ST','SV','SX','SY','SZ','TC','TD','TF','TG','TH','TJ','TK','TL','TM','TN','TO','TR','TT','TV','TW','TZ','UA','UG','UM','US','UY','UZ','VA','VC','VE','VG','VI','VN','VU','WF','WS','YE','YT','ZA','ZM','ZW') NOT NULL,
28   region varchar(2),
29   city varchar(50),
30   postalCode  varchar(8),
31   lat float,
32   lon float,
33   metroCode varchar(3),
34   areaCode varchar(3),
35   index cc (cc)
36
37)
38ENGINE=MyISAM;
39
40truncate table geoipci2;
41load data infile '/tmp/GeoLiteCity-Location.csv' into table geoipci2
42FIELDS TERMINATED BY ','
43OPTIONALLY ENCLOSED BY '"'
44LINES TERMINATED BY '\n'
45IGNORE 2 lines
46(locId,cc,region,city,postalCode,lat,lon,metroCode,areaCode);
47
48drop view if exists geoipci;
49create view geoipci as
50select i1,i2,loc,cc,region,city,lat,lon,postalCode,metroCode,areaCode from geoipci1 inner join geoipci2 on geoipci1.loc=geoipci2.locid;
51
52optimize table geoipci1;
53optimize table geoipci2;
54
55-- select *,inet_ntoa(i1)ip1,inet_ntoa(i2)ip2 from geoipci where inet_aton('8.8.4.4') >=i1 order by i1 desc limit 1;