-- This is Free Software under GNU Affero General Public License v >= 3.0
-- without warranty, see README.md and license for details.

-- SPDX-License-Identifier: AGPL-3.0-or-later
-- License-Filename: LICENSES/AGPL-3.0.txt

-- Copyright (C) 2018 by via donau
--   – Österreichische Wasserstraßen-Gesellschaft mbH
-- Software engineering by Intevation GmbH

-- Author(s):
--  * Sascha Wilde <wilde@intevation.de>


CREATE TEMP TABLE geonames_import_tmp (
       geonameid int PRIMARY KEY,
       name varchar(200) NOT NULL,
       asciiname varchar(200),
       alternatenames varchar(10000),
       lat float NOT NULL,
       lon float NOT NULL,
       feature_class char(1),
       feature_code varchar(10),
       country_code char(2),
       cc2 varchar(200),
       admin1_code varchar(20),
       admin2_code varchar(80),
       admin3_code varchar(20),
       admin4_code varchar(20),
       population text,
       elevation text,
       dem text,
       timezone varchar(40),
       modification_date timestamp
);

\set cpycmd '\\copy geonames_import_tmp FROM ' :'filename' ' WITH NULL \'\';'
:cpycmd

INSERT INTO waterway.geonames
  SELECT geonameid,
       name,
       asciiname,
       alternatenames,
       ST_MakePoint(lon,lat),
       feature_class,
       feature_code,
       country_code,
       cc2,
       admin1_code,
       admin2_code,
       admin3_code,
       admin4_code,
       population::int,
       elevation::int,
       dem::int,
       timezone,
       modification_date FROM geonames_import_tmp;