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