Mercurial > gemma

annotate schema/geonames.sql @ 4606:dfe9cde6a20c geoserver_sql_views

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Reflect database model changes for SQL views in backend In principle, we could use many datasources with different database schemas, but this would imply changing GeoServer initialization, service filtering, endpoints and eventually more. Since we do not need it, just hard-code the schema name as a constant.
author Tom Gottfried <tom@intevation.de>
date Thu, 05 Sep 2019 12:23:31 +0200
parents 2304778c4432
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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 );