CREATE DATABASE geonames DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; use geonames; CREATE TABLE geonames ( geoname_id mediumint PRIMARY KEY, name varchar(200), ascii_name varchar(200), alternate_names varchar(4000), latitude decimal(10,7), longitude decimal(10,7), feature_class char(1), feature_code varchar(10), country_code varchar(2), cc2 varchar(60), admin1_code varchar(20), admin2_code varchar(80), admin3_code varchar(20), admin4_code varchar(20), population bigint, elevation int, gtopo30 smallint, time_zone varchar(40), modification_date date ) CHARACTER SET utf8; CREATE TABLE alternate_names ( alternate_name_id int PRIMARY KEY, geoname_id int, iso_language varchar(7), alternate_name varchar(200), is_preferred_name boolean, is_short_name boolean ) CHARACTER SET utf8; CREATE TABLE country_info ( iso CHAR(2), iso3 CHAR(3), iso_numeric int, fips character varying(3), country character varying(200), capital character varying(200), area_in_sq_km double precision, population mediumint, continent char(2), tld CHAR(10), currency_code char(3), currency_name CHAR(10), phone character varying(10), postal_code_format character varying(100), postal_code_regex character varying(100), languages character varying(200), geoname_id int, neighbours character varying(50), equivalent_fips_code character varying(3) ) CHARACTER SET utf8; CREATE TABLE iso_language_codes( iso_639_3 CHAR(4), iso_639_2 VARCHAR(50), iso_639_1 VARCHAR(50), language_name VARCHAR(200) ) CHARACTER SET utf8; CREATE TABLE admin1_codes ( code CHAR(10), name TEXT ) CHARACTER SET utf8; CREATE TABLE admin1_codes_ascii ( code CHAR(10), name TEXT, name_ascii TEXT, geoname_id int ) CHARACTER SET utf8; CREATE TABLE admin2_codes ( code char(10) default NULL, name_local text, name text NOT NULL, geoname_id int(11) NOT NULL ) CHARACTER SET utf8; CREATE TABLE feature_codes ( code CHAR(7), name VARCHAR(200), description TEXT ) CHARACTER SET utf8; CREATE TABLE time_zones ( time_zone_id VARCHAR(200), gmt_offset DECIMAL(3,1), dst_offset DECIMAL(3,1) ) CHARACTER SET utf8; CREATE TABLE continent_codes ( code CHAR(2), name VARCHAR(20), geoname_id INT ) CHARACTER SET utf8; SET CHARACTER SET utf8; SET NAMES utf8; LOAD DATA LOCAL INFILE 'allCountries.txt' INTO TABLE geonames (geoname_id, name, ascii_name, alternate_names, latitude, longitude, feature_class, feature_code, country_code, cc2, admin1_code, admin2_code, admin3_code, admin4_code, population, elevation, gtopo30, time_zone, modification_date); LOAD DATA LOCAL INFILE 'alternateNames.txt' INTO TABLE alternate_names (alternate_name_id, geoname_id, iso_language, alternate_name, is_preferred_name, is_short_name); LOAD DATA LOCAL INFILE 'iso-languagecodes.txt' INTO TABLE iso_language_codes (iso_639_3, iso_639_2, iso_639_1, language_name); LOAD DATA LOCAL INFILE 'admin1Codes.txt' INTO TABLE admin1_codes (code, name); LOAD DATA LOCAL INFILE 'admin1CodesASCII.txt' INTO TABLE admin1_codes_ascii (code, name, name_ascii, geoname_id); LOAD DATA LOCAL INFILE 'admin2Codes.txt' INTO TABLE admin2_codes (code, name_local, name, geoname_id); LOAD DATA LOCAL INFILE 'featureCodes.txt' INTO TABLE feature_codes (code, name, description); LOAD DATA LOCAL INFILE 'timeZones.txt' INTO TABLE time_zones IGNORE 1 LINES (time_zone_id, gmt_offset, dst_offset); LOAD DATA LOCAL INFILE 'countryInfo.txt' INTO TABLE country_info (iso, iso3, iso_numeric, fips, country, capital, area_in_sq_km, population, continent, tld, currency_code, currency_name, phone, postal_code_format, postal_code_regex, languages, geoname_id, neighbours, equivalent_fips_code); LOAD DATA LOCAL INFILE 'continentCodes.txt' INTO TABLE continent_codes FIELDS TERMINATED BY ',' (code, name, geoname_id);