地理数据库GeoNames，并导入Mysql

由于最近实验室的工作需要用到全世界的地名数据库，但是呢，从网上的各种结果来看，都是些相对不太完整 或者 导出格式不太方便的表格。因此这里先给出本文的主角——Geonames数据库。该数据库含有经纬度，所属国家及相应的人口密度，可以说是免费的数据库里最为详细的了，所以强烈推荐有需要的同学拿走哦：D

GeoNames数据库

首先贴上百度百科的的介绍：

GeoNames是一个免费的全球地理数据库。
GeoNames的目标是把各种来源的免费数据进行集成并制作成一个数据库或一系列的Web服务。

GeoNames地名辞典包含了1100万个地点将近200种语言的1100万个地名和200万种别名。地理信息还详细到坐标、行政区划、邮政编码、人口、海拔和时区。GeoNames的数据收集自（美国）国家测绘机构、国家统计署、国家邮政局，还有美国陆军。

如何下载并利用这些数据?

首先贴上GeoName数据库的官网：http://www.geonames.org/

然后进入主页，找到相应的下载位置：

可以看到这里也支持直接的web查询，不过这不是我们的重点。我们直接对其数据进行下载并导入mysql数据库。

点击Free Gazetteer Data,进入下载页面：
http://download.geonames.org/export/dump/

这里有好几种文件的名称，前面缩写的如，CN.zip就是中国地区的地名。不过这里演示的是总的，含有全球地名的zip——allCountries.zip，就是大小最大的文件。

然后当我们解压完该文件时竟然惊奇的发现，哇！居然是超过1个G的txt，甚至无法直接打开（汗），看来只有导入数据库进行使用了。

导入Mysql数据库

首先要导入数据库，就得知道如何创建相应的表支持其txt里各项值。当然这一点GeoNames官网肯定会有相应的介绍。
其实就在刚刚下载页面的下方，就有相应的文字说明：
多余的部分我就不贴上来了，这里就放上关于allCountries中的说明：

说实话导入数据库我花了很长的时间，这里面也有非常的多的坑，在网上不停的查询，论坛提问最后我才找到了一个亲试可行的办法。
这里直接贴上Mysql语句：

创建表

CREATE TABLE AllCountries (
    geonameid MEDIUMINT UNSIGNED NOT NULL  COMMENT "integer id of record in geonames database",
    name     VARCHAR(200) CHARACTER SET utf8mb4  COMMENT "name of geographical point (utf8) varchar(200)",
    asciiname  VARCHAR(200)    COMMENT "name of geographical point in plain ascii characters, varchar(200)",
    alternatenames TEXT CHARACTER SET utf8mb4   COMMENT "alternatenames, comma separated, ascii names automatically transliterated, convenience attribute from alternatename table, varchar(10000)",
    latitude   DECIMAL(7,5)    COMMENT "latitude in decimal degrees (wgs84)",
    longitude  DECIMAL(8,5)    COMMENT "longitude in decimal degrees (wgs84)",
    feature_class CHAR(1)      COMMENT "see http://www.geonames.org/export/codes.html, char(1)",
    feature_code  VARCHAR(10)  COMMENT "see http://www.geonames.org/export/codes.html, varchar(10)",
    country_code  CHAR(2)      COMMENT "ISO-3166 2-letter country code, 2 characters",
    cc2          VARCHAR(200)  COMMENT "alternate country codes, comma separated, ISO-3166 2-letter country code, 200 characters",
    admin1_code  VARCHAR(20)   COMMENT "fipscode (subject to change to iso code), see exceptions below, see file admin1Codes.txt for display names of this code; varchar(20)",
    admin2_code  VARCHAR(80)   COMMENT "code for the second administrative division, a county in the US, see file admin2Codes.txt; varchar(80) ",
    admin3_code  VARCHAR(20)   COMMENT "code for third level administrative division, varchar(20)",
    admin4_code  VARCHAR(20)   COMMENT "code for fourth level administrative division, varchar(20)",
    population  DECIMAL(11,0)  COMMENT "bigint (8 byte int) ",
    elevation  SMALLINT UNSIGNED  COMMENT "in meters, integer",
    dem        SMALLINT UNSIGNED  COMMENT "digital elevation model, srtm3 or gtopo30, average elevation of 3''x3'' (ca 90mx90m) or 30''x30'' (ca 900mx900m) area in meters, integer. srtm processed by cgiar/ciat.",
    timezone   VARCHAR(40)     COMMENT "the timezone id (see file timeZone.txt) varchar(40)",
    modification_date DATE     COMMENT "date of last modification in yyyy-MM-dd format",
    PRIMARY KEY(geonameid)
) ENGINE=InnoDB DEFAULT CHARACTER SET ascii COMMENT 'http://download.geonames.org/export/dump/';

导入allCountries.txt

LOAD DATA INFILE 'C:\\Users\\Administrator\\Desktop\\allcountries.txt'
    INTO TABLE allcountries
    CHARACTER SET utf8mb4
    FIELDS TERMINATED BY "\t"
    LINES TERMINATED BY '\n';

然后就可以静候导入成功啦！（超过40分钟的导入过程）

可以看到这里的行数超过1200万条！！！

这里贴上原论坛大神的链接：
https://dba.stackexchange.com/questions/145080/import-geonames-allcountries-txt-into-mysql-5-7-using-load-infile-error-1300

TXT文本转表格

最后贴上一个python代码，如果有想将国家地名的txt转为表格形式的可以试试下面的代码：

from openpyxl import Workbook
def txt2xls(filename, xlsname):
    """
    :文本转换成xls的函数
    :param filename txt文本文件名称、
    :param xlsname 表示转换后的excel文件名
    """
    print(filename)
    try:
        f = open(filename, encoding='utf-8')
        xls = Workbook()
        # 生成excel的方法，声明excel
        sheet = xls.get_sheet_by_name('Sheet')  # 通过表名获取
        x = 1
        while True:
            # 按行循环，读取文本文件
            line = f.readline()
            if not line:
                break  # 如果没有内容，则退出循环
            for i in range(len(line.split('\t'))):
                item = line.split('\t')[i]
                sheet.cell(x,i+1).value = item 
            x += 1  # excel另起一行
        f.close()
        xls.save(xlsname)  # 保存xls文件
    except:
        raise

需要提醒的是US.zip即美国的地名超过excel最大行数1048576，所以往往一个表格不够用（美国地名超过两百万），所以可以单独试试下面的代码：

from openpyxl import Workbook
def US2xls(filename, xlsname):
    """
    :文本转换成xls的函数
    :param filename txt文本文件名称、
    :param xlsname 表示转换后的excel文件名
    """
    print(filename)
    num = 1
    try:
        f = open(filename, encoding='utf-8')
        xls = Workbook()
        # 生成excel的方法，声明excel
        sheet = xls.get_sheet_by_name('Sheet')  # 通过表名获取
        x = 1
        while True:
            # 按行循环，读取文本文件
            line = f.readline()
            if not line:
                break  # 如果没有内容，则退出循环
            for i in range(len(line.split('\t'))):
                item = line.split('\t')[i]
                sheet.cell(x,i+1).value = item
            x += 1  # excel另起一行
            if x== 1048576:
                xls.save(xlsname)  # 保存xls文件
                xls = Workbook()# 创建一个新的excel文件
                sheet = xls.get_sheet_by_name('Sheet')  # 通过表名获取
                xlsname = str(xlsname).replace('.xls', str(num)+'.xls')# 更新保存的路径
                num += 1
                x = 1# 重置行数
        f.close()
        xls.save(xlsname)  # 保存xls文件
    except:
        raise

写在最后：
因为本身找到这个数据库并将其导入Mysql中确实不容易花费至少一个星期的时间，所以最后成功导入后就像把帖子分享出来，本身Geonames数据库很全，而且还是免费（划重点），但是呢查出来的方法往往都是很早以前的版本，而且大部分都是英语的网页，所以就特别想把这个分享出来，给需要的人尤其是需要这方面数据的朋友。