Mercurial > gemma

annotate schema/geonames-import/import-geonames.sql @ 5666:37c2354a6024 clickable-links

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Render links only to known bottlenecks
author Thomas Junk <thomas.junk@intevation.de>
date Tue, 05 Dec 2023 15:34:31 +0100
parents a26d2afe0c01
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
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
15 CREATE TEMP TABLE geonames_import_tmp (
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
16 geonameid int PRIMARY KEY,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
17 name varchar(200) NOT NULL,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
18 asciiname varchar(200),
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
19 alternatenames varchar(10000),
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
20 lat float NOT NULL,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
21 lon float NOT NULL,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
22 feature_class char(1),
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
23 feature_code varchar(10),
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
24 country_code char(2),
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
25 cc2 varchar(200),
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
26 admin1_code varchar(20),
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
27 admin2_code varchar(80),
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
28 admin3_code varchar(20),
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
29 admin4_code varchar(20),
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
30 population text,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
31 elevation text,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
32 dem text,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
33 timezone varchar(40),
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
34 modification_date timestamp
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
35 );
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
36
5663
a26d2afe0c01 Fixed access rights shenanigans with geonames import.
Sascha Wilde <wilde@sha-bang.de>
parents: 1109
diff changeset
37 \set cpycmd '\\copy geonames_import_tmp FROM ' :'filename' ' WITH NULL \'\';'
a26d2afe0c01 Fixed access rights shenanigans with geonames import.
Sascha Wilde <wilde@sha-bang.de>
parents: 1109
diff changeset
38 :cpycmd
1109
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
39
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
40 INSERT INTO waterway.geonames
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
41 SELECT geonameid,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
42 name,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
43 asciiname,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
44 alternatenames,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
45 ST_MakePoint(lon,lat),
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
46 feature_class,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
47 feature_code,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
48 country_code,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
49 cc2,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
50 admin1_code,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
51 admin2_code,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
52 admin3_code,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
53 admin4_code,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
54 population::int,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
55 elevation::int,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
56 dem::int,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
57 timezone,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
58 modification_date FROM geonames_import_tmp;