Mercurial > gemma

annotate schema/geonames-import/import-geonames.sql @ 2455:54c9fe587fe6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Subdivide SQL function to prepare for improved error handling The context of an error (e.g. the function in which it occured) can be inferred by the database client. Not doing all in one statement will render the context more meaningful.
author Tom Gottfried <tom@intevation.de>
date Fri, 01 Mar 2019 18:38:02 +0100
parents 74a75a5ce770
children a26d2afe0c01
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
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
37 COPY geonames_import_tmp FROM :'filename' WITH NULL '';
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
38
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
39 INSERT INTO waterway.geonames
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
40 SELECT geonameid,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
41 name,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
42 asciiname,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
43 alternatenames,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
44 ST_MakePoint(lon,lat),
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
45 feature_class,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
46 feature_code,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
47 country_code,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
48 cc2,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
49 admin1_code,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
50 admin2_code,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
51 admin3_code,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
52 admin4_code,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
53 population::int,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
54 elevation::int,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
55 dem::int,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
56 timezone,
74a75a5ce770 Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
57 modification_date FROM geonames_import_tmp;