Loading GeoNames data into MySQL

http://forum.geonames.org/gforum/posts/list/80.page

Get and unzip allCountries.zip and alternateNames.zip.

Create a database named geonames. Select it, then run the following SQL queries:

CREATE TABLE `geonames` (
`geonameid` int(10) unsigned NOT NULL default '0',
`name` varchar(200) NOT NULL default '',
`ansiname` varchar(200) NOT NULL default '',
`alternames` varchar(200) NOT NULL default '',
`latitude` double NOT NULL default '0',
`longitude` double NOT NULL default '0',
`feature_class` char(1) ,
`feature_code` varchar(10) ,
`country_code` char(2),
`cc2` varchar(60),
`admin1_code` varchar(20) default '',
`admin2_code` varchar(80) default '',
`admin3_code` varchar(20) default '',
`admin4_code` varchar(20) default '',
`population` int(11) default '0',
`elevation` int(11) default '0',
`gtopo30` int(11) default '0',
`timezone` varchar(40),
`modification_date` date default '0000-00-00',
PRIMARY KEY (`geonameid`)
) CHARACTER SET utf8 ;


CREATE TABLE `alternateNames` (
`alternateNameId` int(11) NOT NULL,
`geonameid` int(11) NOT NULL,
`isolanguage` varchar(7) NOT NULL,
`alternatename` varchar(200) NOT NULL,
`isPreferredName` enum('','1') NOT NULL,
`isShortName` enum('','1') NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

load data infile '/permanent1/www/geonames/allCountries.txt' INTO TABLE geonames;
load data infile '/permanent1/www/geonames/alternateNames.txt' INTO TABLE alternateNames;

(your path will be different)

ALTER TABLE `geonames` ADD INDEX ( `name` );
ALTER TABLE `geonames` ADD INDEX ( `ansiname` );
ALTER TABLE `geonames` ADD INDEX ( `population` );
ALTER TABLE `geonames` ADD INDEX ( `alternames` );

ALTER TABLE `alternateNames` ADD INDEX ( `alternatename` );
ALTER TABLE `alternateNames` ADD INDEX ( `geonameid` );