GeoNames Forum

filename: import.sh

#!/bin/bash
# http://download.geonames.org/export/dump/

clear

echo "--- inicialization of importation"

rm -rf admin1Codes.txt
echo "Downloading admin1Codes.txt from geonames"
wget -c http://download.geonames.org/export/dump/admin1Codes.txt

rm -rf admin2Codes.txt
echo "Downloading admin2Codes.txt from geonames"
wget -c http://download.geonames.org/export/dump/admin2Codes.txt

rm -rf countryInfo.txt
echo "Downloading countryInfo.txt from geonames"
wget -c http://download.geonames.org/export/dump/countryInfo.txt

rm -rf featureCodes.txt
echo "Downloading featureCodes.txt from geonames"
wget -c http://download.geonames.org/export/dump/featureCodes.txt

mv -f alternateNames.zip alternateNames-bkp.zip
echo "Downloading alternateNames.zip from geonames"
wget -c http://download.geonames.org/export/dump/alternateNames.zip
unzip -o alternateNames.zip

mv -f allCountries.zip allCountries-bkp.zip
echo "Downloading allCountries.zip from geonames"
wget -c http://download.geonames.org/export/dump/allCountries.zip
unzip -o allCountries.zip

#import mysql

echo "start importation of tables"

mysql -u root geoname < sql-import.sql

echo "--- finalizado script de importação"

#3 beeps
echo -e "\a"
echo -e "\a"
echo -e "\a"

------------------- OTHER FILES

filename: sql-import.sql

-- Author: Nei Rauni Santos nrauni@yahoo.com.br
-- execute this file with this command
-- mysql -u root geoname < sql-import.sql

SET character_set_client = 'utf8';
SET character_set_results = 'utf8';

-- criação da tabela main do geoname

CREATE TABLE IF NOT EXISTS geoname (
geonameid INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'integer id of record in geonames database',
name VARCHAR (200) NULL COMMENT 'name of geographical point',
asciiname VARCHAR(200) NULL COMMENT 'name of geographical point in plain ascii characters',
alternatenames TEXT NULL COMMENT 'alternatenames',
latitude DECIMAL( 20, 15 ) NULL COMMENT 'latitude in decimal degrees (wgs84)',
longitude DECIMAL( 20, 15 ) NULL COMMENT 'longitude in decimal degrees (wgs84)',
feature_class CHAR(1) NULL COMMENT 'see http://www.geonames.org/export/codes.html',
feature_code VARCHAR(10) NULL COMMENT 'see http://www.geonames.org/export/codes.html',
country_code CHAR(2) NULL COMMENT 'ISO-3166 2-letter country code, 2 characters',
cc2 VARCHAR(60) NULL COMMENT 'alternate country codes, comma separated, ISO-3166 2-letter country code',
admin1_code VARCHAR(20) NULL COMMENT 'fipscode (subject to change to iso code), isocode for the us and ch, see file admin1Codes.txt for display names of this code',
admin2_code VARCHAR(80) NULL COMMENT 'code for the second administrative division, a county in the US, see file admin2Codes.txt',
population INT NULL,
elevation INT NULL COMMENT 'in meters',
gtopo30 INT NULL COMMENT 'average elevation of 30x30 (ca 900mx900m) area in meters',
timezone VARCHAR(40) NOT NULL COMMENT 'the timezone id (see file timeZone.txt)',
modification DATE NULL COMMENT 'date : date of last modification'
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT = 'geoname table main';

-- criaçao da tabela alternatename

CREATE TABLE IF NOT EXISTS alternatename (
alternateNameId INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'the id of this alternate name',
geonameid INT(10) NULL COMMENT 'geonameId referring to id in table geoname',
isolanguage VARCHAR(4) NULL COMMENT 'iso 693 language code 2- or 3-characters; 4-characters post for postal codes and iata or icao for airport codes',
alternate_name VARCHAR(200) NULL COMMENT 'alternate name or name variant',
isPreferredName INT(1) NULL COMMENT '1 if this alternate name is an official/preferred name',
isShortName INT(1) NULL COMMENT '1, if this is a short name like California for (State of California)'
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT = 'alternate names';

-- criaçao da tabela continentcode

CREATE TABLE IF NOT EXISTS continentcode (
geonameid INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'the id of continents in geoname table',
name VARCHAR(100) NULL COMMENT 'name of continent',
code VARCHAR(2) NULL COMMENT 'code of continent'
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

-- inserção/atualização dos continentes

REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES ('6255146', 'Africa', 'AF');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES ('6255147', 'Asia', 'AS');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES ('6255148', 'Europa', 'EU');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES ('6255149', 'North America', 'NA');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES ('6255151', 'Oceania', 'OC');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES ('6255150', 'South America', 'SA');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES ('6255152', 'Antarctica', 'AN');

-- criaçao da tabela continentcode

CREATE TABLE IF NOT EXISTS admincodes1 (
code VARCHAR(15) NOT NULL PRIMARY KEY,
name VARCHAR(100) NULL
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE IF NOT EXISTS admincodes2 (
code VARCHAR(15) NOT NULL PRIMARY KEY,
name VARCHAR(100) NULL,
name2 VARCHAR(100) NULL,
geonameid INT(10) NULL
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

-- criaçao da tabela continentcode
-- BR|BRA|076|BR|Brazil|Brasília|8511965.0|186112794|SA|pt-BR|BRL|3469034
-- ISO ISO3 ISO-Numeric fips Country Capital Area(in sq km) Population Continent tld CurrencyCode CurrencyName Phone Postal Code Format Postal Code Regex Languages geonameid neighbours EquivalentFipsCode

CREATE TABLE IF NOT EXISTS countryinfo (
iso_alpha2 CHAR(2) NULL,
iso_alpha3 CHAR(3) NULL,
iso_numeric INT(10) NULL,
fips CHAR(3) NULL,
name VARCHAR(200) NULL,
capital VARCHAR(200) NULL,
areainsqkm DECIMAL(20,1) NULL,
population INT(20) NULL,
continent CHAR(2) NULL,
languages VARCHAR(200) NULL,
other1 VARCHAR(3) NULL,
other2 INT(20) NULL
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Remark : the field 'alternatenames' in the table 'geoname' is a short version of the 'alternatenames' table. You probably don't need both.
-- If you don't need to know the language of a name variant, the field 'alternatenames' will be sufficient. If you need to know the language
-- of a name variant, then you will need to load the table 'alternatenames' and you can drop the column in the geoname table.

LOAD DATA INFILE '/home/nei/projects/geoname/geonames/countryInfo.txt' REPLACE INTO TABLE countryinfo FIELDS TERMINATED BY '\t';

LOAD DATA INFILE '/home/nei/projects/geoname/geonames/admin1Codes.txt' REPLACE INTO TABLE admincodes1 FIELDS TERMINATED BY '\t';

LOAD DATA INFILE '/home/nei/projects/geoname/geonames/admin2Codes.txt' REPLACE INTO TABLE admincodes2 FIELDS TERMINATED BY '\t';

LOAD DATA INFILE '/home/nei/projects/geoname/geonames/alternateNames.txt' REPLACE INTO TABLE alternatename FIELDS TERMINATED BY '\t';

LOAD DATA INFILE '/home/nei/projects/geoname/geonames/allCountries.txt' REPLACE INTO TABLE geoname FIELDS TERMINATED BY '\t';