GeoNames-MySQL-DataImport/geonames_db_struct.sql at master · danygielow/GeoNames-MySQL-DataImport · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
-- DROP DATABASE IF EXISTS geonames;
-- CREATE DATABASE geonames DEFAULT CHARACTER SET utf8;
-- USE geonames;

CREATE TABLE geoname (
    geonameid int PRIMARY KEY,
    name varchar(200),
    asciiname varchar(200),
    alternatenames varchar(4000),
    latitude decimal(10,7),
    longitude decimal(10,7),
    fclass char(1),
    fcode varchar(10),
    country varchar(2),
    cc2 varchar(60),
    admin1 varchar(20),
    admin2 varchar(80),
    admin3 varchar(20),
    admin4 varchar(20),
    population int,
    elevation int,
    gtopo30 int,
    timezone varchar(40),
    moddate date
) CHARACTER SET utf8;


CREATE TABLE alternatename (
    alternatenameId int PRIMARY KEY,
    geonameid int,
    isoLanguage varchar(7),
    alternateName varchar(200),
    isPreferredName BOOLEAN,
    isShortName BOOLEAN,
    isColloquial BOOLEAN,
    isHistoric BOOLEAN
) CHARACTER SET utf8;


CREATE TABLE countryinfo (
    iso_alpha2 char(2),
    iso_alpha3 char(3),
    iso_numeric integer,
    fips_code varchar(3),
    name varchar(200),
    capital varchar(200),
    areainsqkm double,
    population integer,
    continent char(2),
    tld char(3),
    currency char(3),
    currencyName char(20),
    Phone char(10),
    postalCodeFormat varchar(100),
    postalCodeRegex varchar(255),
    geonameId int,
    languages varchar(200),
    neighbours char(100),
    equivalentFipsCode char(10)
) CHARACTER SET utf8;


CREATE TABLE iso_languagecodes(
    iso_639_3 CHAR(4),
    iso_639_2 VARCHAR(50),
    iso_639_1 VARCHAR(50),
    language_name VARCHAR(200)
) CHARACTER SET utf8;


CREATE TABLE admin1CodesAscii (
    code CHAR(6),
    name TEXT,
    nameAscii TEXT,
    geonameid int
) CHARACTER SET utf8;


CREATE TABLE admin2Codes (
    code CHAR(15),
    name TEXT,
    nameAscii TEXT,
    geonameid int
) CHARACTER SET utf8;


CREATE TABLE hierarchy (
    parentId int,
    childId int,
    type VARCHAR(50)
) CHARACTER SET utf8;


CREATE TABLE featureCodes (
    code CHAR(7),
    name VARCHAR(200),
    description TEXT
) CHARACTER SET utf8;


CREATE TABLE timeZones (
    timeZoneId VARCHAR(200),
    GMT_offset DECIMAL(3,1),
    DST_offset DECIMAL(3,1)
) CHARACTER SET utf8;


CREATE TABLE continentCodes (
    code CHAR(2),
    name VARCHAR(20),
    geonameid INT
) CHARACTER SET utf8;