Maxmind Free World Cities Database to MySQL custom city table

lag-lng-cities-catalog.php

<?php
// this script will parse data from maxmind cities list
// http://download.maxmind.com/download/worldcities/worldcitiespop.txt.gz

// country list taken from
// https://github.com/raramuridesign/mysql-country-list/blob/master/mysql-country-list.sql

ini_set('default_charset', 'UTF-8');

$arr[1] = 'AF';
$arr[2] = 'AL';
$arr[3] = 'DZ';
$arr[4] = 'DS';
$arr[5] = 'AD';
$arr[6] = 'AO';
$arr[7] = 'AI';
$arr[8] = 'AQ';
$arr[9] = 'AG';
$arr[10] = 'AR';
$arr[11] = 'AM';
$arr[12] = 'AW';
$arr[13] = 'AU';
$arr[14] = 'AT';
$arr[15] = 'AZ';
$arr[16] = 'BS';
$arr[17] = 'BH';
$arr[18] = 'BD';
$arr[19] = 'BB';
$arr[20] = 'BY';
$arr[21] = 'BE';
$arr[22] = 'BZ';
$arr[23] = 'BJ';
$arr[24] = 'BM';
$arr[25] = 'BT';
$arr[26] = 'BO';
$arr[27] = 'BA';
$arr[28] = 'BW';
$arr[29] = 'BV';
$arr[30] = 'BR';
$arr[31] = 'IO';
$arr[32] = 'BN';
$arr[33] = 'BG';
$arr[34] = 'BF';
$arr[35] = 'BI';
$arr[36] = 'KH';
$arr[37] = 'CM';
$arr[38] = 'CA';
$arr[39] = 'CV';
$arr[40] = 'KY';
$arr[41] = 'CF';
$arr[42] = 'TD';
$arr[43] = 'CL';
$arr[44] = 'CN';
$arr[45] = 'CX';
$arr[46] = 'CC';
$arr[47] = 'CO';
$arr[48] = 'KM';
$arr[49] = 'CG';
$arr[50] = 'CK';
$arr[51] = 'CR';
$arr[52] = 'HR';
$arr[53] = 'CU';
$arr[54] = 'CY';
$arr[55] = 'CZ';
$arr[56] = 'DK';
$arr[57] = 'DJ';
$arr[58] = 'DM';
$arr[59] = 'DO';
$arr[60] = 'TP';
$arr[61] = 'EC';
$arr[62] = 'EG';
$arr[63] = 'SV';
$arr[64] = 'GQ';
$arr[65] = 'ER';
$arr[66] = 'EE';
$arr[67] = 'ET';
$arr[68] = 'FK';
$arr[69] = 'FO';
$arr[70] = 'FJ';
$arr[71] = 'FI';
$arr[72] = 'FR';
$arr[73] = 'FX';
$arr[74] = 'GF';
$arr[75] = 'PF';
$arr[76] = 'TF';
$arr[77] = 'GA';
$arr[78] = 'GM';
$arr[79] = 'GE';
$arr[80] = 'DE';
$arr[81] = 'GH';
$arr[82] = 'GI';
$arr[83] = 'GK';
$arr[84] = 'GR';
$arr[85] = 'GL';
$arr[86] = 'GD';
$arr[87] = 'GP';
$arr[88] = 'GU';
$arr[89] = 'GT';
$arr[90] = 'GN';
$arr[91] = 'GW';
$arr[92] = 'GY';
$arr[93] = 'HT';
$arr[94] = 'HM';
$arr[95] = 'HN';
$arr[96] = 'HK';
$arr[97] = 'HU';
$arr[98] = 'IS';
$arr[99] = 'IN';
$arr[100] = 'IM';
$arr[101] = 'ID';
$arr[102] = 'IR';
$arr[103] = 'IQ';
$arr[104] = 'IE';
$arr[105] = 'IL';
$arr[106] = 'IT';
$arr[107] = 'CI';
$arr[108] = 'JE';
$arr[109] = 'JM';
$arr[110] = 'JP';
$arr[111] = 'JO';
$arr[112] = 'KZ';
$arr[113] = 'KE';
$arr[114] = 'KI';
$arr[115] = 'KP';
$arr[116] = 'KR';
$arr[117] = 'XK';
$arr[118] = 'KW';
$arr[119] = 'KG';
$arr[120] = 'LA';
$arr[121] = 'LV';
$arr[122] = 'LB';
$arr[123] = 'LS';
$arr[124] = 'LR';
$arr[125] = 'LY';
$arr[126] = 'LI';
$arr[127] = 'LT';
$arr[128] = 'LU';
$arr[129] = 'MO';
$arr[130] = 'MK';
$arr[131] = 'MG';
$arr[132] = 'MW';
$arr[133] = 'MY';
$arr[134] = 'MV';
$arr[135] = 'ML';
$arr[136] = 'MT';
$arr[137] = 'MH';
$arr[138] = 'MQ';
$arr[139] = 'MR';
$arr[140] = 'MU';
$arr[141] = 'TY';
$arr[142] = 'MX';
$arr[143] = 'FM';
$arr[144] = 'MD';
$arr[145] = 'MC';
$arr[146] = 'MN';
$arr[147] = 'ME';
$arr[148] = 'MS';
$arr[149] = 'MA';
$arr[150] = 'MZ';
$arr[151] = 'MM';
$arr[152] = 'NA';
$arr[153] = 'NR';
$arr[154] = 'NP';
$arr[155] = 'NL';
$arr[156] = 'AN';
$arr[157] = 'NC';
$arr[158] = 'NZ';
$arr[159] = 'NI';
$arr[160] = 'NE';
$arr[161] = 'NG';
$arr[162] = 'NU';
$arr[163] = 'NF';
$arr[164] = 'MP';
$arr[165] = 'NO';
$arr[166] = 'OM';
$arr[167] = 'PK';
$arr[168] = 'PW';
$arr[169] = 'PS';
$arr[170] = 'PA';
$arr[171] = 'PG';
$arr[172] = 'PY';
$arr[173] = 'PE';
$arr[174] = 'PH';
$arr[175] = 'PN';
$arr[176] = 'PL';
$arr[177] = 'PT';
$arr[178] = 'PR';
$arr[179] = 'QA';
$arr[180] = 'RE';
$arr[181] = 'RO';
$arr[182] = 'RU';
$arr[183] = 'RW';
$arr[184] = 'KN';
$arr[185] = 'LC';
$arr[186] = 'VC';
$arr[187] = 'WS';
$arr[188] = 'SM';
$arr[189] = 'ST';
$arr[190] = 'SA';
$arr[191] = 'SN';
$arr[192] = 'RS';
$arr[193] = 'SC';
$arr[194] = 'SL';
$arr[195] = 'SG';
$arr[196] = 'SK';
$arr[197] = 'SI';
$arr[198] = 'SB';
$arr[199] = 'SO';
$arr[200] = 'ZA';
$arr[201] = 'GS';
$arr[202] = 'SS';
$arr[203] = 'ES';
$arr[204] = 'LK';
$arr[205] = 'SH';
$arr[206] = 'PM';
$arr[207] = 'SD';
$arr[208] = 'SR';
$arr[209] = 'SJ';
$arr[210] = 'SZ';
$arr[211] = 'SE';
$arr[212] = 'CH';
$arr[213] = 'SY';
$arr[214] = 'TW';
$arr[215] = 'TJ';
$arr[216] = 'TZ';
$arr[217] = 'TH';
$arr[218] = 'TG';
$arr[219] = 'TK';
$arr[220] = 'TO';
$arr[221] = 'TT';
$arr[222] = 'TN';
$arr[223] = 'TR';
$arr[224] = 'TM';
$arr[225] = 'TC';
$arr[226] = 'TV';
$arr[227] = 'UG';
$arr[228] = 'UA';
$arr[229] = 'AE';
$arr[230] = 'GB';
$arr[231] = 'US';
$arr[232] = 'UM';
$arr[233] = 'UY';
$arr[234] = 'UZ';
$arr[235] = 'VU';
$arr[236] = 'VA';
$arr[237] = 'VE';
$arr[238] = 'VN';
$arr[239] = 'VG';
$arr[240] = 'VI';
$arr[241] = 'WF';
$arr[242] = 'EH';
$arr[243] = 'YE';
$arr[244] = 'ZR';
$arr[245] = 'ZM';
$arr[246] = 'ZW';

$filename = "worldcitiespop.txt";
$fin = fopen($filename, 'r') or die('cant open file');
$i = 0;
while (($data = fgetcsv($fin, 1000, ",")) !== FALSE) {
	$i++;
	if ($i === 1) continue;
	$index = array_search(strtoupper($data[0]), $arr);
    if ($index > 0) {
        $query = utf8_encode('INSERT INTO city (name, lat, lng, country_id) VALUES ("'.str_replace('"', '\'', $data[2]).'", '.$data[5].', '.$data[6].', '.$index.');'.PHP_EOL);
        $myfile = file_put_contents('cities.sql', $query, FILE_APPEND | LOCK_EX);
    } 
}
fclose($fin);

/**
DROP TABLE IF EXISTS `country` ;

CREATE TABLE IF NOT EXISTS `country` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(5) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM;

-- -----------------------------------------------------
-- Table `city`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `city` ;

CREATE TABLE IF NOT EXISTS `city` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `lat` DECIMAL(11,8) NOT NULL,
  `lng` DECIMAL(11,8) NOT NULL,
  `country_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_city_country1_idx` (`country_id` ASC))
ENGINE = MyISAM;
**/
?>