001 //$HeadURL: svn+ssh://rbezema@svn.wald.intevation.org/deegree/base/tags/2.1/src/org/deegree/io/quadtree/DBQuadtreeManagerWithNumberId.java $
002 /*---------------- FILE HEADER ------------------------------------------
003
004 This file is part of deegree.
005 Copyright (C) 2001-2006 by:
006 EXSE, Department of Geography, University of Bonn
007 http://www.giub.uni-bonn.de/deegree/
008 lat/lon GmbH
009 http://www.lat-lon.de
010
011 This library is free software; you can redistribute it and/or
012 modify it under the terms of the GNU Lesser General Public
013 License as published by the Free Software Foundation; either
014 version 2.1 of the License, or (at your option) any later version.
015
016 This library is distributed in the hope that it will be useful,
017 but WITHOUT ANY WARRANTY; without even the implied warranty of
018 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
019 Lesser General Public License for more details.
020
021 You should have received a copy of the GNU Lesser General Public
022 License along with this library; if not, write to the Free Software
023 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
024
025 Contact:
026
027 Andreas Poth
028 lat/lon GmbH
029 Aennchenstr. 19
030 53115 Bonn
031 Germany
032 E-Mail: poth@lat-lon.de
033
034 Prof. Dr. Klaus Greve
035 Department of Geography
036 University of Bonn
037 Meckenheimer Allee 166
038 53115 Bonn
039 Germany
040 E-Mail: greve@giub.uni-bonn.de
041
042 ---------------------------------------------------------------------------*/
043 package org.deegree.io.quadtree;
044
045 import java.io.IOException;
046 import java.io.StringReader;
047 import java.sql.Connection;
048 import java.sql.Date;
049 import java.sql.PreparedStatement;
050 import java.sql.ResultSet;
051 import java.sql.Statement;
052
053 import org.deegree.datatypes.Types;
054 import org.deegree.framework.log.ILogger;
055 import org.deegree.framework.log.LoggerFactory;
056 import org.deegree.framework.util.StringTools;
057 import org.deegree.io.DBConnectionPool;
058 import org.deegree.io.JDBCConnection;
059 import org.deegree.io.shpapi.ShapeFile;
060 import org.deegree.model.feature.Feature;
061 import org.deegree.model.feature.schema.FeatureType;
062 import org.deegree.model.feature.schema.PropertyType;
063 import org.deegree.model.spatialschema.Envelope;
064 import org.deegree.model.spatialschema.GMLGeometryAdapter;
065 import org.deegree.model.spatialschema.Geometry;
066 import org.deegree.model.spatialschema.GeometryFactory;
067 import org.deegree.model.spatialschema.Point;
068
069 /**
070 * Same as
071 *
072 * @see org.deegree.io.quadtree.DBQuadtreeManager but uses Integer values as IDs instead of UUIDs.
073 *
074 * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
075 * @author last edited by: $Author: bezema $
076 *
077 * @version $Revision: 6259 $, $Date: 2007-03-20 10:15:15 +0100 (Di, 20 Mär 2007) $
078 */
079 public class DBQuadtreeManagerWithNumberId extends DBQuadtreeManager {
080
081 private static final ILogger LOG = LoggerFactory.getLogger( DBQuadtreeManagerWithNumberId.class );
082
083 /**
084 * @param jdbc
085 * database connection info
086 * @param indexName
087 * this name will be used to create the table that stores the nodes of a specific
088 * quadtree
089 * @param table
090 * name of table the index shall be created for
091 * @param column
092 * name of column the index shall be created for
093 * @param owner
094 * owner of the table (optional, database user will be used if set to null )
095 * @param maxDepth
096 * max depth of the generated quadtree (default = 6 if a value < 2 will be passed)
097 */
098 public DBQuadtreeManagerWithNumberId( JDBCConnection jdbc, String indexName, String table,
099 String column, String owner, int maxDepth ) {
100 super( jdbc, indexName, table, column, owner, maxDepth );
101 }
102
103 /**
104 *
105 * @param driver
106 * database connection driver
107 * @param logon
108 * database connection logon
109 * @param user
110 * database user
111 * @param password
112 * database user's password
113 * @param encoding
114 * character encoding to be used (if possible)
115 * @param indexName
116 * this name will be used to create the table that stores the nodes of a specific
117 * quadtree
118 * @param table
119 * name of table the index shall be created for
120 * @param column
121 * name of column the index shall be created for
122 * @param owner
123 * owner of the table (optional, database user will be used if set to null )
124 * @param maxDepth
125 * max depth of the generated quadtree (default = 6 if a value < 2 will be passed)
126 */
127 public DBQuadtreeManagerWithNumberId( String driver, String logon, String user,
128 String password, String encoding, String indexName,
129 String table, String column, String owner, int maxDepth ) {
130 super( driver, logon, user, password, encoding, indexName, table, column, owner, maxDepth );
131 }
132
133 /**
134 * initializes a QuadtreeManager to access an alread existing Quadtree
135 *
136 * @param jdbc
137 * database connection info
138 * @param table
139 * name of table the index shall be created for
140 * @param column
141 * name of column the index shall be created for
142 * @param owner
143 * owner of the table (optional, database user will be used if set to null )
144 */
145 public DBQuadtreeManagerWithNumberId( JDBCConnection jdbc, String table, String column,
146 String owner ) {
147 super( jdbc, table, column, owner );
148 }
149
150 /**
151 * initializes a QuadtreeManager to access an alread existing Quadtree
152 *
153 * @param driver
154 * database connection driver
155 * @param logon
156 * database connection logon
157 * @param user
158 * database user
159 * @param password
160 * database user's password
161 * @param encoding
162 * character encoding to be used (if possible)
163 * @param table
164 * name of table the index shall be created for
165 * @param column
166 * name of column the index shall be created for
167 * @param owner
168 * owner of the table (optional, database user will be used if set to null )
169 */
170 public DBQuadtreeManagerWithNumberId( String driver, String logon, String user,
171 String password, String encoding, String table,
172 String column, String owner ) {
173 super( driver, logon, user, password, encoding, table, column, owner );
174 }
175
176 /**
177 * stores one feature into the defined table
178 *
179 * @param feature
180 * @param jdbc
181 * @throws Exception
182 */
183 private void storeFeature( Feature feature, int id, JDBCConnection jdbc )
184 throws Exception {
185
186 Connection con = null;
187 DBConnectionPool pool = null;
188
189 FeatureType ft = feature.getFeatureType();
190 PropertyType[] ftp = ft.getProperties();
191 try {
192 pool = DBConnectionPool.getInstance();
193 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
194 jdbc.getPassword() );
195
196 StringBuffer sb = new StringBuffer( 100 );
197 sb.append( "INSERT INTO " ).append( table ).append( '(' );
198 sb.append( "FEATURE_ID," );
199 for ( int i = 0; i < ftp.length; i++ ) {
200 if ( ftp[i].getType() == Types.GEOMETRY ) {
201 sb.append( column ).append( ' ' );
202 } else {
203 sb.append( ftp[i].getName().getLocalName() );
204 }
205 if ( i < ftp.length - 1 ) {
206 sb.append( ", " );
207 }
208 }
209 sb.append( ") VALUES (?," );
210 for ( int i = 0; i < ftp.length; i++ ) {
211 sb.append( '?' );
212 if ( i < ftp.length - 1 ) {
213 sb.append( ", " );
214 }
215 }
216 sb.append( ')' );
217 LOG.logDebug( "SQL for inser feature: " + sb );
218
219 PreparedStatement stmt = con.prepareStatement( sb.toString() );
220 stmt.setInt( 1, id );
221 for ( int i = 0; i < ftp.length; i++ ) {
222 Object o = null;
223 if ( feature.getProperties( ftp[i].getName() ) != null ) {
224 if ( feature.getProperties( ftp[i].getName() ).length > 0 ) {
225 o = feature.getProperties( ftp[i].getName() )[0].getValue();
226 }
227 }
228 if ( o == null ) {
229 stmt.setNull( i + 2, ftp[i].getType() );
230 } else {
231 switch ( ftp[i].getType() ) {
232 case Types.CHAR:
233 case Types.VARCHAR:
234 stmt.setString( i + 2, o.toString() );
235 break;
236 case Types.SMALLINT:
237 case Types.TINYINT:
238 case Types.INTEGER:
239 case Types.BIGINT:
240 stmt.setInt( i + 2, (int) Double.parseDouble( o.toString() ) );
241 break;
242 case Types.DOUBLE:
243 case Types.FLOAT:
244 case Types.DECIMAL:
245 case Types.NUMERIC:
246 stmt.setFloat( i + 2, Float.parseFloat( o.toString() ) );
247 break;
248 case Types.DATE:
249 case Types.TIME:
250 case Types.TIMESTAMP:
251 stmt.setDate( i + 2, (Date) o );
252 break;
253 case Types.GEOMETRY: {
254 StringBuffer gs = GMLGeometryAdapter.export( (Geometry) o );
255 String s = StringTools.replace(
256 gs.toString(),
257 ">",
258 " xmlns:gml=\"http://www.opengis.net/gml\">",
259 false );
260 if ( backend.equals( "POSTGRES" ) || backend.equals( "HSQLDB" ) ) {
261 stmt.setString( i + 2, s );
262 } else if ( backend.equals( "INGRES" ) ) {
263 stmt.setObject( i + 2, new StringReader( s ) );
264 } else {
265 stmt.setObject( i + 2, s.getBytes() );
266 }
267 break;
268 }
269 default: {
270 LOG.logWarning( "unsupported type: " + ftp[i].getType() );
271 }
272 }
273 }
274 }
275
276 stmt.execute();
277 stmt.close();
278
279 } catch ( Exception e ) {
280 e.printStackTrace();
281 throw new IndexException( "could not insert feature into database", e );
282 } finally {
283 try {
284 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
285 jdbc.getPassword() );
286 } catch ( Exception e1 ) {
287 e1.printStackTrace();
288 }
289 }
290 }
291
292 /**
293 * creates table the shape data shall be stored
294 *
295 * @param fileName
296 * @throws IndexException
297 * @throws IOException
298 */
299 protected void createDataTable( String fileName )
300 throws IndexException, IOException {
301 ShapeFile sf = new ShapeFile( fileName );
302 FeatureType ft = null;
303 try {
304 ft = sf.getFeatureByRecNo( 1 ).getFeatureType();
305 } catch ( Exception e ) {
306 e.printStackTrace();
307 throw new IndexException( e );
308 }
309 sf.close();
310 StringBuffer sb = new StringBuffer( 1000 );
311 sb.append( "CREATE TABLE " ).append( table ).append( '(' );
312 sb.append( "FEATURE_ID " ).append( getDatabaseType( Types.INTEGER ) ).append( "," );
313 PropertyType[] ftp = ft.getProperties();
314 for ( int i = 0; i < ftp.length; i++ ) {
315 if ( ftp[i].getType() == Types.GEOMETRY ) {
316 sb.append( column ).append( ' ' );
317 } else {
318 sb.append( ftp[i].getName().getLocalName() ).append( ' ' );
319 }
320 sb.append( getDatabaseType( ftp[i].getType() ) );
321 if ( i < ftp.length - 1 ) {
322 sb.append( ", " );
323 }
324 }
325 sb.append( ')' );
326
327 Connection con = null;
328 DBConnectionPool pool = null;
329 try {
330 pool = DBConnectionPool.getInstance();
331 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
332 jdbc.getPassword() );
333
334 Statement stmt = con.createStatement();
335 LOG.logDebug( sb.toString() );
336 stmt.execute( sb.toString() );
337 stmt.close();
338 } catch ( Exception e ) {
339 e.printStackTrace();
340 throw new IndexException( "could not create node definition at database", e );
341 } finally {
342 try {
343 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
344 jdbc.getPassword() );
345 } catch ( Exception e1 ) {
346 e1.printStackTrace();
347 }
348 }
349 }
350
351 /**
352 * imports a shape into the database and builds a quadtree on it
353 *
354 * @param fileName
355 * @throws Exception
356 * @throws IOException
357 */
358 public void importShape( String fileName )
359 throws Exception, IOException {
360
361 createDataTable( fileName );
362
363 int qtid = initQuadtree( fileName );
364
365 insertIndexMetadata( qtid );
366
367 qt = new DBQuadtree( qtid, indexName, jdbc );
368
369 ShapeFile sf = new ShapeFile( fileName );
370
371 int b = sf.getRecordNum() / 100;
372 if ( b == 0 )
373 b = 1;
374 int k = 0;
375 Envelope sfEnv = sf.getFileMBR();
376 for ( int i = 0; i < sf.getRecordNum(); i++ ) {
377 Feature feat = sf.getFeatureByRecNo( i + 1 );
378 if ( i % b == 0 ) {
379 System.out.println( k + "%" );
380 k++;
381 }
382 if ( i % 200 == 0 ) {
383 System.gc();
384 }
385 Envelope env = feat.getDefaultGeometryPropertyValue().getEnvelope();
386 LOG.logDebug( i + " --- " + env );
387 if ( env == null ) {
388 // must be a point geometry
389 Point point = (Point) feat.getDefaultGeometryPropertyValue();
390 double w = sfEnv.getWidth() / 1000;
391 double h = sfEnv.getHeight() / 1000;
392 env = GeometryFactory.createEnvelope( point.getX() - w / 2d, point.getY() - h / 2d,
393 point.getX() + w / 2d, point.getY() + h / 2d,
394 null );
395 }
396 int id = i;
397 qt.insert( id, env );
398 storeFeature( feat, id, jdbc );
399 }
400 LOG.logInfo( " finished!" );
401 sf.close();
402 }
403
404 /**
405 * appends the features of a shape to an existing datatable and inserts references into the
406 * assigned quadtree table.
407 * <p>
408 * you have to consider that the quadtree is just valid for a defined area. if the features to
409 * append exceeds this area the quadtree has to be rebuilded.
410 * </p>
411 *
412 * @param fileName
413 * @throws Exception
414 * @throws IOException
415 */
416 public void appendShape( String fileName )
417 throws Exception, IOException {
418
419 ShapeFile sf = new ShapeFile( fileName );
420
421 int b = sf.getRecordNum() / 100;
422 if ( b == 0 )
423 b = 1;
424 int k = 0;
425 qt = getQuadtree();
426 Envelope sfEnv = sf.getFileMBR();
427
428 int cnt = getMaxIdValue();
429
430 for ( int i = 0; i < sf.getRecordNum(); i++ ) {
431 Feature feat = sf.getFeatureByRecNo( i + 1 );
432 if ( i % b == 0 ) {
433 System.out.println( k + "%" );
434 k++;
435 }
436 if ( i % 200 == 0 ) {
437 System.gc();
438 }
439 Envelope env = feat.getDefaultGeometryPropertyValue().getEnvelope();
440 if ( env == null ) {
441 // must be a point geometry
442 Point point = (Point) feat.getDefaultGeometryPropertyValue();
443 double w = sfEnv.getWidth() / 1000;
444 double h = sfEnv.getHeight() / 1000;
445 env = GeometryFactory.createEnvelope( point.getX() - w / 2d, point.getY() - h / 2d,
446 point.getX() + w / 2d, point.getY() + h / 2d,
447 null );
448 }
449 int id = cnt + i + 1;
450 qt.insert( id, env );
451 storeFeature( feat, id, jdbc );
452 }
453 LOG.logInfo( " finished!" );
454 sf.close();
455 }
456
457 /**
458 * returns the maximum ID of the data table
459 *
460 * @return the maximum ID of the data table
461 * @throws IndexException
462 */
463 private int getMaxIdValue()
464 throws IndexException {
465 String sql = "SELECT MAX( FEATURE_ID ) FROM " + table;
466
467 Connection con = null;
468 DBConnectionPool pool = null;
469 Statement stmt = null;
470 int maxId = 0;
471 try {
472 pool = DBConnectionPool.getInstance();
473 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
474 jdbc.getPassword() );
475
476 stmt = con.createStatement();
477 LOG.logDebug( sql );
478 ResultSet rs = stmt.executeQuery( sql );
479 if ( rs.next() ) {
480 maxId = rs.getInt( 1 );
481 }
482 } catch ( Exception e ) {
483 e.printStackTrace();
484 throw new IndexException( "could not read max( Faeture_Id ) from table: " + table );
485 } finally {
486 try {
487 stmt.close();
488 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
489 jdbc.getPassword() );
490 } catch ( Exception e1 ) {
491 e1.printStackTrace();
492 }
493 }
494
495 return maxId;
496 }
497
498 /**
499 * creates a table that will store the nodes assigned to a specific quadtree index.
500 *
501 * @param indexTable
502 */
503 protected void createIndexTable( String indexTable ) {
504 StringBuffer sb = new StringBuffer( 2000 );
505 sb.append( "CREATE TABLE " ).append( indexTable ).append( " ( " );
506 try {
507 sb.append( "ID " ).append( getDatabaseType( Types.VARCHAR ) ).append( " NOT NULL," );
508 } catch ( IndexException neverhappens ) {
509 }
510 sb.append( "minx float NOT NULL," );
511 sb.append( "miny float NOT NULL," );
512 sb.append( "maxx float NOT NULL," );
513 sb.append( "maxy float NOT NULL," );
514 sb.append( "FK_SUBNODE1 varchar(150)," );
515 sb.append( "FK_SUBNODE2 varchar(150)," );
516 sb.append( "FK_SUBNODE3 varchar(150)," );
517 sb.append( "FK_SUBNODE4 varchar(150) )" );
518
519 StringBuffer sb2 = new StringBuffer( 1000 );
520 sb2.append( "CREATE TABLE " ).append( indexName ).append( "_ITEM ( " );
521 try {
522 sb2.append( "FK_QTNODE " ).append( getDatabaseType( Types.VARCHAR ) ).append(
523 " NOT NULL," );
524 sb2.append( "FK_ITEM " ).append( getDatabaseType( Types.INTEGER ) ).append(
525 " NOT NULL )" );
526 } catch ( IndexException neverhappens ) {
527 }
528
529 Connection con = null;
530 DBConnectionPool pool = null;
531 try {
532 pool = DBConnectionPool.getInstance();
533 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
534 jdbc.getPassword() );
535
536 Statement stmt = con.createStatement();
537 stmt.execute( sb.toString() );
538 stmt.close();
539
540 stmt = con.createStatement();
541 stmt.execute( sb2.toString() );
542 stmt.close();
543 } catch ( Exception e ) {
544 // throw new IndexException( "could not create node definition at database", e );
545 } finally {
546 try {
547 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
548 jdbc.getPassword() );
549 } catch ( Exception e1 ) {
550 e1.printStackTrace();
551 }
552 }
553 }
554
555 }
556
557 /***************************************************************************************************
558 * <code>
559 Changes to this class. What the people have been up to:
560
561 $Log$
562 Revision 1.4 2007/03/06 13:11:17 wanhoff
563 Fixed Javadoc (@see, @throws)
564
565 Revision 1.3 2007/02/26 14:10:20 poth
566 bug fix - missing call for ShapeFile.close operation added
567
568 Revision 1.2 2007/01/26 14:37:03 wanhoff
569 fixed Javadoc @return tag and footer
570
571 Revision 1.1 2006/10/20 07:56:00 poth
572 core methods extracted to interfaces
573
574 Revision 1.2 2006/07/26 12:58:47 poth
575 implementation of appendShape method
576
577 Revision 1.1 2006/07/26 12:43:12 poth
578 new quadtree manager using integer as datatype for object IDs
579
580 Revision 1.33 2006/07/20 12:30:01 poth
581 *** empty log message ***
582
583 Revision 1.32 2006/07/18 14:50:45 poth
584 *** empty log message ***
585
586 Revision 1.31 2006/07/10 11:44:56 poth
587 log statements inserted
588
589 Revision 1.30 2006/06/12 10:59:49 schmitz
590 Updated the Quadtree framework to work with INGRES database backends.
591
592 Revision 1.29 2006/05/18 14:08:54 poth
593 file comments completed
594
595 Revision 1.28 2006/05/16 09:01:45 poth
596 Ingres identification adapted
597
598 Revision 1.27 2006/05/15 19:13:39 poth
599 support for Ingres added
600
601 Revision 1.26 2006/05/12 15:26:05 poth
602 *** empty log message ***
603
604 Revision 1.25 2006/05/12 06:46:23 poth
605 *** empty log message ***
606
607 Revision 1.24 2006/05/11 16:37:35 poth
608 *** empty log message ***
609
610 Revision 1.23 2006/05/11 13:26:31 poth
611 *** empty log message ***
612
613 Revision 1.22 2006/05/11 08:02:14 poth
614 *** empty log message ***
615
616 Revision 1.21 2006/04/13 07:49:10 poth
617 *** empty log message ***
618
619 Revision 1.20 2006/04/06 20:25:31 poth
620 *** empty log message ***
621
622 Revision 1.19 2006/03/30 21:20:28 poth
623 *** empty log message ***
624
625 Revision 1.18 2006/01/31 16:23:14 mschneider
626 Changes due to refactoring of org.deegree.model.feature package.
627
628 Revision 1.17 2006/01/25 10:26:24 poth
629 *** empty log message ***
630
631 Revision 1.16 2006/01/16 20:36:40 poth
632 *** empty log message ***
633
634 Revision 1.15 2006/01/08 14:09:35 poth
635 *** empty log message ***
636
637 Revision 1.14 2005/12/18 19:06:30 poth
638 no message
639
640 Revision 1.13 2005/12/06 13:45:20 poth
641 System.out.println substituted by logging api
642
643 Revision 1.12 2005/12/04 19:21:09 poth
644 no message
645
646 Revision 1.11 2005/11/21 18:42:10 mschneider
647 Refactoring due to changes in Feature class.
648
649 Revision 1.10 2005/11/18 08:47:35 deshmukh
650 Geometry cast replaced
651 Revision
652
653 </code>
654 **************************************************************************************************/