p50jVz9t

1/*
2curl -O http://geolite.maxmind.com/download/geoip/database/GeoLite2-City-CSV.zip
3unzip -j GeoLite2-City-CSV.zip
4*/
5
6
7drop table if exists geoip2ci2;
8CREATE TABLE geoip2ci2 (
9	locId MEDIUMINT(6) UNSIGNED NOT NULL,
10	co ENUM('AF','AN','AS','EU','NA','OC','SA') NOT NULL,
11	cc ENUM('','AD','AE','AF','AG','AI','AL','AM','AO','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','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','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','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','XK','YE','YT','ZA','ZM','ZW') NOT NULL,
12	cn ENUM('','Afghanistan','Albania','Algeria','American Samoa','Andorra','Angola','Anguilla','Antarctica','Antigua and Barbuda','Argentina','Armenia','Aruba','Australia','Austria','Azerbaijan','Bahamas','Bahrain','Bangladesh','Barbados','Belarus','Belgium','Belize','Benin','Bermuda','Bhutan','Bolivia','Bonaire, Sint Eustatius, and Saba','Bosnia and Herzegovina','Botswana','Brazil','British Indian Ocean Territory','British Virgin Islands','Brunei','Bulgaria','Burkina Faso','Burundi','Cabo Verde','Cambodia','Cameroon','Canada','Cayman Islands','Central African Republic','Chad','Chile','China','Christmas Island','Cocos [Keeling] Islands','Colombia','Comoros','Congo','Cook Islands','Costa Rica','Croatia','Cuba','Cura?ao','Cyprus','Czechia','Denmark','Djibouti','Dominica','Dominican Republic','East Timor','Ecuador','Egypt','El Salvador','Equatorial Guinea','Eritrea','Estonia','Ethiopia','Falkland Islands','Faroe Islands','Federated States of Micronesia','Fiji','Finland','France','French Guiana','French Polynesia','French Southern Territories','Gabon','Gambia','Georgia','Germany','Ghana','Gibraltar','Greece','Greenland','Grenada','Guadeloupe','Guam','Guatemala','Guernsey','Guinea','Guinea-Bissau','Guyana','Haiti','Hashemite Kingdom of Jordan','Heard Island and McDonald Islands','Honduras','Hong Kong','Hungary','Iceland','India','Indonesia','Iran','Iraq','Ireland','Isle of Man','Israel','Italy','Ivory Coast','Jamaica','Japan','Jersey','Kazakhstan','Kenya','Kiribati','Kosovo','Kuwait','Kyrgyzstan','Laos','Latvia','Lebanon','Lesotho','Liberia','Libya','Liechtenstein','Luxembourg','Macao','Macedonia','Madagascar','Malawi','Malaysia','Maldives','Mali','Malta','Marshall Islands','Martinique','Mauritania','Mauritius','Mayotte','Mexico','Monaco','Mongolia','Montenegro','Montserrat','Morocco','Mozambique','Myanmar [Burma]','Namibia','Nauru','Nepal','Netherlands','New Caledonia','New Zealand','Nicaragua','Niger','Nigeria','Niue','Norfolk Island','North Korea','Northern Mariana Islands','Norway','Oman','Pakistan','Palau','Palestine','Panama','Papua New Guinea','Paraguay','Peru','Philippines','Pitcairn Islands','Poland','Portugal','Puerto Rico','Qatar','Republic of Korea','Republic of Lithuania','Republic of Moldova','Republic of the Congo','R?union','Romania','Russia','Rwanda','Saint Helena','Saint Lucia','Saint Martin','Saint Pierre and Miquelon','Saint Vincent and the Grenadines','Saint-Barth?lemy','Samoa','San Marino','S?o Tom? and Pr?ncipe','Saudi Arabia','Senegal','Serbia','Seychelles','Sierra Leone','Singapore','Sint Maarten','Slovakia','Slovenia','Solomon Islands','Somalia','South Africa','South Georgia and the South Sandwich Islands','South Sudan','Spain','Sri Lanka','St Kitts and Nevis','Sudan','Suriname','Svalbard and Jan Mayen','Swaziland','Sweden','Switzerland','Syria','Taiwan','Tajikistan','Tanzania','Thailand','Togo','Tokelau','Tonga','Trinidad and Tobago','Tunisia','Turkey','Turkmenistan','Turks and Caicos Islands','Tuvalu','U.S. Minor Outlying Islands','U.S. Virgin Islands','Uganda','Ukraine','United Arab Emirates','United Kingdom','United States','Uruguay','Uzbekistan','Vanuatu','Vatican City','Venezuela','Vietnam','Wallis and Futuna','Western Sahara','Yemen','Zambia','Zimbabwe','?land') NOT NULL,
13	sub1_iso VARCHAR(3) NULL DEFAULT NULL,
14	sub1_name VARCHAR(64) NULL DEFAULT NULL,
15	sub2_iso VARCHAR(3) NULL DEFAULT NULL,
16	sub2_name VARCHAR(64) NULL DEFAULT NULL,
17	city VARCHAR(64) NULL DEFAULT NULL,
18	mc VARCHAR(3) NULL DEFAULT NULL,
19	tz VARCHAR(30) NULL DEFAULT NULL,
20	PRIMARY KEY (locId),
21	INDEX cc (cc)
22)
23ENGINE=MyISAM DEFAULT CHARSET=utf8
24;
25
26-- geoname_id,locale_code,continent_code,continent_name,country_iso_code,country_name,subdivision_1_iso_code,subdivision_1_name,subdivision_2_iso_code,subdivision_2_name,city_name,metro_code,time_zone
27truncate table geoip2ci2;
28load data infile '/tmp/GeoLite2-City-Locations-en.csv' into table geoip2ci2
29CHARACTER SET 'utf8'
30FIELDS TERMINATED BY ','
31OPTIONALLY ENCLOSED BY '"'
32LINES TERMINATED BY '\n'
33IGNORE 1 lines
34(locId,@locale_code,co,@continent_name,cc,cn,sub1_iso,sub1_name,sub2_iso,sub2_name,city,mc,tz);
35
36
37-- network,geoname_id,registered_country_geoname_id,represented_country_geoname_id,is_anonymous_proxy,is_satellite_provider,postal_code,latitude,longitude,accuracy_radius
38
39
40drop table if exists geoip2ci1;
41CREATE TABLE geoip2ci1 (
42    i1 INT(10) UNSIGNED NULL DEFAULT inet_aton(substring_index(prefix,'/',1)) primary key,
43    i2 INT(10) UNSIGNED NULL DEFAULT (inet_aton(substring_index(prefix,'/',1))+power(2,32-substring_index(prefix,'/',-1))-1),
44    prefix VARCHAR(18) NULL DEFAULT NULL,
45    loc  mediumint unsigned NULL,    
46    reg  mediumint unsigned NULL,
47--    loc2  varchar(20) NULL,
48    proxy ENUM('0','1') NOT NULL,
49    sat ENUM('0','1') NOT NULL,
50--    postal varchar(8),
51    lat float,
52    lon float,
53    -- radius int,
54    radius ENUM('0','1','5','10','20','50','100','200','500','1000') NOT NULL,
55    index loc(loc),
56    index reg(reg)
57)
58ENGINE=MyISAM;
59 
60 
61truncate table geoip2ci1;
62load data infile '/tmp/GeoLite2-City-Blocks-IPv4.csv' into table geoip2ci1
63CHARACTER SET 'utf8'
64FIELDS TERMINATED BY ','
65OPTIONALLY ENCLOSED BY '"'
66LINES TERMINATED BY '\n'
67IGNORE 1 lines
68(prefix,@loc0,@loc1,@loc2,proxy,sat,@postal,lat,lon,radius) set loc=convert(@loc0,int),reg=convert(@loc1,int);
69update geoip2ci1 set loc =null where loc=0;
70update geoip2ci1 set reg =null where reg=0;
71 
72optimize table geoip2ci1;
73optimize table geoip2ci2;
74 
75
76drop view if exists geoip2ci;
77create view geoip2ci as select A.*,B.cc,C.cc regcc ,B.cn ,C.cn regcn,B.sub1_name region,B.city from geoip2ci1 A left join geoip2ci2 B on A.loc=B.locid left join geoip2ci2 C on A.reg=C.locid;