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