    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    }