001 //$HeadURL: svn+ssh://jwilden@svn.wald.intevation.org/deegree/base/branches/2.5_testing/src/org/deegree/tools/raster/ImportIndexIntoDB.java $
002 /*----------------------------------------------------------------------------
003 This file is part of deegree, http://deegree.org/
004 Copyright (C) 2001-2009 by:
005 Department of Geography, University of Bonn
006 and
007 lat/lon GmbH
008
009 This library is free software; you can redistribute it and/or modify it under
010 the terms of the GNU Lesser General Public License as published by the Free
011 Software Foundation; either version 2.1 of the License, or (at your option)
012 any later version.
013 This library is distributed in the hope that it will be useful, but WITHOUT
014 ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
015 FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
016 details.
017 You should have received a copy of the GNU Lesser General Public License
018 along with this library; if not, write to the Free Software Foundation, Inc.,
019 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
020
021 Contact information:
022
023 lat/lon GmbH
024 Aennchenstr. 19, 53177 Bonn
025 Germany
026 http://lat-lon.de/
027
028 Department of Geography, University of Bonn
029 Prof. Dr. Klaus Greve
030 Postfach 1147, 53001 Bonn
031 Germany
032 http://www.geographie.uni-bonn.de/deegree/
033
034 e-mail: info@deegree.org
035 ----------------------------------------------------------------------------*/
036
037 package org.deegree.tools.raster;
038
039 import static java.io.File.separator;
040
041 import java.io.File;
042 import java.io.FileOutputStream;
043 import java.io.IOException;
044 import java.io.PrintStream;
045 import java.net.URI;
046 import java.net.URISyntaxException;
047 import java.net.URL;
048 import java.security.InvalidParameterException;
049 import java.sql.Connection;
050 import java.sql.PreparedStatement;
051 import java.sql.SQLException;
052 import java.sql.Statement;
053 import java.util.Arrays;
054 import java.util.HashMap;
055 import java.util.Iterator;
056 import java.util.List;
057 import java.util.Map;
058 import java.util.Properties;
059
060 import javax.xml.transform.TransformerException;
061
062 import org.deegree.datatypes.QualifiedName;
063 import org.deegree.framework.log.ILogger;
064 import org.deegree.framework.log.LoggerFactory;
065 import org.deegree.framework.util.ConvenienceFileFilter;
066 import org.deegree.framework.util.FileUtils;
067 import org.deegree.framework.util.StringTools;
068 import org.deegree.framework.xml.XMLFragment;
069 import org.deegree.framework.xml.XSLTDocument;
070 import org.deegree.io.DBConnectionPool;
071 import org.deegree.io.DBPoolException;
072 import org.deegree.io.datastore.sql.postgis.PGgeometryAdapter;
073 import org.deegree.io.dbaseapi.DBaseException;
074 import org.deegree.io.shpapi.shape_new.ShapeFile;
075 import org.deegree.io.shpapi.shape_new.ShapeFileReader;
076 import org.deegree.model.feature.Feature;
077 import org.deegree.model.feature.FeatureCollection;
078 import org.deegree.model.spatialschema.Geometry;
079 import org.deegree.model.spatialschema.GeometryException;
080 import org.xml.sax.SAXException;
081
082 /**
083 *
084 *
085 * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
086 * @author last edited by: $Author: mschneider $
087 *
088 * @version $Revision: 18195 $, $Date: 2009-06-18 17:55:39 +0200 (Do, 18 Jun 2009) $
089 */
090 public class ImportIndexIntoDB {
091
092 private static ILogger LOG = LoggerFactory.getLogger( ImportIndexIntoDB.class );
093
094 private static final String createPG = "create_wcs_table_template_postgis.sql";
095
096 private static final String indexPG = "create_wcs_table_index_template_postgis.sql";
097
098 private static final String createOrcl = "create_wcs_table_template_oracle.sql";
099
100 private static final String indexOrcl = "create_wcs_index_table_template_oracle.sql";
101
102 private static URI app = null;
103
104 static {
105 try {
106 app = new URI( "http://www.deegree.org/app" );
107 } catch ( URISyntaxException e ) {
108 e.printStackTrace();
109 }
110 }
111
112 private String table;
113
114 private String user;
115
116 private String pw;
117
118 private String driver;
119
120 private String url;
121
122 private String rootDir;
123
124 private File configurationFile;
125
126 private boolean append = false;
127
128 private boolean switchDir = false;
129
130 /**
131 * @param driver
132 * @param url
133 * @param user
134 * @param pw
135 * @param table
136 * @param rootDir
137 */
138 public ImportIndexIntoDB( String driver, String url, String user, String pw, String table, String rootDir ) {
139 this.driver = driver;
140 this.url = url;
141 this.user = user;
142 this.pw = pw;
143 this.table = table;
144 this.rootDir = rootDir;
145 ConvenienceFileFilter ff = new ConvenienceFileFilter( false, "XML" );
146 File dir = new File( this.rootDir );
147 configurationFile = dir.listFiles( ff )[0];
148 }
149
150 /**
151 * @param driver
152 * @param url
153 * @param user
154 * @param pw
155 * @param table
156 * @param rootDir
157 * @param append
158 * @param switchDir
159 */
160 public ImportIndexIntoDB( String driver, String url, String user, String pw, String table, String rootDir,
161 boolean append, boolean switchDir ) {
162 this.driver = driver;
163 this.url = url;
164 this.user = user;
165 this.pw = pw;
166 this.table = table;
167 this.rootDir = rootDir;
168 ConvenienceFileFilter ff = new ConvenienceFileFilter( false, "XML" );
169 File dir = new File( this.rootDir );
170 configurationFile = dir.listFiles( ff )[0];
171 this.append = append;
172 this.switchDir = switchDir;
173 }
174
175 /**
176 * main method to perform indexing of raster tile via a spatial DB
177 *
178 * @throws IOException
179 * @throws DBPoolException
180 * @throws SQLException
181 * @throws DBaseException
182 * @throws GeometryException
183 * @throws SAXException
184 * @throws TransformerException
185 */
186 public void perform()
187 throws IOException, DBPoolException, SQLException, DBaseException, GeometryException,
188 SAXException, TransformerException {
189 if ( !append ) {
190 createTables();
191 }
192 fillIndexTable();
193 // index will be created after data has been added to increase performance
194 if ( !append ) {
195 createIndex();
196 }
197 adaptConfiguration();
198 }
199
200 /**
201 * fill the pyramid table with informations about the assoziation between levels and scales
202 *
203 * @param map
204 * @throws DBPoolException
205 * @throws SQLException
206 */
207 private void fillPyramidTable( Map<File, PyramidHelper> map )
208 throws DBPoolException, SQLException {
209 DBConnectionPool pool = DBConnectionPool.getInstance();
210 Connection con = pool.acquireConnection( driver, url, user, pw );
211 Iterator<PyramidHelper> iterator = map.values().iterator();
212 while ( iterator.hasNext() ) {
213 PyramidHelper helper = iterator.next();
214 String sql = StringTools.concat( 200, "INSERT INTO ", table,
215 "_pyr (level,minscale,maxscale) values (?,?,?)" );
216 PreparedStatement stmt = con.prepareStatement( sql );
217 stmt.setInt( 1, helper.level );
218 stmt.setFloat( 2, helper.minscale );
219 stmt.setFloat( 3, helper.maxscale );
220 stmt.execute();
221 stmt.close();
222 }
223 LOG.logInfo( "pyramid table filled!" );
224
225 }
226
227 /**
228 * creates DB-indexes for pyramid and tile-index tables
229 *
230 * @throws IOException
231 * @throws DBPoolException
232 * @throws SQLException
233 */
234 private void createIndex()
235 throws IOException, DBPoolException, SQLException {
236 String template = null;
237 if ( driver.toUpperCase().indexOf( "ORACLE" ) > -1 ) {
238 template = indexOrcl;
239 } else if ( driver.toUpperCase().indexOf( "POSTGRES" ) > -1 ) {
240 template = indexPG;
241 }
242
243 StringBuffer sb = FileUtils.readTextFile( ImportIndexIntoDB.class.getResource( template ) );
244 String tmp = StringTools.replace( sb.toString(), "$TABLE$", table.toLowerCase(), true );
245 String[] sql = parseSQLStatements( tmp );
246
247 DBConnectionPool pool = DBConnectionPool.getInstance();
248 Connection con = pool.acquireConnection( driver, url, user, pw );
249 Statement stmt = con.createStatement();
250 for ( int i = 0; i < sql.length; i++ ) {
251 stmt.execute( sql[i] );
252 }
253 stmt.close();
254 pool.releaseConnection( con, driver, url, user, pw );
255 LOG.logInfo( "indexes created!" );
256
257 }
258
259 /**
260 * fills the index table with informations about raster tiles, their spatial extent, assigened
261 * level and assigned file
262 *
263 * @throws IOException
264 * @throws DBaseException
265 * @throws DBPoolException
266 * @throws SQLException
267 * @throws GeometryException
268 * @throws SAXException
269 */
270 private void fillIndexTable()
271 throws IOException, DBaseException, DBPoolException, SQLException, GeometryException,
272 SAXException {
273 DBConnectionPool pool = DBConnectionPool.getInstance();
274 Connection con = pool.acquireConnection( driver, url, user, pw );
275
276 ConvenienceFileFilter ff = new ConvenienceFileFilter( false, "SHP" );
277 File dir = new File( rootDir );
278 File[] files = dir.listFiles( ff );
279 Map<File, PyramidHelper> map = createLevelMap( files );
280 if ( !append ) {
281 fillPyramidTable( map );
282 }
283
284 XMLFragment xml = new XMLFragment( configurationFile.toURI().toURL() );
285
286 for ( int i = 0; i < files.length; i++ ) {
287 LOG.logInfo( "import: ", files[i].getAbsolutePath() );
288 String tmp = files[i].getAbsolutePath();
289 tmp = tmp.substring( 0, tmp.length() - 4 );
290 ShapeFileReader sfr = new ShapeFileReader( tmp );
291 ShapeFile sf = sfr.read();
292 FeatureCollection fc = sf.getFeatureCollection();
293 for ( int j = 0; j < fc.size(); j++ ) {
294 String sql = StringTools.concat( 200, "INSERT INTO ", table,
295 " (level, dir, file, bbox) values(?,?,?,?)" );
296 PreparedStatement stmt = con.prepareStatement( sql );
297 Object[] values = createInsert( fc.getFeature( j ) );
298 stmt.setInt( 1, map.get( files[i] ).level );
299 if ( switchDir ) {
300 String s = xml.resolve( (String) values[1] ).toExternalForm();
301 StringBuffer sb = new StringBuffer();
302 int k = 0;
303 int l = s.length() - 1;
304 while ( k != 2 ) {
305 sb.insert( 0, s.charAt( l ) );
306 if ( s.charAt( l ) == '/' ) {
307 k++;
308 }
309 l--;
310 }
311 stmt.setString( 2, sb.toString() );
312 } else {
313 stmt.setString( 2, (String) values[1] );
314 }
315 stmt.setString( 3, (String) values[2] );
316 stmt.setObject( 4, values[3] );
317 stmt.execute();
318 stmt.close();
319 }
320
321 }
322
323 pool.releaseConnection( con, driver, url, user, pw );
324 LOG.logInfo( "index table filled!" );
325
326 }
327
328 /**
329 * creates pyramid level map; assigning each file to a level starting at 0
330 *
331 * @param files
332 * @return the created pyramid level map
333 */
334 private Map<File, PyramidHelper> createLevelMap( File[] files ) {
335 PyramidHelper[] phelper = new PyramidHelper[files.length];
336 for ( int i = 0; i < files.length; i++ ) {
337 String tmp = files[i].getName().substring( 2, files[i].getName().length() - 4 );
338 float scale = Float.parseFloat( tmp );
339 PyramidHelper helper = new PyramidHelper();
340 helper.file = files[i];
341 helper.scale = scale;
342 phelper[i] = helper;
343 }
344 Arrays.sort( phelper );
345 Map<File, PyramidHelper> map = new HashMap<File, PyramidHelper>();
346 for ( int i = 0; i < phelper.length; i++ ) {
347 phelper[i].level = i;
348 if ( i == 0 ) {
349 phelper[i].minscale = 0;
350 } else {
351 phelper[i].minscale = phelper[i - 1].scale;
352 }
353 if ( i == phelper.length - 1 ) {
354 phelper[i].maxscale = 99999999999F;
355 } else {
356 phelper[i].maxscale = phelper[i].scale;
357 }
358 map.put( phelper[i].file, phelper[i] );
359 }
360 LOG.logInfo( "pyramid level assoziation:", map );
361 return map;
362 }
363
364 /**
365 *
366 * @param feature
367 * @return values to insert for a raster tile
368 * @throws GeometryException
369 */
370 private Object[] createInsert( Feature feature )
371 throws GeometryException {
372 Object[] values = new Object[4];
373 values[1] = feature.getProperties( new QualifiedName( "FOLDER", app ) )[0].getValue();
374 values[2] = feature.getProperties( new QualifiedName( "FILENAME", app ) )[0].getValue();
375 Geometry geom = feature.getDefaultGeometryPropertyValue();
376 values[3] = PGgeometryAdapter.export( geom, -1 );
377 return values;
378 }
379
380 /**
381 * creates data and pyramid table
382 *
383 * @throws IOException
384 * @throws DBPoolException
385 * @throws SQLException
386 */
387 private void createTables()
388 throws IOException, DBPoolException, SQLException {
389 String template = null;
390 if ( driver.toUpperCase().indexOf( "ORACLE" ) > -1 ) {
391 template = createOrcl;
392 } else if ( driver.toUpperCase().indexOf( "POSTGRES" ) > -1 ) {
393 template = createPG;
394 }
395 StringBuffer sb = FileUtils.readTextFile( ImportIndexIntoDB.class.getResource( template ) );
396 String tmp = StringTools.replace( sb.toString(), "$TABLE$", table.toLowerCase(), true );
397 String[] sql = parseSQLStatements( tmp );
398
399 DBConnectionPool pool = DBConnectionPool.getInstance();
400 Connection con = pool.acquireConnection( driver, url, user, pw );
401 Statement stmt = con.createStatement();
402 for ( int i = 0; i < sql.length; i++ ) {
403 try {
404 stmt.execute( sql[i] );
405 } catch ( Exception e ) {
406 LOG.logWarning( e.getMessage() );
407 }
408 }
409 stmt.close();
410 pool.releaseConnection( con, driver, url, user, pw );
411 LOG.logInfo( "tables created!" );
412
413 }
414
415 /**
416 *
417 * @param tmp
418 * @return SQL statements parsed from a file/string
419 */
420 private String[] parseSQLStatements( String tmp ) {
421 List<String> list = StringTools.toList( tmp, ";", false );
422 String[] sql = new String[list.size()];
423 for ( int i = 0; i < sql.length; i++ ) {
424 sql[i] = list.get( i ).trim();
425 }
426 return sql;
427 }
428
429 /**
430 * adapts the wcs configuration for a coverage to use the created db-based index instead of a
431 * shapefile base one.
432 *
433 * @throws IOException
434 * @throws SAXException
435 * @throws TransformerException
436 */
437 private void adaptConfiguration()
438 throws IOException, SAXException, TransformerException {
439 URL url = ImportIndexIntoDB.class.getResource( "wcsconfiguration.xsl" );
440 XSLTDocument xslt = new XSLTDocument( url );
441 XMLFragment xml = new XMLFragment( configurationFile.toURI().toURL() );
442 xml = xslt.transform( xml );
443 Map<String, String> params = new HashMap<String, String>();
444 params.put( "TABLE", table );
445 params.put( "USER", user );
446 params.put( "PASSWORD", pw );
447 params.put( "DRIVER", driver );
448 params.put( "URL", this.url );
449 xml = xslt.transform( xml, XMLFragment.DEFAULT_URL, null, params );
450
451 String s = configurationFile.getAbsolutePath();
452 if ( switchDir ) {
453 s = configurationFile.getAbsolutePath();
454 int kk = s.lastIndexOf( File.separator );
455 s = s.substring( 0, kk );
456 kk = s.lastIndexOf( File.separator );
457 s = s.substring( 0, kk ) + File.separator + configurationFile.getName();
458 }
459
460 FileOutputStream fos = new FileOutputStream( s );
461 xml.write( fos );
462 fos.close();
463 }
464
465 /**
466 * @param args
467 * @throws Exception
468 */
469 public static void main( String[] args )
470 throws Exception {
471
472 try {
473 Properties map = new Properties();
474 for ( int i = 0; i < args.length; ) {
475 String first = args[i++];
476 if ( "?".equals( first ) || "-h".equals( first ) || "-help".equals( first ) ) {
477 printHelp();
478 System.exit( 0 );
479 }
480 map.put( first, args[i++] );
481 }
482
483 // set up stderr/stdout redirection
484 String redirect = map.getProperty( "-redirect" );
485 if ( redirect != null && redirect.equals( "true" ) ) {
486 String rootDir = map.getProperty( "-rootDir" );
487 File f = new File( rootDir + separator + "dbindexer.log" );
488 PrintStream out = new PrintStream( new FileOutputStream( f ) );
489 System.setOut( out );
490 System.setErr( out );
491 }
492
493 try {
494 validate( map );
495 } catch ( InvalidParameterException ipe ) {
496 LOG.logError( ipe.getMessage() );
497 printHelp();
498 System.exit( 1 );
499 }
500 LOG.logDebug( "Resulting commandline arguments and their values {argument=value, ...}: " + map.toString() );
501
502 String table = map.getProperty( "-table" );
503 String user = map.getProperty( "-user" );
504 String pw = map.getProperty( "-password" );
505 String driver = map.getProperty( "-driver" );
506 String url = map.getProperty( "-url" );
507 String rootDir = map.getProperty( "-rootDir" );
508 String tmp = map.getProperty( "-append" );
509 boolean append = "true".equals( tmp );
510 tmp = map.getProperty( "-switchDir" );
511 boolean switchDir = "true".equals( tmp );
512
513 ImportIndexIntoDB importer = new ImportIndexIntoDB( driver, url, user, pw, table, rootDir, append,
514 switchDir );
515 importer.perform();
516 } catch ( Exception e ) {
517 e.printStackTrace();
518 System.exit( 1 );
519 }
520 System.out.println( "finished ...." );
521 System.exit( 0 );
522 }
523
524 /**
525 *
526 * @param map
527 * @throws InvalidParameterException
528 */
529 private static void validate( Properties map )
530 throws InvalidParameterException {
531 if ( map.getProperty( "-table" ) == null ) {
532 throw new InvalidParameterException( "-table must be set" );
533 }
534 if ( map.getProperty( "-user" ) == null ) {
535 throw new InvalidParameterException( "-user must be set" );
536 }
537 if ( map.getProperty( "-password" ) == null ) {
538 throw new InvalidParameterException( "-password must be set" );
539 }
540 if ( map.getProperty( "-driver" ) == null ) {
541 throw new InvalidParameterException( "-driver must be set" );
542 }
543 if ( map.getProperty( "-url" ) == null ) {
544 throw new InvalidParameterException( "-url must be set" );
545 }
546 if ( map.getProperty( "-rootDir" ) == null ) {
547 throw new InvalidParameterException( "-rootDir must be set" );
548 }
549
550 }
551
552 /**
553 *
554 *
555 */
556 private static void printHelp() {
557 // TODO Auto-generated method stub
558 System.out.println( "no help available at the moment" );
559 }
560
561 /**
562 *
563 *
564 *
565 * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
566 * @author last edited by: $Author: mschneider $
567 *
568 * @version $Revision: 18195 $, $Date: 2009-06-18 17:55:39 +0200 (Do, 18 Jun 2009) $
569 */
570 class PyramidHelper implements Comparable<PyramidHelper> {
571
572 File file;
573
574 float scale;
575
576 float minscale;
577
578 float maxscale;
579
580 int level;
581
582 public int compareTo( PyramidHelper o ) {
583 if ( o.scale < scale ) {
584 return 1;
585 } else if ( o.scale > scale ) {
586 return -1;
587 }
588 return 0;
589 }
590
591 @Override
592 public String toString() {
593 return StringTools.concat( 260, file.getName(), ' ', level, ' ', scale );
594 }
595
596 }
597
598 }