001 //$HeadURL: svn+ssh://rbezema@svn.wald.intevation.org/deegree/base/tags/2.1/src/org/deegree/io/quadtree/DBQuadtreeManager.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 import java.util.UUID;
053
054 import org.deegree.datatypes.Types;
055 import org.deegree.framework.log.ILogger;
056 import org.deegree.framework.log.LoggerFactory;
057 import org.deegree.framework.util.StringTools;
058 import org.deegree.io.DBConnectionPool;
059 import org.deegree.io.JDBCConnection;
060 import org.deegree.io.shpapi.ShapeFile;
061 import org.deegree.model.feature.Feature;
062 import org.deegree.model.feature.schema.FeatureType;
063 import org.deegree.model.feature.schema.PropertyType;
064 import org.deegree.model.spatialschema.Envelope;
065 import org.deegree.model.spatialschema.GMLGeometryAdapter;
066 import org.deegree.model.spatialschema.Geometry;
067 import org.deegree.model.spatialschema.GeometryFactory;
068 import org.deegree.model.spatialschema.Point;
069
070 /**
071 * Access control to a quadtree for managing spatial indizes stored in a
072 * usual database.
073 *
074 * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
075 * @author last edited by: $Author: apoth $
076 *
077 * @version $Revision: 7786 $, $Date: 2007-07-19 09:37:19 +0200 (Do, 19 Jul 2007) $
078 */
079 public class DBQuadtreeManager {
080
081 private static final ILogger LOG = LoggerFactory.getLogger( DBQuadtreeManager.class );
082
083 protected JDBCConnection jdbc = null;
084
085 protected String table = null;
086
087 protected String column = null;
088
089 protected String owner = null;
090
091 protected String indexName = null;
092
093 protected int maxDepth = 6;
094
095 protected Quadtree qt = null;
096
097 protected Envelope envelope = null;
098
099 protected String backend = null;
100
101 /**
102 *
103 */
104 protected void checkForBackend() {
105 String driver = jdbc.getDriver();
106 // find out which database is used
107 if ( driver.toUpperCase().indexOf( "POSTGRES" ) > -1 ) {
108 backend = "POSTGRES";
109 } else if ( driver.toUpperCase().indexOf( "SQLSERVER" ) > -1 ) {
110 backend = "SQLSERVER";
111 } else if ( driver.toUpperCase().indexOf( "INGRES" ) > -1
112 || driver.equals( "ca.edbc.jdbc.EdbcDriver" ) ) {
113 backend = "INGRES";
114 } else if ( driver.toUpperCase().indexOf( "HSQLDB" ) > -1 ) {
115 backend = "HSQLDB";
116 } else {
117 backend = "GENERICSQL";
118 }
119 }
120
121 /**
122 * @param jdbc
123 * database connection info
124 * @param indexName
125 * this name will be used to create the table that stores the nodes of a specific
126 * quadtree
127 * @param table
128 * name of table the index shall be created for
129 * @param column
130 * name of column the index shall be created for
131 * @param owner
132 * owner of the table (optional, database user will be used if set to null )
133 * @param maxDepth
134 * max depth of the generated quadtree (default = 6 if a value < 2 will be passed)
135 */
136 public DBQuadtreeManager( JDBCConnection jdbc, String indexName, String table, String column,
137 String owner, int maxDepth ) {
138 this.jdbc = jdbc;
139 this.table = table.trim();
140 this.column = column.trim();
141 this.indexName = indexName.trim();
142 if ( owner == null ) {
143 this.owner = jdbc.getUser();
144 } else {
145 this.owner = owner;
146 }
147 if ( maxDepth > 1 ) {
148 this.maxDepth = maxDepth;
149 }
150
151 checkForBackend();
152
153 createIndexTable( indexName );
154 }
155
156 /**
157 *
158 * @param driver
159 * database connection driver
160 * @param logon
161 * database connection logon
162 * @param user
163 * database user
164 * @param password
165 * database user's password
166 * @param encoding
167 * character encoding to be used (if possible)
168 * @param indexName
169 * this name will be used to create the table that stores the nodes of a specific
170 * quadtree
171 * @param table
172 * name of table the index shall be created for
173 * @param column
174 * name of column the index shall be created for
175 * @param owner
176 * owner of the table (optional, database user will be used if set to null )
177 * @param maxDepth
178 * max depth of the generated quadtree (default = 6 if a value < 2 will be passed)
179 */
180 public DBQuadtreeManager( String driver, String logon, String user, String password,
181 String encoding, String indexName, String table, String column,
182 String owner, int maxDepth ) {
183 jdbc = new JDBCConnection( driver, logon, user, password, null, encoding, null );
184 this.table = table.trim();
185 this.column = column.trim();
186 this.indexName = indexName.trim();
187 if ( owner == null ) {
188 this.owner = user;
189 } else {
190 this.owner = owner;
191 }
192 if ( maxDepth > 1 ) {
193 this.maxDepth = maxDepth;
194 }
195
196 checkForBackend();
197
198 createIndexTable( indexName );
199 }
200
201 /**
202 * initializes a QuadtreeManager to access an alread existing Quadtree
203 *
204 * @param jdbc
205 * database connection info
206 * @param table
207 * name of table the index shall be created for
208 * @param column
209 * name of column the index shall be created for
210 * @param owner
211 * owner of the table (optional, database user will be used if set to null )
212 */
213 public DBQuadtreeManager( JDBCConnection jdbc, String table, String column, String owner ) {
214 this.jdbc = jdbc;
215 this.table = table.trim();
216 this.column = column.trim();
217 if ( owner == null ) {
218 this.owner = jdbc.getUser();
219 } else {
220 this.owner = owner;
221 }
222
223 checkForBackend();
224 }
225
226 /**
227 * initializes a QuadtreeManager to access an alread existing Quadtree
228 *
229 * @param driver
230 * database connection driver
231 * @param logon
232 * database connection logon
233 * @param user
234 * database user
235 * @param password
236 * database user's password
237 * @param encoding
238 * character encoding to be used (if possible)
239 * @param table
240 * name of table the index shall be created for
241 * @param column
242 * name of column the index shall be created for
243 * @param owner
244 * owner of the table (optional, database user will be used if set to null )
245 */
246 public DBQuadtreeManager( String driver, String logon, String user, String password,
247 String encoding, String table, String column, String owner ) {
248 jdbc = new JDBCConnection( driver, logon, user, password, null, encoding, null );
249 this.table = table.trim();
250 this.column = column.trim();
251 if ( owner == null ) {
252 this.owner = user;
253 } else {
254 this.owner = owner;
255 }
256
257 checkForBackend();
258 }
259
260 /**
261 * loads the metadata of a Index from the TAB_DEEGREE_IDX table
262 *
263 * @return FK to the index
264 * @throws IndexException
265 */
266 private int loadIndexMetadata()
267 throws IndexException {
268 int fk_indexTree = -1;
269 Connection con = null;
270 DBConnectionPool pool = null;
271 try {
272 pool = DBConnectionPool.getInstance();
273 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
274 jdbc.getPassword() );
275
276 StringBuffer sb = new StringBuffer( 200 );
277 sb.append( "Select INDEX_NAME, FK_INDEXTREE from TAB_DEEGREE_IDX where " );
278 sb.append( "column_name = '" ).append( column ).append( "' AND " );
279 sb.append( "table_name = '" ).append( table ).append( "' AND " );
280 sb.append( "owner = '" ).append( owner ).append( "'" );
281
282 Statement stmt = con.createStatement();
283 ResultSet rs = stmt.executeQuery( sb.toString() );
284
285 if ( rs.next() ) {
286 indexName = rs.getString( "INDEX_NAME" );
287 fk_indexTree = rs.getInt( "FK_INDEXTREE" );
288 } else {
289 throw new IndexException( "could not read index metadata" );
290 }
291 rs.close();
292 stmt.close();
293 } catch ( Exception e ) {
294 throw new IndexException( "could not load quadtree definition from database", e );
295 } finally {
296 try {
297 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
298 jdbc.getPassword() );
299 } catch ( Exception e1 ) {
300 e1.printStackTrace();
301 }
302 }
303 return fk_indexTree;
304 }
305
306 /**
307 * returns the current Quadtree
308 *
309 * @return the current Quadtree
310 * @throws IndexException
311 */
312 public Quadtree getQuadtree()
313 throws IndexException {
314 if ( qt == null ) {
315 qt = loadQuadtree();
316 }
317 return qt;
318 }
319
320 /**
321 * loads an already existing quadtree
322 *
323 * @return
324 * @throws IndexException
325 */
326 protected Quadtree loadQuadtree()
327 throws IndexException {
328 int fk_index = loadIndexMetadata();
329 return new DBQuadtree( fk_index, indexName, jdbc );
330 }
331
332 /**
333 * stores one feature into the defined table
334 *
335 * @param feature
336 * @param jdbc
337 * @throws Exception
338 */
339 private void storeFeature( Feature feature, String id, JDBCConnection jdbc )
340 throws Exception {
341
342 Connection con = null;
343 DBConnectionPool pool = null;
344
345 FeatureType ft = feature.getFeatureType();
346 PropertyType[] ftp = ft.getProperties();
347 try {
348 pool = DBConnectionPool.getInstance();
349 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
350 jdbc.getPassword() );
351
352 StringBuffer sb = new StringBuffer( 100 );
353 sb.append( "INSERT INTO " ).append( table ).append( '(' );
354 sb.append( "FEATURE_ID," );
355 for ( int i = 0; i < ftp.length; i++ ) {
356 if ( ftp[i].getType() == Types.GEOMETRY ) {
357 sb.append( column ).append( ' ' );
358 } else {
359 sb.append( ftp[i].getName().getLocalName() );
360 }
361 if ( i < ftp.length - 1 ) {
362 sb.append( ", " );
363 }
364 }
365 sb.append( ") VALUES (?," );
366 for ( int i = 0; i < ftp.length; i++ ) {
367 sb.append( '?' );
368 if ( i < ftp.length - 1 ) {
369 sb.append( ", " );
370 }
371 }
372 sb.append( ')' );
373 LOG.logDebug( "SQL for inser feature: " + sb );
374
375 PreparedStatement stmt = con.prepareStatement( sb.toString() );
376 stmt.setString( 1, id );
377 for ( int i = 0; i < ftp.length; i++ ) {
378 Object o = null;
379 if ( feature.getProperties( ftp[i].getName() ) != null ) {
380 if ( feature.getProperties( ftp[i].getName() ).length > 0 ) {
381 o = feature.getProperties( ftp[i].getName() )[0].getValue();
382 }
383 }
384 if ( o == null ) {
385 stmt.setNull( i + 2, ftp[i].getType() );
386 } else {
387 switch ( ftp[i].getType() ) {
388 case Types.CHAR:
389 case Types.VARCHAR:
390 stmt.setString( i + 2, o.toString() );
391 break;
392 case Types.SMALLINT:
393 case Types.TINYINT:
394 case Types.INTEGER:
395 case Types.BIGINT:
396 stmt.setInt( i + 2, (int) Double.parseDouble( o.toString() ) );
397 break;
398 case Types.DOUBLE:
399 case Types.FLOAT:
400 case Types.DECIMAL:
401 case Types.NUMERIC:
402 stmt.setFloat( i + 2, Float.parseFloat( o.toString() ) );
403 break;
404 case Types.DATE:
405 case Types.TIME:
406 case Types.TIMESTAMP:
407 stmt.setDate( i + 2, (Date) o );
408 break;
409 case Types.GEOMETRY: {
410 StringBuffer gs = GMLGeometryAdapter.export( (Geometry) o );
411 String s = StringTools.replace(
412 gs.toString(),
413 ">",
414 " xmlns:gml=\"http://www.opengis.net/gml\">",
415 false );
416 if ( backend.equals( "POSTGRES" ) || backend.equals( "HSQLDB" ) ) {
417 stmt.setString( i + 2, s );
418 } else if ( backend.equals( "INGRES" ) ) {
419 stmt.setObject( i + 2, new StringReader( s ) );
420 } else {
421 stmt.setObject( i + 2, s.getBytes() );
422 }
423 break;
424 }
425 default: {
426 LOG.logWarning( "unsupported type: " + ftp[i].getType() );
427 }
428 }
429 }
430 }
431
432 stmt.execute();
433 stmt.close();
434
435 } catch ( Exception e ) {
436 e.printStackTrace();
437 throw new IndexException( "could not insert feature into database", e );
438 } finally {
439 try {
440 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
441 jdbc.getPassword() );
442 } catch ( Exception e1 ) {
443 e1.printStackTrace();
444 }
445 }
446 }
447
448 /**
449 * initializes the root node of the quadtree
450 *
451 * @param fileName
452 * @throws IndexException
453 * @throws IOException
454 *
455 */
456 protected void initRootNode( String fileName )
457 throws IndexException, IOException {
458 ShapeFile sf = new ShapeFile( fileName );
459 if ( envelope == null ) {
460 envelope = sf.getFileMBR();
461 }
462 envelope = envelope.getBuffer( envelope.getWidth() / 20 );
463 LOG.logInfo( "root envelope: " + envelope );
464 sf.close();
465 new DBNode( "1", envelope, null, indexName, jdbc, 1 );
466 }
467
468 /**
469 * before importing a shape a user may set an envelope for the quadtree to bee created that is
470 * different from the one of the shape by calling this method. Notice: calling this method does
471 * not have any effect when calling
472 *
473 * @see #appendShape(String) method.
474 * @param envelope
475 */
476 public void setRootEnvelope( Envelope envelope ) {
477 this.envelope = envelope;
478 }
479
480 /**
481 * initializes a new Quadtree by adding a row into table TAB_QUADTREE and into TAB_QTNODE (->
482 * root node)
483 *
484 * @param fileName
485 *
486 * @return
487 * @throws IndexException
488 * @throws IOException
489 */
490 protected int initQuadtree( String fileName )
491 throws IndexException, IOException {
492
493 initRootNode( fileName );
494
495 Connection con = null;
496 DBConnectionPool pool = null;
497 int id = -1;
498 try {
499 pool = DBConnectionPool.getInstance();
500 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
501 jdbc.getPassword() );
502
503 StringBuffer sb = new StringBuffer( 100 );
504 sb.append( "INSERT INTO TAB_QUADTREE (" );
505 if ( backend.equals( "INGRES" ) || backend.equals( "HSQLDB" ) ) {
506 sb.append( "ID, " );
507 }
508 sb.append( "FK_ROOT, DEPTH ) VALUES ( " );
509 if ( backend.equals( "INGRES" ) || backend.equals( "HSQLDB" ) ) {
510 Statement stm = con.createStatement();
511 ResultSet rs = stm.executeQuery( "SELECT MAX(ID) FROM TAB_QUADTREE" );
512 rs.next();
513 int myid = rs.getInt( 1 ) + 1;
514 sb.append( myid + ", " );
515 }
516 sb.append( " '1', ? ) " );
517
518 PreparedStatement stmt = con.prepareStatement( sb.toString() );
519 stmt.setInt( 1, maxDepth );
520 stmt.execute();
521 stmt.close();
522 Statement stm = con.createStatement();
523 ResultSet rs = stm.executeQuery( "select max(ID) from TAB_QUADTREE" );
524 rs.next();
525 id = rs.getInt( 1 );
526 if ( id < 0 ) {
527 throw new IndexException( "could not read ID of quadtree from database." );
528 }
529 } catch ( Exception e ) {
530 e.printStackTrace();
531 throw new IndexException( "could not create node definition at database", e );
532 } finally {
533 try {
534 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
535 jdbc.getPassword() );
536 } catch ( Exception e1 ) {
537 e1.printStackTrace();
538 }
539 }
540 return id;
541 }
542
543 /**
544 *
545 * @param fk_indexTree
546 * @return
547 * @throws IndexException
548 */
549 public void insertIndexMetadata( int fk_indexTree )
550 throws IndexException {
551
552 Connection con = null;
553 DBConnectionPool pool = null;
554 try {
555 pool = DBConnectionPool.getInstance();
556 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
557 jdbc.getPassword() );
558
559 StringBuffer sb = new StringBuffer( 100 );
560 sb.append( "INSERT INTO TAB_DEEGREE_IDX ( " );
561 if ( backend.equals( "INGRES" ) || backend.equals( "HSQLDB" ) ) {
562 sb.append( "ID, " );
563 }
564 sb.append( "column_name, table_name, " );
565 sb.append( "owner, INDEX_NAME, FK_indexTree ) " );
566 sb.append( "VALUES ( " );
567 if ( backend.equals( "INGRES" ) || backend.equals( "HSQLDB" ) ) {
568 Statement stm = con.createStatement();
569 ResultSet rs = stm.executeQuery( "SELECT MAX(ID) FROM TAB_QUADTREE" );
570 rs.next();
571 int myid = rs.getInt( 1 ) + 1;
572 sb.append( myid + ", " );
573 }
574 sb.append( "?, ?, ?, ?, ? ) " );
575 PreparedStatement stmt = con.prepareStatement( sb.toString() );
576 stmt.setString( 1, column );
577 stmt.setString( 2, table );
578 stmt.setString( 3, owner );
579 stmt.setString( 4, indexName );
580 stmt.setInt( 5, fk_indexTree );
581
582 stmt.execute();
583 stmt.close();
584 } catch ( Exception e ) {
585 LOG.logError( e.getMessage(), e );
586 throw new IndexException( "could not create node definition at database", e );
587 } finally {
588 try {
589 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
590 jdbc.getPassword() );
591 } catch ( Exception e1 ) {
592 e1.printStackTrace();
593 }
594 }
595 }
596
597 /**
598 * creates table the shape data shall be stored
599 *
600 * @param fileName
601 * @throws IndexException
602 * @throws IOException
603 */
604 protected void createDataTable( String fileName )
605 throws IndexException, IOException {
606 ShapeFile sf = new ShapeFile( fileName );
607 FeatureType ft = null;
608 try {
609 ft = sf.getFeatureByRecNo( 1 ).getFeatureType();
610 } catch ( Exception e ) {
611 e.printStackTrace();
612 throw new IndexException( e );
613 }
614 sf.close();
615 StringBuffer sb = new StringBuffer( 1000 );
616 sb.append( "CREATE TABLE " ).append( table ).append( '(' );
617 sb.append( "FEATURE_ID VARCHAR(50)," );
618 PropertyType[] ftp = ft.getProperties();
619 for ( int i = 0; i < ftp.length; i++ ) {
620 if ( ftp[i].getType() == Types.GEOMETRY ) {
621 sb.append( column ).append( ' ' );
622 } else {
623 sb.append( ftp[i].getName().getLocalName() ).append( ' ' );
624 }
625 sb.append( getDatabaseType( ftp[i].getType() ) );
626 if ( i < ftp.length - 1 ) {
627 sb.append( ", " );
628 }
629 }
630 sb.append( ')' );
631
632 Connection con = null;
633 DBConnectionPool pool = null;
634 try {
635 pool = DBConnectionPool.getInstance();
636 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
637 jdbc.getPassword() );
638
639 Statement stmt = con.createStatement();
640 LOG.logDebug( sb.toString() );
641 stmt.execute( sb.toString() );
642 stmt.close();
643 } catch ( Exception e ) {
644 e.printStackTrace();
645 throw new IndexException( "could not create node definition at database", e );
646 } finally {
647 try {
648 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
649 jdbc.getPassword() );
650 } catch ( Exception e1 ) {
651 e1.printStackTrace();
652 }
653 }
654 }
655
656 /**
657 * returns the type name for a generic type code as used by SQLServer
658 *
659 * @param dataTypeCode
660 * @return the type name for a generic type code as used by SQLServer
661 * @throws IndexException
662 */
663 protected String getDatabaseType( int dataTypeCode )
664 throws IndexException {
665 String driver = jdbc.getDriver();
666 String backend = null;
667 if ( driver.toUpperCase().indexOf( "POSTGRES" ) > -1 ) {
668 backend = "POSTGRES";
669 } else if ( driver.toUpperCase().indexOf( "SQLSERVER" ) > -1 ) {
670 backend = "SQLSERVER";
671 } else if ( driver.toUpperCase().indexOf( "INGRES" ) > -1
672 || driver.equals( "ca.edbc.jdbc.EdbcDriver" ) ) {
673 backend = "INGRES";
674 } else if ( driver.toUpperCase().indexOf( "HSQLDB" ) > -1 ) {
675 backend = "HSQLDB";
676 } else {
677 backend = "GENERICSQL";
678 }
679 String type = null;
680
681 switch ( dataTypeCode ) {
682 case Types.CHAR:
683 case Types.VARCHAR:
684 type = DBQuadtreeDataTypes.getString( backend + ".string" );
685 break;
686 case Types.SMALLINT:
687 case Types.TINYINT:
688 case Types.INTEGER:
689 case Types.BIGINT:
690 type = DBQuadtreeDataTypes.getString( backend + ".integer" );
691 break;
692 case Types.DOUBLE:
693 case Types.FLOAT:
694 case Types.DECIMAL:
695 case Types.NUMERIC:
696 type = DBQuadtreeDataTypes.getString( backend + ".float" );
697 break;
698 case Types.DATE:
699 case Types.TIME:
700 case Types.TIMESTAMP:
701 type = DBQuadtreeDataTypes.getString( backend + ".datetime" );
702 break;
703 case Types.GEOMETRY:
704 type = DBQuadtreeDataTypes.getString( backend + ".geometry" );
705 break;
706 default:
707 throw new IndexException( "unknown data type code: " + dataTypeCode );
708 }
709
710 return type;
711 }
712
713 /**
714 * imports a shape into the database and builds a quadtree on it
715 *
716 * @param fileName
717 * @throws Exception
718 * @throws IOException
719 */
720 public void importShape( String fileName )
721 throws Exception, IOException {
722
723 createDataTable( fileName );
724
725 int qtid = initQuadtree( fileName );
726
727 insertIndexMetadata( qtid );
728
729 qt = new DBQuadtree( qtid, indexName, jdbc );
730
731 ShapeFile sf = new ShapeFile( fileName );
732
733 int b = sf.getRecordNum() / 100;
734 if ( b == 0 )
735 b = 1;
736 int k = 0;
737 Envelope sfEnv = sf.getFileMBR();
738
739 for ( int i = 0; i < sf.getRecordNum(); i++ ) {
740 Feature feat = sf.getFeatureByRecNo( i + 1 );
741 if ( i % b == 0 ) {
742 System.out.println( k + "%" );
743 k++;
744 }
745 if ( i % 200 == 0 ) {
746 System.gc();
747 }
748 Envelope env = feat.getDefaultGeometryPropertyValue().getEnvelope();
749 LOG.logDebug( i + " --- " + env );
750 if ( env == null ) {
751 // must be a point geometry
752 Point point = (Point) feat.getDefaultGeometryPropertyValue();
753 double w = sfEnv.getWidth() / 1000;
754 double h = sfEnv.getHeight() / 1000;
755 env = GeometryFactory.createEnvelope( point.getX() - w / 2d, point.getY() - h / 2d,
756 point.getX() + w / 2d, point.getY() + h / 2d,
757 null );
758 }
759 String id = UUID.randomUUID().toString();
760 qt.insert( id, env );
761 storeFeature( feat, id, jdbc );
762 }
763 LOG.logInfo( " finished!" );
764 sf.close();
765 }
766
767 /**
768 * appends the features of a shape to an existing datatable and inserts references into the
769 * assigned quadtree table.
770 * <p>
771 * you have to consider that the quadtree is just valid for a defined area. if the features to
772 * append exceeds this area the quadtree has to be rebuilded.
773 * </p>
774 *
775 * @param fileName
776 * @throws Exception
777 * @throws IOException
778 */
779 public void appendShape( String fileName )
780 throws Exception, IOException {
781
782 ShapeFile sf = new ShapeFile( fileName );
783
784 int b = sf.getRecordNum() / 100;
785 if ( b == 0 )
786 b = 1;
787 int k = 0;
788 qt = getQuadtree();
789 Envelope sfEnv = sf.getFileMBR();
790
791 for ( int i = 0; i < sf.getRecordNum(); i++ ) {
792 Feature feat = sf.getFeatureByRecNo( i + 1 );
793 if ( i % b == 0 ) {
794 System.out.println( k + "%" );
795 k++;
796 }
797 if ( i % 200 == 0 ) {
798 System.gc();
799 }
800 Envelope env = feat.getDefaultGeometryPropertyValue().getEnvelope();
801 if ( env == null ) {
802 // must be a point geometry
803 Point point = (Point) feat.getDefaultGeometryPropertyValue();
804 double w = sfEnv.getWidth() / 1000;
805 double h = sfEnv.getHeight() / 1000;
806 env = GeometryFactory.createEnvelope( point.getX() - w / 2d, point.getY() - h / 2d,
807 point.getX() + w / 2d, point.getY() + h / 2d,
808 null );
809 }
810 String id = UUID.randomUUID().toString();
811 qt.insert( id, env );
812 storeFeature( feat, id, jdbc );
813 }
814 LOG.logInfo( " finished!" );
815 sf.close();
816 }
817
818 /**
819 * creates a table that will store the nodes assigned to a specific quadtree index.
820 *
821 * @param indexTable
822 */
823 protected void createIndexTable( String indexTable ) {
824 StringBuffer sb = new StringBuffer( 2000 );
825 sb.append( "CREATE TABLE " ).append( indexTable ).append( " ( " );
826 sb.append( "ID varchar(150) NOT NULL," );
827 sb.append( "minx float NOT NULL," );
828 sb.append( "miny float NOT NULL," );
829 sb.append( "maxx float NOT NULL," );
830 sb.append( "maxy float NOT NULL," );
831 sb.append( "FK_SUBNODE1 varchar(150)," );
832 sb.append( "FK_SUBNODE2 varchar(150)," );
833 sb.append( "FK_SUBNODE3 varchar(150)," );
834 sb.append( "FK_SUBNODE4 varchar(150) )" );
835
836 StringBuffer sb2 = new StringBuffer( 1000 );
837 sb2.append( "CREATE TABLE " ).append( indexName ).append( "_ITEM ( " );
838 sb2.append( "FK_QTNODE varchar(150) NOT NULL," );
839 sb2.append( "FK_ITEM varchar(150) NOT NULL )" );
840
841 Connection con = null;
842 DBConnectionPool pool = null;
843 try {
844 pool = DBConnectionPool.getInstance();
845 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
846 jdbc.getPassword() );
847
848 Statement stmt = con.createStatement();
849 stmt.execute( sb.toString() );
850 stmt.close();
851
852 stmt = con.createStatement();
853 stmt.execute( sb2.toString() );
854 stmt.close();
855 } catch ( Exception e ) {
856 // throw new IndexException( "could not create node definition at database", e );
857 } finally {
858 try {
859 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
860 jdbc.getPassword() );
861 } catch ( Exception e1 ) {
862 e1.printStackTrace();
863 }
864 }
865 }
866
867 }