Mercurial > gemma
annotate schema/geonames.sql @ 1819:99cf6da4c8c0
Removed all unique geohash indexes as they prevent updates of data.
What ever the intension of these indexes were, in the given form they
are wrong and prevent updating of data in an area, where old data
already exists.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Wed, 16 Jan 2019 10:36:22 +0100 |
parents | 2304778c4432 |
children |
rev | line source |
---|---|
1109
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
1 -- This is Free Software under GNU Affero General Public License v >= 3.0 |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
2 -- without warranty, see README.md and license for details. |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
3 |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
4 -- SPDX-License-Identifier: AGPL-3.0-or-later |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
5 -- License-Filename: LICENSES/AGPL-3.0.txt |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
6 |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
7 -- Copyright (C) 2018 by via donau |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
9 -- Software engineering by Intevation GmbH |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
10 |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
11 -- Author(s): |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
12 -- * Sascha Wilde <wilde@intevation.de> |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
13 |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
14 -- Tables for data from geonames database |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
15 -- https://download.geonames.org/ |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
16 -- used for searching cities and villages... |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
17 |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
18 CREATE TABLE waterway.geonames ( |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
19 geonameid int PRIMARY KEY, |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
20 name varchar(200) NOT NULL, |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
21 asciiname varchar(200), |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
22 alternatenames varchar(10000), |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
23 location geography(POINT, 4326) NOT NULL, |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
24 feature_class char(1), |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
25 feature_code varchar(10), |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
26 country_code char(2), |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
27 cc2 varchar(200), |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
28 admin1_code varchar(20), |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
29 admin2_code varchar(80), |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
30 admin3_code varchar(20), |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
31 admin4_code varchar(20), |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
32 population int, |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
33 elevation int, |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
34 dem int, |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
35 timezone varchar(40), |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
36 modification_date timestamp |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
37 ); |