Mercurial > gemma
annotate schema/geonames-import/import-geonames.sql @ 4072:1eb39e9e8ec2 historization_ng
Adapted fwa-import to new historization model.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Thu, 25 Jul 2019 14:48:38 +0200 |
parents | 74a75a5ce770 |
children | a26d2afe0c01 |
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; |