Mercurial > dive4elements > river

comparison flys-backend/src/main/java/de/intevation/flys/utils/DgmSqlConverter.java @ 3951:89ada0b9083f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
children 5d8db3349b77
comparison
equal deleted inserted replaced
3950:82e931f88137 3951:89ada0b9083f
1 package de.intevation.flys.utils;
2
3 import java.io.BufferedInputStream;
4 import java.io.BufferedWriter;
5 import java.io.File;
6 import java.io.FileInputStream;
7 import java.io.FileNotFoundException;
8 import java.io.FileWriter;
9 import java.io.IOException;
10 import java.io.InputStream;
11 import java.io.InputStreamReader;
12 import java.io.Reader;
13 import java.util.ArrayList;
14 import java.util.List;
15
16 import au.com.bytecode.opencsv.CSVReader;
17
18
19 /**
20 * A converter for CSV files with DGM information. The result of a conversion
21 * is an SQL file with "INSERT INTO dem ..." statements.
22 * <br>
23 * To start the converter, at least the following three system properties are
24 * required:
25 * <br>
26 * <ul>
27 * <ol><b>gew.dir</b>: This property must point to the directory where all
28 * rivers are stored.</ol>
29 * <ol><b>csv</b>: This property must point to the CSV file that contains the
30 * DGM information.</ol>
31 * <ol><b>sql</b>: This property must point to a (not yet existing) file that
32 * will be generated by this converter.</ol>
33 * </ul>
34 * <br>
35 * In addiation, the following properties are accepted to modify log messages,
36 * etc.
37 * <ul>
38 * <ol><b>verbose</b>: Accepts integer values (0, 1, 2, 3) to modify the log
39 * messages. The higher the value the more log messages are printed to STDOUT.
40 * </ol>
41 * <ol><b>full</b>: Accepts true|false values. If true is set, all rivers
42 * included in the CSV file are taken into account while parsing. Otherwise,
43 * the converter reads information for 'Saar', 'Mosel' and 'Eble' only.</ol>
44 * </ul>
45 *
46 * @author Ingo Weinzierl <a href="mailto:ingo.weinzierl@intevation.de">
47 * ingo.weinzierl@intevation.de</a>
48 *
49 */
50 public class DgmSqlConverter {
51
52 public static final String SQL_INSERT = "INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,"
53 + "projection, elevation_state, format, border_break, resolution, description, path) VALUES ("
54 + "%s, '%s', %s, %s, %s, %s, '%s', '%s', '%s', %s, '%s', '%s', '%s');";
55
56 public static final String SQL_SELECT_RIVER = "(SELECT id from rivers WHERE name = '%s')";
57
58 public static final char DEFAULT_SEPERATOR = ',';
59 public static final char DEFAULT_QUOTE = '"';
60 public static final int DEFAULT_LOG_LEVEL = 2;
61
62 public static final boolean FULL_MODE = Boolean.getBoolean("full");
63 public static final String GEW_DIR = System.getProperty("gew.dir", null);
64 public static final String CSV_FILE = System.getProperty("csv");
65 public static final String SQL_FILE = System.getProperty("sql");
66 public static final int LOG_LEVEL = Integer.getInteger("verbose",
67 DEFAULT_LOG_LEVEL);
68
69 public static final int MIN_COLUMN_COUNT = 15;
70
71 public static final int IDX_RIVERNAME = 0;
72 public static final int IDX_NAME = 12;
73 public static final int IDX_LOWER = 1;
74 public static final int IDX_UPPER = 2;
75 public static final int IDX_YEAR_FROM = 3;
76 public static final int IDX_YEAR_TO = 4;
77 public static final int IDX_PROJECTION = 7;
78 public static final int IDX_ELEVATION_STATE = 8;
79 public static final int IDX_FORMAT = 9;
80 public static final int IDX_BORDER_BREAK = 10;
81 public static final int IDX_RESOLUTION = 11;
82 public static final int IDX_DESCRIPTION = 14;
83 public static final int IDX_FILE_NAME = 5;
84 public static final int IDX_FILE_PATH = 6;
85
86 private class DGM {
87
88 public String river;
89 public String name;
90 public String projection;
91 public String elevationState;
92 public String format;
93 public String resolution;
94 public String description;
95 public String path;
96
97 public double lower;
98 public double upper;
99 public Integer yearFrom;
100 public Integer yearTo;
101
102 public boolean borderBreak;
103
104 public DGM() {
105 borderBreak = false;
106 }
107
108 public String toSQL() {
109 String riverId = String.format(SQL_SELECT_RIVER, river);
110 String lower = String.valueOf(this.lower);
111 String upper = String.valueOf(this.upper);
112 String yearFrom = this.yearFrom != null ? String
113 .valueOf(this.yearFrom) : "null";
114 String yearTo = this.yearTo != null ? String.valueOf(this.yearTo)
115 : "null";
116
117 return String.format(SQL_INSERT, riverId, name, lower, upper,
118 yearFrom, yearTo, projection, elevationState, format,
119 borderBreak, resolution, description, path);
120 }
121 }
122
123 private File riverDir;
124 private File csv;
125 private File sql;
126
127 private List<DGM> dgms;
128
129 public static void debug(String msg) {
130 if (LOG_LEVEL >= 3) {
131 System.out.println("DEBUG: " + msg);
132 }
133 }
134
135 public static void info(String msg) {
136 if (LOG_LEVEL >= 2) {
137 System.out.println("INFO: " + msg);
138 }
139 }
140
141 public static void warn(String msg) {
142 if (LOG_LEVEL >= 1) {
143 System.out.println("WARN: " + msg);
144 }
145 }
146
147 public static void error(String msg) {
148 System.out.println("ERROR: " + msg);
149 }
150
151 public static File getRiverDir(String[] args) {
152 if (GEW_DIR != null && GEW_DIR.length() > 0) {
153 return new File(GEW_DIR);
154 }
155 else if (args != null && args.length > 0) {
156 return new File(args[0]);
157 }
158
159 return null;
160 }
161
162 public static File getCSVFile(String[] args) {
163 if (CSV_FILE != null && CSV_FILE.length() > 0) {
164 return new File(CSV_FILE);
165 }
166 else if (args != null && args.length > 1) {
167 return new File(args[1]);
168 }
169
170 return null;
171 }
172
173 public static File getSQLFile(String[] args) {
174 if (SQL_FILE != null && SQL_FILE.length() > 0) {
175 return new File(SQL_FILE);
176 }
177 else if (args != null && args.length > 2) {
178 return new File(args[2]);
179 }
180
181 return null;
182 }
183
184 public static void main(String[] args) {
185 info("Start convering CSV -> SQL statements");
186
187 if (!FULL_MODE) {
188 info("You are running in DEMO mode; other rivers than 'Saar', 'Mosel' and 'Elbe' are ignored.");
189 }
190
191 File riverDir = getRiverDir(args);
192
193 if (riverDir == null) {
194 warn("No rivers directory specified!");
195 return;
196 }
197 else if (!riverDir.isDirectory()) {
198 warn("Specified rivers directory is not a directory!");
199 return;
200 }
201 else if (!riverDir.canRead()) {
202 warn("Unable to read '" + riverDir.toString() + "'");
203 return;
204 }
205
206 File csv = getCSVFile(args);
207
208 if (csv == null) {
209 warn("No CSV file specified!");
210 return;
211 }
212 else if (csv.isDirectory()) {
213 warn("Specified CSV file is a directory!");
214 return;
215 }
216 else if (!csv.canRead()) {
217 warn("Unable to read '" + csv.toString() + "'");
218 return;
219 }
220
221 File sql = getSQLFile(args);
222
223 if (sql == null) {
224 warn("No destination file specified!");
225 return;
226 }
227 else if (sql.isDirectory()) {
228 warn("Specified destination file is a directory!");
229 return;
230 }
231 else if (sql.exists() && !sql.canWrite()) {
232 warn("Unable to write to '" + sql.toString() + "'");
233 return;
234 }
235 else if (!sql.exists()) {
236 try {
237 sql.createNewFile();
238 }
239 catch (IOException ioe) {
240 warn("Unable to write to '" + sql.toString() + "'");
241 return;
242 }
243 }
244
245 info("Start parsing CSV file '" + csv.toString() + "'");
246
247 try {
248 DgmSqlConverter parser = new DgmSqlConverter(riverDir, csv, sql);
249 parser.read();
250 parser.write();
251 }
252 catch (Exception e) {
253 error("Unexpected error: " + e.getMessage());
254 e.printStackTrace();
255 }
256
257 info("Finished converting CSV -> SQL regularly.");
258 }
259
260 public DgmSqlConverter(File riverDir, File csv, File sql) {
261 this.riverDir = riverDir;
262 this.csv = csv;
263 this.sql = sql;
264 this.dgms = new ArrayList<DGM>();
265 }
266
267 public void read() {
268 info("Read DGM information from CSV file: " + csv.getAbsolutePath());
269
270 InputStream in = null;
271
272 try {
273 in = new BufferedInputStream(new FileInputStream(csv));
274 }
275 catch (FileNotFoundException e) {
276 error("File not found: " + e.getMessage());
277 return;
278 }
279
280 Reader reader = new InputStreamReader(in);
281 CSVReader csvReader = new CSVReader(reader, DEFAULT_SEPERATOR,
282 DEFAULT_QUOTE);
283
284 List<String[]> rows = new ArrayList<String[]>();
285
286 int success = 0;
287
288 try {
289 rows = csvReader.readAll();
290
291 for (int idx = 0; idx < rows.size(); idx++) {
292 String[] row = rows.get(idx);
293 if (readRow(row)) {
294 success++;
295 }
296 else {
297 warn("Unable to parse row " + (idx + 1));
298 }
299 }
300 }
301 catch (IOException e) {
302 error("Error while parsing CSV: " + e.getMessage());
303 return;
304 }
305
306 info("Parsed CSV file: " + rows.size() + " lines.");
307 info("Parsed " + success + " line successful");
308 }
309
310 private boolean readRow(String[] row) {
311 if (row == null) {
312 warn("Row is null!");
313 return false;
314 }
315
316 if (row.length < MIN_COLUMN_COUNT) {
317 warn("invalid column count: " + row.length);
318 return false;
319 }
320
321 StringBuffer rowBuffer = new StringBuffer();
322 for (String col : row) {
323 rowBuffer.append(col);
324 rowBuffer.append(" | ");
325 }
326 debug(rowBuffer.toString());
327
328 try {
329 DGM dgm = new DGM();
330 dgm.river = readRiver(row[IDX_RIVERNAME]);
331 dgm.name = row[IDX_NAME];
332 dgm.projection = row[IDX_PROJECTION];
333 dgm.elevationState = row[IDX_ELEVATION_STATE];
334 dgm.format = row[IDX_FORMAT];
335 dgm.resolution = row[IDX_RESOLUTION];
336 dgm.description = row[IDX_DESCRIPTION];
337 dgm.lower = readLower(row[IDX_LOWER]);
338 dgm.upper = readUpper(row[IDX_UPPER]);
339 dgm.yearFrom = readFromYear(row[IDX_YEAR_FROM]);
340 dgm.yearTo = readToYear(row[IDX_YEAR_TO]);
341 dgm.borderBreak = readBorderBreak(row[IDX_BORDER_BREAK]);
342 dgm.path = readPath(dgm.river, row[IDX_FILE_PATH],
343 row[IDX_FILE_NAME]);
344
345 dgms.add(dgm);
346
347 return true;
348 }
349 catch (IllegalArgumentException iae) {
350 warn(iae.getMessage());
351 }
352
353 return false;
354 }
355
356 private String readRiver(String rivername) throws IllegalArgumentException {
357 if (rivername == null || rivername.length() == 0) {
358 throw new IllegalAccessError("Invalid rivername: " + rivername);
359 }
360
361 if (!FULL_MODE
362 && !(rivername.equals("Saar") || rivername.equals("Mosel") || rivername
363 .equals("Elbe"))) {
364 throw new IllegalArgumentException("In DEMO mode; skip river: "
365 + rivername);
366 }
367
368 return rivername;
369 }
370
371 private Double readLower(String lower) throws IllegalArgumentException {
372 try {
373 return Double.valueOf(lower);
374 }
375 catch (NumberFormatException nfe) {
376 }
377
378 throw new IllegalArgumentException("Attribute 'lower' invalid: "
379 + lower);
380 }
381
382 private Double readUpper(String upper) throws IllegalArgumentException {
383 try {
384 return Double.valueOf(upper);
385 }
386 catch (NumberFormatException nfe) {
387 }
388
389 throw new IllegalArgumentException("Attribute 'upper' invalid: "
390 + upper);
391 }
392
393 private Integer readFromYear(String from) throws IllegalArgumentException {
394 try {
395 return Integer.valueOf(from);
396 }
397 catch (NumberFormatException nfe) {
398 }
399
400 return null;
401 }
402
403 private Integer readToYear(String to) throws IllegalArgumentException {
404 try {
405 return Integer.valueOf(to);
406 }
407 catch (NumberFormatException nfe) {
408 }
409
410 return null;
411 }
412
413 private String readPath(String rivername, String dir, String filename)
414 throws IllegalArgumentException {
415 File riverDir = new File(this.riverDir, rivername);
416 File dgmDir = new File(riverDir, dir);
417 File dgmFile = new File(dgmDir, filename);
418
419 try {
420 debug("Path of DGM = " + dgmFile.getAbsolutePath());
421
422 if (dgmFile == null || !dgmFile.exists()) {
423 throw new IllegalAccessError(
424 "Specified DGM file does not exist: "
425 + dgmFile.getAbsolutePath());
426 }
427
428 if (!dgmFile.isFile()) {
429 throw new IllegalArgumentException(
430 "Specified DGM file is no file: "
431 + dgmFile.getAbsolutePath());
432 }
433 }
434 catch (IllegalAccessError iae) {
435 throw new IllegalArgumentException("Cannot find DGM file (river="
436 + rivername + " | directory=" + dir + " | filename=" + filename
437 + ")");
438 }
439
440 return dgmFile.getAbsolutePath();
441 }
442
443 private boolean readBorderBreak(String borderBreak) {
444 if (borderBreak == null || borderBreak.length() == 0) {
445 return true;
446 }
447 else if (borderBreak.toLowerCase().equals("ja")) {
448 return true;
449 }
450 else if (borderBreak.toLowerCase().equals("nein")) {
451 return false;
452 }
453 else {
454 return true;
455 }
456 }
457
458 public void write() {
459 info("Write DEM information to SQL file: " + sql.getAbsolutePath());
460
461 BufferedWriter bufferedWriter = null;
462 try {
463 bufferedWriter = new BufferedWriter(new FileWriter(sql));
464
465 for (DGM dgm : dgms) {
466 bufferedWriter.write(dgm.toSQL());
467 bufferedWriter.newLine();
468 }
469 }
470 catch (IOException ioe) {
471 error(ioe.getMessage());
472 }
473 finally {
474 if (bufferedWriter != null) {
475 try {
476 bufferedWriter.close();
477 }
478 catch (IOException ioe) {
479 }
480 }
481 }
482 }
483 }
http://dive4elements.wald.intevation.org