Mercurial > dive4elements > river
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) { + } + } + } + } +}