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.model.coverage.grid.postgres;
040
041 import java.sql.Connection;
042 import java.sql.PreparedStatement;
043 import java.sql.ResultSet;
044 import java.util.ArrayList;
045 import java.util.List;
046
047 import org.deegree.framework.log.ILogger;
048 import org.deegree.framework.log.LoggerFactory;
049 import org.deegree.framework.util.StringTools;
050 import org.deegree.io.DBConnectionPool;
051 import org.deegree.io.JDBCConnection;
052 import org.deegree.io.datastore.sql.postgis.PGgeometryAdapter;
053 import org.deegree.model.coverage.grid.DatabaseIndexAccess;
054 import org.deegree.model.coverage.grid.DatabaseIndexedGCMetadata;
055 import org.deegree.model.crs.CoordinateSystem;
056 import org.deegree.model.spatialschema.Envelope;
057 import org.deegree.model.spatialschema.Geometry;
058 import org.deegree.model.spatialschema.GeometryFactory;
059 import org.deegree.ogcwebservices.wcs.configuration.File;
060 import org.postgis.PGgeometry;
061 import org.postgresql.PGConnection;
062
063 /**
064 *
065 *
066 * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
067 * @author last edited by: $Author: poth $
068 *
069 * @version. $Revision: 6251 $, $Date: 2007-03-19 16:59:28 +0100 (Mo, 19 Mrz 2007) $
070 */
071 public class PGDatabaseIndexAccess implements DatabaseIndexAccess {
072
073 private static final ILogger LOG = LoggerFactory.getLogger( PGDatabaseIndexAccess.class );
074
075 private static final String GEOMETRY_DATATYPE_NAME = "geometry";
076
077 private static final String PG_GEOMETRY_CLASS_NAME = "org.postgis.PGgeometry";
078
079 private static Class pgGeometryClass;
080
081 static {
082 try {
083 pgGeometryClass = Class.forName( PG_GEOMETRY_CLASS_NAME );
084 } catch ( ClassNotFoundException e ) {
085 LOG.logError( "Cannot find class '" + PG_GEOMETRY_CLASS_NAME + "'.", e );
086 }
087 }
088
089 /*
090 * (non-Javadoc)
091 *
092 * @see org.deegree.model.coverage.grid.DatabaseIndexAccess#getFiles(org.deegree.model.coverage.grid.DatabaseIndexedGCMetadata,
093 * org.deegree.model.spatialschema.Envelope, org.deegree.model.crs.CoordinateSystem)
094 */
095 public File[] getFiles( DatabaseIndexedGCMetadata dbigcmd, Envelope envelope, CoordinateSystem crs ) {
096 JDBCConnection jdbc = dbigcmd.getJDBCConnection();
097 DBConnectionPool pool = DBConnectionPool.getInstance();
098 Connection con = null;
099 PreparedStatement stmt = null;
100 try {
101 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
102 PGConnection pgConn = (PGConnection) con;
103 pgConn.addDataType( GEOMETRY_DATATYPE_NAME, pgGeometryClass );
104
105 // get level number
106 String sql = StringTools.concat( 300, "select level from ", dbigcmd.getTable(),
107 "_pyr where minscale < ? AND maxscale >= ?" );
108 LOG.logDebug( sql );
109 LOG.logDebug( "scale ", dbigcmd.getScale() );
110 stmt = con.prepareStatement( sql );
111 stmt.setFloat( 1, dbigcmd.getScale() );
112 stmt.setFloat( 2, dbigcmd.getScale() );
113 ResultSet rs = stmt.executeQuery();
114 if ( !rs.next() ) {
115 // no tiles/level defined for current scale
116 rs.close();
117 return new File[0];
118 }
119 int level = rs.getInt( 1 );
120 rs.close();
121 stmt.close();
122 // get file names (postgis version)
123 sql = StringTools.concat( 300, "select DIR, FILE, BBOX from ", dbigcmd.getTable(),
124 " where level = ? AND BBOX && ?" );
125
126 stmt = con.prepareStatement( sql );
127 stmt.setInt( 1, level );
128 Geometry geom = GeometryFactory.createSurface( envelope, null );
129 stmt.setObject( 2, PGgeometryAdapter.export( geom, -1 ) );
130 LOG.logDebug( sql );
131 LOG.logDebug( "level/bbox ", level + " " + envelope );
132 rs = stmt.executeQuery();
133 List<File> list = new ArrayList<File>( 50 );
134 while ( rs.next() ) {
135 String dir = rs.getString( 1 );
136 String file = rs.getString( 2 );
137 Envelope env = PGgeometryAdapter.wrap( (PGgeometry) rs.getObject( 3 ), crs ).getEnvelope();
138 String fn = StringTools.concat( 256, dbigcmd.getRootDir(), '/', dir, '/', file );
139 list.add( new File( crs, fn, env ) );
140 }
141 LOG.logInfo( "tiles found: ", list.size() );
142 rs.close();
143 return list.toArray( new File[list.size()] );
144 } catch ( Exception e ) {
145 e.printStackTrace();
146 } finally {
147 try {
148 stmt.close();
149 } catch ( Exception e ) {
150 LOG.logWarning( "could not close sql statement" );
151 }
152 try {
153 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
154 } catch ( Exception e ) {
155 LOG.logWarning( "could not release sql connection back to pool" );
156 }
157 }
158 return new File[0];
159 }
160 }