API tools faq
paste
Advertisement
rg443

geoip2 lite citymysql import

rg443
Jan 7th, 2018
707
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 7.28 KB | None | 0 0
raw download clone embed print report
  1. /*
  2. curl -O http://geolite.maxmind.com/download/geoip/database/GeoLite2-City-CSV.zip
  3. unzip -j GeoLite2-City-CSV.zip
  4. */
  5.  
  6.  
  7. drop table if exists geoip2ci2;
  8. CREATE 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. )
  23. ENGINE=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
  27. truncate table geoip2ci2;
  28. load data infile '/tmp/GeoLite2-City-Locations-en.csv' into table geoip2ci2
  29. CHARACTER SET 'utf8'
  30. FIELDS TERMINATED BY ','
  31. OPTIONALLY ENCLOSED BY '"'
  32. LINES TERMINATED BY '\n'
  33. IGNORE 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.  
  40. drop table if exists geoip2ci1;
  41. CREATE 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. )
  58. ENGINE=MyISAM;
  59.  
  60.  
  61. truncate table geoip2ci1;
  62. load data infile '/tmp/GeoLite2-City-Blocks-IPv4.csv' into table geoip2ci1
  63. CHARACTER SET 'utf8'
  64. FIELDS TERMINATED BY ','
  65. OPTIONALLY ENCLOSED BY '"'
  66. LINES TERMINATED BY '\n'
  67. IGNORE 1 lines
  68. (prefix,@loc0,@loc1,@loc2,proxy,sat,@postal,lat,lon,radius) set loc=convert(@loc0,int),reg=convert(@loc1,int);
  69. update geoip2ci1 set loc =null where loc=0;
  70. update geoip2ci1 set reg =null where reg=0;
  71.  
  72. optimize table geoip2ci1;
  73. optimize table geoip2ci2;
  74.  
  75.  
  76. drop view if exists geoip2ci;
  77. create view geoip2ci as select A.*,B.cc,C.cc regcc ,B.cn ,C.cn regcn,B.sub1_name region,B.sub2_name region2,B.city from geoip2ci1 A left join geoip2ci2 B on A.loc=B.locid left join geoip2ci2 C on A.reg=C.locid;
  78.  
  79. -- set @i:=inet_aton('37.209.252.0'); select A.* from (select * from geoip2ci where @i >= i1 order by i1 desc limit 1) A where @i<=i2
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!