--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/geonames-import/import-geonames.sql	Mon Nov 05 13:07:16 2018 +0100
@@ -0,0 +1,57 @@
+-- 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
+);
+
+COPY geonames_import_tmp FROM :'filename' WITH NULL '';
+
+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;