Открытые базы данных

GeoNames

• WebSite: http://www.geonames.org/
• Instructions: http://download.geonames.org/export/dump/readme.txt
• Data: http://download.geonames.org/export/dump/
• Zips: http://download.geonames.org/export/zip/

for i in `wget -O - --quiet http://download.geonames.org/export/dump/ | grep '/icons/compressed.gif' | grep -E '[A-Z]{2}\.zip' | awk -F\" '{print $6}'`; do wget http://download.geonames.org/export/dump/$i; done;
mkdir rawdata
for i in `ls -1 *.zip`; do unzip $i -d ./rawdata; done;

CREATE TABLE `geoname` (
  `geonameid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NULL DEFAULT NULL,
  `asciiname` varchar(200) NULL DEFAULT NULL,
  `alternatenames` varchar(10000) NULL DEFAULT NULL,
  `latitude` decimal NULL DEFAULT NULL,
  `longitude` decimal NULL DEFAULT NULL,
  `feature_class` char(1) NULL DEFAULT NULL,
  `feature_code` varchar(10) NULL DEFAULT NULL,
  `country_code` char(2) NULL DEFAULT NULL,
  `cc2` varchar(200) NULL DEFAULT NULL,
  `admin1_code` varchar(20) NULL DEFAULT NULL,
  `admin2_code` varchar(80) NULL DEFAULT NULL,
  `admin3_code` varchar(20) NULL DEFAULT NULL,
  `admin4_code` varchar(20) NULL DEFAULT NULL,
  `population` bigint(20) unsigned NULL DEFAULT NULL,
  `elevation` bigint(20) unsigned NULL DEFAULT NULL,
  `dem` bigint(20) unsigned NULL DEFAULT NULL,
  `timezone` bigint(20) unsigned NULL DEFAULT NULL,
  `modification_date` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`geonameid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOAD DATA LOCAL INFILE '/var/lib/mysql-files/AD.txt' INTO TABLE `geoname` (`geonameid`, `name`, `asciiname`, `alternatenames`, `latitude`, `longitude`, `feature_class`, `feature_code`, `country_code`, `cc2`, `admin1_code`, `admin2_code`, `admin3_code`, `admin4_code`, `population`, `elevation`, `dem`, `timezone`, `modification_date`);