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

Материал из Artem Aleksashkin's Wiki
Перейти к навигации Перейти к поиску

GeoNames

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` CHARACTER SET utf8 COLUMNS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`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`);


LOAD DATA LOCAL INFILE '/var/lib/mysql-files/US.txt' IGNORE INTO TABLE `locations`  CHARACTER SET utf8mb4 COLUMNS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`id`, `name`, `ascii_name`, `names`, `latitude`, `longitude`, `feature_class`, `feature_code`, `country_code`, `cc2`, `admin1_code`, `admin2_code`, `admin3_code`, `admin4_code`, `population`, `elevation`, `dem`, `timezone`, `modification_date`);

GeoLite2 Free Geolocation Data

КЛАДР

OpenFlights

BIN/IIN banks

Источник — http://wiki.aleksashkin.net/index.php?title=Открытые_базы_данных&oldid=2856