changeset 3951:89ada0b9083f

Modified the schema for DGMs; added converter for CSV to SQL for DGM information. flys-backend/trunk@5604 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Thu, 27 Sep 2012 07:24:07 +0000
parents 82e931f88137
children 5d8db3349b77
files flys-backend/ChangeLog flys-backend/doc/schema/import-dems.sql flys-backend/doc/schema/oracle-spatial.sql flys-backend/doc/schema/postgresql-spatial.sql flys-backend/pom.xml flys-backend/src/main/java/de/intevation/flys/utils/DgmSqlConverter.java
diffstat 6 files changed, 549 insertions(+), 170 deletions(-) [+]
line wrap: on
line diff
--- a/flys-backend/ChangeLog	Mon Sep 24 10:02:36 2012 +0000
+++ b/flys-backend/ChangeLog	Thu Sep 27 07:24:07 2012 +0000
@@ -1,3 +1,16 @@
+2012-09-27  Ingo Weinzierl <ingo@intevation.de>
+
+	* doc/schema/postgresql-spatial.sql:
+
+	* doc/schema/import-dems.sql: Added more information to fullfil the schema
+	  for dems.
+
+	* src/main/java/de/intevation/flys/utils/DgmSqlConverter.java: New converter
+	  for CSV files with DGM information; results in a SQL file with INSERT
+	  statements.
+
+	* pom.xml: Added dependency to OpenCSV for reading CSV files.
+
 2012-09-24  Ingo Weinzierl <ingo@intevation.de>
 
 	* contrib/shpimporter/importer.py: Fixed method name for ERROR log
--- a/flys-backend/doc/schema/import-dems.sql	Mon Sep 24 10:02:36 2012 +0000
+++ b/flys-backend/doc/schema/import-dems.sql	Thu Sep 27 07:24:07 2012 +0000
@@ -1,165 +1,24 @@
--- SAAR
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Saar'),
-    0,
-    7.9,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Saar/Geodaesie/Hoehenmodelle/km0000-0079_long.txt'
-);
-
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Saar'),
-    8.0,
-    20.4,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Saar/Geodaesie/Hoehenmodelle/km0080-0204_long.txt'
-);
-
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Saar'),
-    20.5,
-    31.4,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Saar/Geodaesie/Hoehenmodelle/km0205-0314_long.txt'
-);
-
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Saar'),
-    31.5,
-    54.1,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Saar/Geodaesie/Hoehenmodelle/km0315-0541_long.txt'
-);
-
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Saar'),
-    54.2,
-    65.5,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Saar/Geodaesie/Hoehenmodelle/km0542-0655_long.txt'
-);
-
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Saar'),
-    65.6,
-    82.8,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Saar/Geodaesie/Hoehenmodelle/km0656-0828_long.txt'
-);
-
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Saar'),
-    82.9,
-    93.1,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Saar/Geodaesie/Hoehenmodelle/km0829-0931_erweitert.txt'
-);
-
-
--- ELBE
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Elbe'),
-    0.0,
-    101.1,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Elbe/Geodaesie/Hoehenmodelle/m_00000_10110.grd'
-);
-
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Elbe'),
-    99.2,
-    203.0,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Elbe/Geodaesie/Hoehenmodelle/m_09920_20300.grd'
-);
-
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Elbe'),
-    202.0,
-    299.8,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Elbe/Geodaesie/Hoehenmodelle/m_20200_29980.grd'
-);
-
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Elbe'),
-    298.1,
-    401.0,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Elbe/Geodaesie/Hoehenmodelle/m_29810_40100.grd'
-);
-
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Elbe'),
-    400.0,
-    500.9,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Elbe/Geodaesie/Hoehenmodelle/m_40000_50090.grd'
-);
-
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Elbe'),
-    500.1,
-    583.3,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Elbe/Geodaesie/Hoehenmodelle/m_50010_58330.grd'
-);
-
-
--- MOSEL
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Mosel'),
-    0.0,
-    5.8,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Mosel/Geodaesie/Hoehenmodelle/DGMW-ASCII/0000-0580.xyz'
-);
-
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Mosel'),
-    5.8,
-    15.3,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Mosel/Geodaesie/Hoehenmodelle/DGMW-ASCII/0058-0153.xyz'
-);
-
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Mosel'),
-    15.3,
-    41.6,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Mosel/Geodaesie/Hoehenmodelle/DGMW-ASCII/0153-0416.xyz'
-);
-
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Mosel'),
-    41.4,
-    101.2,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Mosel/Geodaesie/Hoehenmodelle/DGMW-ASCII/0414-1012O.xyz'
-);
-
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Mosel'),
-    41.4,
-    101.21,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Mosel/Geodaesie/Hoehenmodelle/DGMW-ASCII/0414-1012W.xyz'
-);
-
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Mosel'),
-    101.2,
-    148.8,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Mosel/Geodaesie/Hoehenmodelle/DGMW-ASCII/1012-1488.xyz'
-);
-
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Mosel'),
-    148.8,
-    166.6,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Mosel/Geodaesie/Hoehenmodelle/DGMW-ASCII/1488-1666.xyz'
-);
-
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Mosel'),
-    166.6,
-    196.0,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Mosel/Geodaesie/Hoehenmodelle/DGMW-ASCII/1666-1960.xyz'
-);
-
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Mosel'),
-    196.0,
-    204.4,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Mosel/Geodaesie/Hoehenmodelle/DGMW-ASCII/1960-2044.XYZ'
-);
-
-INSERT INTO dem (river_id, lower, upper, path) VALUES (
-    (SELECT id FROM rivers WHERE name = 'Mosel'),
-    204.4,
-    218.4,
-    '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Mosel/Geodaesie/Hoehenmodelle/DGMW-ASCII/2044-2184.XYZ'
-);
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Elbe'), 'GRD_00000_01010', 0.0, 101.0, 2003, 2007, 'GK-3', 'DHHN92', 'ESRI-Grid', false, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Elbe/Geodaesie/Hoehenmodelle/m_00000_10110.grd');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Elbe'), 'GRD_00992_02030', 99.0, 203.0, 2003, 2007, 'GK-3', 'DHHN92', 'ESRI-Grid', false, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Elbe/Geodaesie/Hoehenmodelle/m_09920_20300.grd');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Elbe'), 'GRD_02020_02998', 202.0, 300.0, 2003, 2007, 'GK-3', 'DHHN92', 'ESRI-Grid', false, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Elbe/Geodaesie/Hoehenmodelle/m_20200_29980.grd');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Elbe'), 'GRD_02981_04010', 298.0, 401.0, 2003, 2007, 'GK-3', 'DHHN92', 'ESRI-Grid', false, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Elbe/Geodaesie/Hoehenmodelle/m_29810_40100.grd');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Elbe'), 'GRD_04000_05009', 400.0, 501.0, 2003, 2007, 'GK-3', 'DHHN92', 'ESRI-Grid', false, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Elbe/Geodaesie/Hoehenmodelle/m_40000_50090.grd');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Elbe'), 'GRD_05001_05830', 500.0, 583.0, 2003, 2007, 'GK-3', 'DHHN92', 'ESRI-Grid', false, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Elbe/Geodaesie/Hoehenmodelle/m_50010_58330.grd');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Mosel'), 'GRD_00000_00058', 0.0, 6.0, null, null, 'GK-2', 'DHHN85', 'ASCII-Grid', false, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Mosel/Geodaesie/Hoehenmodelle/DGMW-ASCII/0000-0580.xyz');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Mosel'), 'GRD_00058_00153', 6.0, 15.0, null, null, 'GK-2', 'DHHN85', 'ASCII-Grid', false, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Mosel/Geodaesie/Hoehenmodelle/DGMW-ASCII/0058-0153.xyz');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Mosel'), 'GRD_00153_00416', 15.0, 42.0, null, null, 'GK-2', 'DHHN85', 'ASCII-Grid', false, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Mosel/Geodaesie/Hoehenmodelle/DGMW-ASCII/0153-0416.xyz');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Mosel'), 'GRD_00414_01012_O', 41.0, 101.0, null, null, 'GK-2', 'DHHN85', 'ASCII-Grid', false, '2', 'muss ├╝berarbeitet werden', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Mosel/Geodaesie/Hoehenmodelle/DGMW-ASCII/0414-1012O.xyz');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Mosel'), 'GRD_00414_01012_W', 41.0, 101.0, null, null, 'GK-2', 'DHHN85', 'ASCII-Grid', false, '2', 'muss ├╝berarbeitet werden', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Mosel/Geodaesie/Hoehenmodelle/DGMW-ASCII/0414-1012W.xyz');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Mosel'), 'GRD_01012_01488', 101.0, 145.0, null, null, 'GK-2', 'DHHN85', 'ASCII-Grid', false, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Mosel/Geodaesie/Hoehenmodelle/DGMW-ASCII/1012-1488.xyz');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Mosel'), 'GRD_01488_01666', 145.0, 167.0, null, null, 'GK-2', 'DHHN85', 'ASCII-Grid', false, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Mosel/Geodaesie/Hoehenmodelle/DGMW-ASCII/1488-1666.xyz');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Mosel'), 'GRD_01666_01960', 167.0, 196.0, null, null, 'GK-2', 'DHHN85', 'ASCII-Grid', false, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Mosel/Geodaesie/Hoehenmodelle/DGMW-ASCII/1666-1960.xyz');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Mosel'), 'GRD_01960_02044', 196.0, 204.0, null, null, 'GK-2', 'DHHN85', 'ASCII-Grid', false, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Mosel/Geodaesie/Hoehenmodelle/DGMW-ASCII/1960-2044.XYZ');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Mosel'), 'GRD_02044_02184', 204.0, 218.0, null, null, 'GK-2', 'DHHN85', 'ASCII-Grid', false, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Mosel/Geodaesie/Hoehenmodelle/DGMW-ASCII/2044-2184.XYZ');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Mosel'), 'GRD_02184_02420', 218.0, 242.0, null, null, 'GK-2', 'DHHN85', 'ASCII-Grid', false, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Mosel/Geodaesie/Hoehenmodelle/DGMW-ASCII/525480MO.XYZ');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Saar'), 'GRD_00000_00079', 0.0, 8.0, 1999, 2002, 'GK-2', '', 'ASCII-Grid', true, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Saar/Geodaesie/Hoehenmodelle/km0000-0079_long.txt');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Saar'), 'GRD_00080_00204', 8.0, 20.0, 1999, 2002, 'GK-2', '', 'ASCII-Grid', true, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Saar/Geodaesie/Hoehenmodelle/km0080-0204_long.txt');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Saar'), 'GRD_00205_00314', 20.0, 31.0, 1999, 2002, 'GK-2', '', 'ASCII-Grid', true, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Saar/Geodaesie/Hoehenmodelle/km0205-0314_long.txt');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Saar'), 'GRD_00315_00541', 31.0, 54.0, 1999, 2002, 'GK-2', '', 'ASCII-Grid', true, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Saar/Geodaesie/Hoehenmodelle/km0315-0541_long.txt');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Saar'), 'GRD_00542_00655', 54.0, 65.0, 1999, 2002, 'GK-2', '', 'ASCII-Grid', true, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Saar/Geodaesie/Hoehenmodelle/km0542-0655_long.txt');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Saar'), 'GRD_00656_00828', 65.0, 83.0, 1999, 2002, 'GK-2', '', 'ASCII-Grid', true, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Saar/Geodaesie/Hoehenmodelle/km0656-0828_long.txt');
+INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,projection, elevation_state, format, border_break, resolution, description, path) VALUES ((SELECT id from rivers WHERE name = 'Saar'), 'GRD_00829_00931', 83.0, 93.0, 1999, 2002, 'GK-2', '', 'ASCII-Grid', true, '2', '', '/vol1/projects/Geospatial/flys-3.0/testdaten/Gewaesser/Saar/Geodaesie/Hoehenmodelle/km0829-0931_erweitert.txt');
--- a/flys-backend/doc/schema/oracle-spatial.sql	Mon Sep 24 10:02:36 2012 +0000
+++ b/flys-backend/doc/schema/oracle-spatial.sql	Thu Sep 27 07:24:07 2012 +0000
@@ -155,10 +155,18 @@
     ID NUMBER PRIMARY KEY NOT NULL,
     river_id NUMBER(38),
     -- XXX Should we use the ranges table instead?
-    lower    NUMBER(19,5),
-    upper    NUMBER(19,5),
-    path     VARCHAR(256),
-    UNIQUE (river_id, lower, upper)
+    name            VARCHAR(64),
+    lower           NUMBER(19,5),
+    upper           NUMBER(19,5),
+    year_from       NUMBER(38),
+    year_to         NUMBER(38),
+    projection      VARCHAR(32),
+    elevation_state VARCHAR(32),
+    format          VARCHAR(32),
+    border_break    BOOLEAN NOT NULL DEFAULT FALSE,
+    resolution      VARCHAR(16),
+    description     VARCHAR(256),
+    path            VARCHAR(256)
 );
 CREATE OR REPLACE TRIGGER dem_trigger BEFORE INSERT ON dem FOR each ROW
     BEGIN
--- a/flys-backend/doc/schema/postgresql-spatial.sql	Mon Sep 24 10:02:36 2012 +0000
+++ b/flys-backend/doc/schema/postgresql-spatial.sql	Thu Sep 27 07:24:07 2012 +0000
@@ -104,10 +104,18 @@
     id       int PRIMARY KEY NOT NULL,
     river_id int REFERENCES rivers(id),
     -- XXX Should we use the ranges table instead?
-    lower    NUMERIC,
-    upper    NUMERIC,
-    path     VARCHAR(256),
-    UNIQUE (river_id, lower, upper)
+    name            VARCHAR(64),
+    lower           NUMERIC,
+    upper           NUMERIC,
+    year_from       int,
+    year_to         int,
+    projection      VARCHAR(32),
+    elevation_state VARCHAR(32),
+    format          VARCHAR(32),
+    border_break    BOOLEAN NOT NULL DEFAULT FALSE,
+    resolution      VARCHAR(16),
+    description     VARCHAR(256),
+    path            VARCHAR(256)
 );
 ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ');
 
--- a/flys-backend/pom.xml	Mon Sep 24 10:02:36 2012 +0000
+++ b/flys-backend/pom.xml	Thu Sep 27 07:24:07 2012 +0000
@@ -53,6 +53,11 @@
       <scope>test</scope>
     </dependency>
     <dependency>
+      <groupId>net.sf.opencsv</groupId>
+      <artifactId>opencsv</artifactId>
+      <version>2.0</version>
+    </dependency>
+    <dependency>
       <groupId>org.hibernate</groupId>
       <artifactId>hibernate-core</artifactId>
       <version>3.6.5.Final</version>
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/flys-backend/src/main/java/de/intevation/flys/utils/DgmSqlConverter.java	Thu Sep 27 07:24:07 2012 +0000
@@ -0,0 +1,483 @@
+package de.intevation.flys.utils;
+
+import java.io.BufferedInputStream;
+import java.io.BufferedWriter;
+import java.io.File;
+import java.io.FileInputStream;
+import java.io.FileNotFoundException;
+import java.io.FileWriter;
+import java.io.IOException;
+import java.io.InputStream;
+import java.io.InputStreamReader;
+import java.io.Reader;
+import java.util.ArrayList;
+import java.util.List;
+
+import au.com.bytecode.opencsv.CSVReader;
+
+
+/**
+ * A converter for CSV files with DGM information. The result of a conversion
+ * is an SQL file with "INSERT INTO dem ..." statements.
+ * <br>
+ * To start the converter, at least the following three system properties are
+ * required:
+ * <br>
+ * <ul>
+ * <ol><b>gew.dir</b>: This property must point to the directory where all
+ * rivers are stored.</ol>
+ * <ol><b>csv</b>: This property must point to the CSV file that contains the
+ * DGM information.</ol>
+ * <ol><b>sql</b>: This property must point to a (not yet existing) file that
+ * will be generated by this converter.</ol>
+ * </ul>
+ * <br>
+ * In addiation, the following properties are accepted to modify log messages,
+ * etc.
+ * <ul>
+ * <ol><b>verbose</b>: Accepts integer values (0, 1, 2, 3) to modify the log
+ * messages. The higher the value the more log messages are printed to STDOUT.
+ * </ol>
+ * <ol><b>full</b>: Accepts true|false values. If true is set, all rivers
+ * included in the CSV file are taken into account while parsing. Otherwise,
+ * the converter reads information for 'Saar', 'Mosel' and 'Eble' only.</ol> 
+ * </ul>
+ * 
+ * @author Ingo Weinzierl <a href="mailto:ingo.weinzierl@intevation.de">
+ * ingo.weinzierl@intevation.de</a>
+ *
+ */
+public class DgmSqlConverter {
+
+    public static final String SQL_INSERT = "INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,"
+        + "projection, elevation_state, format, border_break, resolution, description, path) VALUES ("
+        + "%s, '%s', %s, %s, %s, %s, '%s', '%s', '%s', %s, '%s', '%s', '%s');";
+
+    public static final String SQL_SELECT_RIVER = "(SELECT id from rivers WHERE name = '%s')";
+
+    public static final char DEFAULT_SEPERATOR = ',';
+    public static final char DEFAULT_QUOTE = '"';
+    public static final int DEFAULT_LOG_LEVEL = 2;
+
+    public static final boolean FULL_MODE = Boolean.getBoolean("full");
+    public static final String GEW_DIR = System.getProperty("gew.dir", null);
+    public static final String CSV_FILE = System.getProperty("csv");
+    public static final String SQL_FILE = System.getProperty("sql");
+    public static final int LOG_LEVEL = Integer.getInteger("verbose",
+        DEFAULT_LOG_LEVEL);
+
+    public static final int MIN_COLUMN_COUNT = 15;
+
+    public static final int IDX_RIVERNAME = 0;
+    public static final int IDX_NAME = 12;
+    public static final int IDX_LOWER = 1;
+    public static final int IDX_UPPER = 2;
+    public static final int IDX_YEAR_FROM = 3;
+    public static final int IDX_YEAR_TO = 4;
+    public static final int IDX_PROJECTION = 7;
+    public static final int IDX_ELEVATION_STATE = 8;
+    public static final int IDX_FORMAT = 9;
+    public static final int IDX_BORDER_BREAK = 10;
+    public static final int IDX_RESOLUTION = 11;
+    public static final int IDX_DESCRIPTION = 14;
+    public static final int IDX_FILE_NAME = 5;
+    public static final int IDX_FILE_PATH = 6;
+
+    private class DGM {
+
+        public String river;
+        public String name;
+        public String projection;
+        public String elevationState;
+        public String format;
+        public String resolution;
+        public String description;
+        public String path;
+
+        public double lower;
+        public double upper;
+        public Integer yearFrom;
+        public Integer yearTo;
+
+        public boolean borderBreak;
+
+        public DGM() {
+            borderBreak = false;
+        }
+
+        public String toSQL() {
+            String riverId = String.format(SQL_SELECT_RIVER, river);
+            String lower = String.valueOf(this.lower);
+            String upper = String.valueOf(this.upper);
+            String yearFrom = this.yearFrom != null ? String
+                .valueOf(this.yearFrom) : "null";
+            String yearTo = this.yearTo != null ? String.valueOf(this.yearTo)
+                : "null";
+
+            return String.format(SQL_INSERT, riverId, name, lower, upper,
+                yearFrom, yearTo, projection, elevationState, format,
+                borderBreak, resolution, description, path);
+        }
+    }
+
+    private File riverDir;
+    private File csv;
+    private File sql;
+
+    private List<DGM> dgms;
+
+    public static void debug(String msg) {
+        if (LOG_LEVEL >= 3) {
+            System.out.println("DEBUG: " + msg);
+        }
+    }
+
+    public static void info(String msg) {
+        if (LOG_LEVEL >= 2) {
+            System.out.println("INFO: " + msg);
+        }
+    }
+
+    public static void warn(String msg) {
+        if (LOG_LEVEL >= 1) {
+            System.out.println("WARN: " + msg);
+        }
+    }
+
+    public static void error(String msg) {
+        System.out.println("ERROR: " + msg);
+    }
+
+    public static File getRiverDir(String[] args) {
+        if (GEW_DIR != null && GEW_DIR.length() > 0) {
+            return new File(GEW_DIR);
+        }
+        else if (args != null && args.length > 0) {
+            return new File(args[0]);
+        }
+
+        return null;
+    }
+
+    public static File getCSVFile(String[] args) {
+        if (CSV_FILE != null && CSV_FILE.length() > 0) {
+            return new File(CSV_FILE);
+        }
+        else if (args != null && args.length > 1) {
+            return new File(args[1]);
+        }
+
+        return null;
+    }
+
+    public static File getSQLFile(String[] args) {
+        if (SQL_FILE != null && SQL_FILE.length() > 0) {
+            return new File(SQL_FILE);
+        }
+        else if (args != null && args.length > 2) {
+            return new File(args[2]);
+        }
+
+        return null;
+    }
+
+    public static void main(String[] args) {
+        info("Start convering CSV -> SQL statements");
+
+        if (!FULL_MODE) {
+            info("You are running in DEMO mode; other rivers than 'Saar', 'Mosel' and 'Elbe' are ignored.");
+        }
+
+        File riverDir = getRiverDir(args);
+
+        if (riverDir == null) {
+            warn("No rivers directory specified!");
+            return;
+        }
+        else if (!riverDir.isDirectory()) {
+            warn("Specified rivers directory is not a directory!");
+            return;
+        }
+        else if (!riverDir.canRead()) {
+            warn("Unable to read '" + riverDir.toString() + "'");
+            return;
+        }
+
+        File csv = getCSVFile(args);
+
+        if (csv == null) {
+            warn("No CSV file specified!");
+            return;
+        }
+        else if (csv.isDirectory()) {
+            warn("Specified CSV file is a directory!");
+            return;
+        }
+        else if (!csv.canRead()) {
+            warn("Unable to read '" + csv.toString() + "'");
+            return;
+        }
+
+        File sql = getSQLFile(args);
+
+        if (sql == null) {
+            warn("No destination file specified!");
+            return;
+        }
+        else if (sql.isDirectory()) {
+            warn("Specified destination file is a directory!");
+            return;
+        }
+        else if (sql.exists() && !sql.canWrite()) {
+            warn("Unable to write to '" + sql.toString() + "'");
+            return;
+        }
+        else if (!sql.exists()) {
+            try {
+                sql.createNewFile();
+            }
+            catch (IOException ioe) {
+                warn("Unable to write to '" + sql.toString() + "'");
+                return;
+            }
+        }
+
+        info("Start parsing CSV file '" + csv.toString() + "'");
+
+        try {
+            DgmSqlConverter parser = new DgmSqlConverter(riverDir, csv, sql);
+            parser.read();
+            parser.write();
+        }
+        catch (Exception e) {
+            error("Unexpected error: " + e.getMessage());
+            e.printStackTrace();
+        }
+
+        info("Finished converting CSV -> SQL regularly.");
+    }
+
+    public DgmSqlConverter(File riverDir, File csv, File sql) {
+        this.riverDir = riverDir;
+        this.csv = csv;
+        this.sql = sql;
+        this.dgms = new ArrayList<DGM>();
+    }
+
+    public void read() {
+        info("Read DGM information from CSV file: " + csv.getAbsolutePath());
+        
+        InputStream in = null;
+
+        try {
+            in = new BufferedInputStream(new FileInputStream(csv));
+        }
+        catch (FileNotFoundException e) {
+            error("File not found: " + e.getMessage());
+            return;
+        }
+
+        Reader reader = new InputStreamReader(in);
+        CSVReader csvReader = new CSVReader(reader, DEFAULT_SEPERATOR,
+            DEFAULT_QUOTE);
+
+        List<String[]> rows = new ArrayList<String[]>();
+
+        int success = 0;
+
+        try {
+            rows = csvReader.readAll();
+
+            for (int idx = 0; idx < rows.size(); idx++) {
+                String[] row = rows.get(idx);
+                if (readRow(row)) {
+                    success++;
+                }
+                else {
+                    warn("Unable to parse row " + (idx + 1));
+                }
+            }
+        }
+        catch (IOException e) {
+            error("Error while parsing CSV: " + e.getMessage());
+            return;
+        }
+
+        info("Parsed CSV file: " + rows.size() + " lines.");
+        info("Parsed " + success + " line successful");
+    }
+
+    private boolean readRow(String[] row) {
+        if (row == null) {
+            warn("Row is null!");
+            return false;
+        }
+
+        if (row.length < MIN_COLUMN_COUNT) {
+            warn("invalid column count: " + row.length);
+            return false;
+        }
+
+        StringBuffer rowBuffer = new StringBuffer();
+        for (String col : row) {
+            rowBuffer.append(col);
+            rowBuffer.append(" | ");
+        }
+        debug(rowBuffer.toString());
+
+        try {
+            DGM dgm = new DGM();
+            dgm.river = readRiver(row[IDX_RIVERNAME]);
+            dgm.name = row[IDX_NAME];
+            dgm.projection = row[IDX_PROJECTION];
+            dgm.elevationState = row[IDX_ELEVATION_STATE];
+            dgm.format = row[IDX_FORMAT];
+            dgm.resolution = row[IDX_RESOLUTION];
+            dgm.description = row[IDX_DESCRIPTION];
+            dgm.lower = readLower(row[IDX_LOWER]);
+            dgm.upper = readUpper(row[IDX_UPPER]);
+            dgm.yearFrom = readFromYear(row[IDX_YEAR_FROM]);
+            dgm.yearTo = readToYear(row[IDX_YEAR_TO]);
+            dgm.borderBreak = readBorderBreak(row[IDX_BORDER_BREAK]);
+            dgm.path = readPath(dgm.river, row[IDX_FILE_PATH],
+                row[IDX_FILE_NAME]);
+
+            dgms.add(dgm);
+
+            return true;
+        }
+        catch (IllegalArgumentException iae) {
+            warn(iae.getMessage());
+        }
+
+        return false;
+    }
+
+    private String readRiver(String rivername) throws IllegalArgumentException {
+        if (rivername == null || rivername.length() == 0) {
+            throw new IllegalAccessError("Invalid rivername: " + rivername);
+        }
+
+        if (!FULL_MODE
+            && !(rivername.equals("Saar") || rivername.equals("Mosel") || rivername
+                .equals("Elbe"))) {
+            throw new IllegalArgumentException("In DEMO mode; skip river: "
+                + rivername);
+        }
+
+        return rivername;
+    }
+
+    private Double readLower(String lower) throws IllegalArgumentException {
+        try {
+            return Double.valueOf(lower);
+        }
+        catch (NumberFormatException nfe) {
+        }
+
+        throw new IllegalArgumentException("Attribute 'lower' invalid: "
+            + lower);
+    }
+
+    private Double readUpper(String upper) throws IllegalArgumentException {
+        try {
+            return Double.valueOf(upper);
+        }
+        catch (NumberFormatException nfe) {
+        }
+
+        throw new IllegalArgumentException("Attribute 'upper' invalid: "
+            + upper);
+    }
+
+    private Integer readFromYear(String from) throws IllegalArgumentException {
+        try {
+            return Integer.valueOf(from);
+        }
+        catch (NumberFormatException nfe) {
+        }
+
+        return null;
+    }
+
+    private Integer readToYear(String to) throws IllegalArgumentException {
+        try {
+            return Integer.valueOf(to);
+        }
+        catch (NumberFormatException nfe) {
+        }
+
+        return null;
+    }
+
+    private String readPath(String rivername, String dir, String filename)
+        throws IllegalArgumentException {
+        File riverDir = new File(this.riverDir, rivername);
+        File dgmDir = new File(riverDir, dir);
+        File dgmFile = new File(dgmDir, filename);
+
+        try {
+            debug("Path of DGM = " + dgmFile.getAbsolutePath());
+
+            if (dgmFile == null || !dgmFile.exists()) {
+                throw new IllegalAccessError(
+                    "Specified DGM file does not exist: "
+                        + dgmFile.getAbsolutePath());
+            }
+
+            if (!dgmFile.isFile()) {
+                throw new IllegalArgumentException(
+                    "Specified DGM file is no file: "
+                        + dgmFile.getAbsolutePath());
+            }
+        }
+        catch (IllegalAccessError iae) {
+            throw new IllegalArgumentException("Cannot find DGM file (river="
+                + rivername + " | directory=" + dir + " | filename=" + filename
+                + ")");
+        }
+
+        return dgmFile.getAbsolutePath();
+    }
+
+    private boolean readBorderBreak(String borderBreak) {
+        if (borderBreak == null || borderBreak.length() == 0) {
+            return true;
+        }
+        else if (borderBreak.toLowerCase().equals("ja")) {
+            return true;
+        }
+        else if (borderBreak.toLowerCase().equals("nein")) {
+            return false;
+        }
+        else {
+            return true;
+        }
+    }
+
+    public void write() {
+        info("Write DEM information to SQL file: " + sql.getAbsolutePath());
+        
+        BufferedWriter bufferedWriter = null;
+        try {
+            bufferedWriter = new BufferedWriter(new FileWriter(sql));
+
+            for (DGM dgm : dgms) {
+                bufferedWriter.write(dgm.toSQL());
+                bufferedWriter.newLine();
+            }
+        }
+        catch (IOException ioe) {
+            error(ioe.getMessage());
+        }
+        finally {
+            if (bufferedWriter != null) {
+                try {
+                    bufferedWriter.close();
+                }
+                catch (IOException ioe) {
+                }
+            }
+        }
+    }
+}

http://dive4elements.wald.intevation.org