Mercurial > gemma

comparison schema/geonames-import/import-geonames.sql @ 1109:74a75a5ce770

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added geonames data and extended search for villages/cities. The free data from geonames https://download.geonames.org/ is imported into the gemma data base (for the relevant states) and used to search for cities and villages. This data might be replaced by data from the RIS-index later on..?
author Sascha Wilde <wilde@intevation.de>
date Mon, 05 Nov 2018 13:07:16 +0100
parents
children a26d2afe0c01
comparison
equal deleted inserted replaced
1108:5adee8b0e29f 1109:74a75a5ce770
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
2 -- without warranty, see README.md and license for details.
3
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
5 -- License-Filename: LICENSES/AGPL-3.0.txt
6
7 -- Copyright (C) 2018 by via donau
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
9 -- Software engineering by Intevation GmbH
10
11 -- Author(s):
12 -- * Sascha Wilde <wilde@intevation.de>
13
14
15 CREATE TEMP TABLE geonames_import_tmp (
16 geonameid int PRIMARY KEY,
17 name varchar(200) NOT NULL,
18 asciiname varchar(200),
19 alternatenames varchar(10000),
20 lat float NOT NULL,
21 lon float NOT NULL,
22 feature_class char(1),
23 feature_code varchar(10),
24 country_code char(2),
25 cc2 varchar(200),
26 admin1_code varchar(20),
27 admin2_code varchar(80),
28 admin3_code varchar(20),
29 admin4_code varchar(20),
30 population text,
31 elevation text,
32 dem text,
33 timezone varchar(40),
34 modification_date timestamp
35 );
36
37 COPY geonames_import_tmp FROM :'filename' WITH NULL '';
38
39 INSERT INTO waterway.geonames
40 SELECT geonameid,
41 name,
42 asciiname,
43 alternatenames,
44 ST_MakePoint(lon,lat),
45 feature_class,
46 feature_code,
47 country_code,
48 cc2,
49 admin1_code,
50 admin2_code,
51 admin3_code,
52 admin4_code,
53 population::int,
54 elevation::int,
55 dem::int,
56 timezone,
57 modification_date FROM geonames_import_tmp;