    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.io.databaseloader;
    038
    039    import java.net.URI;
    040    import java.sql.Connection;
    041    import java.sql.PreparedStatement;
    042    import java.sql.ResultSet;
    043    import java.sql.ResultSetMetaData;
    044    import java.sql.SQLException;
    045    import java.util.UUID;
    046
    047    import org.deegree.datatypes.QualifiedName;
    048    import org.deegree.datatypes.Types;
    049    import org.deegree.framework.log.ILogger;
    050    import org.deegree.framework.log.LoggerFactory;
    051    import org.deegree.io.DBConnectionPool;
    052    import org.deegree.io.DBPoolException;
    053    import org.deegree.io.JDBCConnection;
    054    import org.deegree.io.datastore.sql.postgis.PGgeometryAdapter;
    055    import org.deegree.model.crs.CoordinateSystem;
    056    import org.deegree.model.feature.FeatureCollection;
    057    import org.deegree.model.feature.FeatureFactory;
    058    import org.deegree.model.feature.FeatureProperty;
    059    import org.deegree.model.feature.schema.FeatureType;
    060    import org.deegree.model.feature.schema.PropertyType;
    061    import org.deegree.model.spatialschema.Envelope;
    062    import org.deegree.model.spatialschema.GeometryException;
    063    import org.deegree.model.spatialschema.GeometryFactory;
    064    import org.deegree.model.spatialschema.Surface;
    065    import org.deegree.ogcwebservices.wms.configuration.DatabaseDataSource;
    066    import org.postgis.PGboxbase;
    067    import org.postgis.PGgeometry;
    068    import org.postgresql.PGConnection;
    069
    070    /**
    071     * class for loading data as feature collection from a postgis database
    072     *
    073     * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
    074     * @author last edited by: $Author: poth $
    075     *
    076     * @version $Revision: 6251 $, $Date: 2007-03-19 16:59:28 +0100 (Mo, 19 Mrz 2007) $
    077     */
    078    public class PostgisDataLoader {
    079
    080        private static final ILogger LOG = LoggerFactory.getLogger( PostgisDataLoader.class );
    081
    082        private static final String GEOMETRY_DATATYPE_NAME = "geometry";
    083
    084        private static final String BOX3D_DATATYPE_NAME = "box3d";
    085
    086        private static final String PG_GEOMETRY_CLASS_NAME = "org.postgis.PGgeometry";
    087
    088        private static final String PG_BOX3D_CLASS_NAME = "org.postgis.PGbox3d";
    089
    090        private static Class<?> pgGeometryClass;
    091
    092        private static Class<?> pgBox3dClass;
    093
    094        private static URI namespace;
    095        static {
    096            try {
    097                namespace = new URI( "http://www.deegree.org/database" );
    098            } catch ( Exception e ) {
    099                LOG.logError( e.getMessage(), e );
    100            }
    101            try {
    102                pgGeometryClass = Class.forName( PG_GEOMETRY_CLASS_NAME );
    103            } catch ( ClassNotFoundException e ) {
    104                LOG.logError( "Cannot find class '" + PG_GEOMETRY_CLASS_NAME + "'.", e );
    105            }
    106            try {
    107                pgBox3dClass = Class.forName( PG_BOX3D_CLASS_NAME );
    108            } catch ( ClassNotFoundException e ) {
    109                LOG.logError( "Cannot find class '" + PG_BOX3D_CLASS_NAME + "'.", e );
    110            }
    111        }
    112
    113        /**
    114         * @param datasource
    115         * @param envelope
    116         * @param sql
    117         * @return the fc
    118         * @throws Exception
    119         */
    120        public static FeatureCollection load( DatabaseDataSource datasource, Envelope envelope, String sql )
    121                                throws Exception {
    122            return load( datasource, envelope, sql, null );
    123        }
    124
    125        /**
    126         * @param datasource
    127         * @param envelope
    128         * @param sql
    129         * @param extraClauses
    130         * @return the feature collection directly from the db
    131         * @throws Exception
    132         */
    133        public static FeatureCollection load( DatabaseDataSource datasource, Envelope envelope, String sql,
    134                                              String extraClauses )
    135                                throws Exception {
    136            if ( sql == null ) {
    137                sql = datasource.getSqlTemplate();
    138            }
    139
    140            JDBCConnection jdbc = datasource.getJDBCConnection();
    141            PreparedStatement stmt = null;
    142            Connection conn = null;
    143            ResultSet rs = null;
    144            FeatureCollection fc = FeatureFactory.createFeatureCollection( UUID.randomUUID().toString(), 10000 );
    145            try {
    146                CoordinateSystem crs = datasource.getNativeCRS();
    147                conn = acquireConnection( jdbc );
    148                stmt = createPreparedStatement( datasource, envelope, conn, crs, sql, extraClauses );
    149
    150                rs = stmt.executeQuery();
    151
    152                LOG.logDebug( "performing database query: " + sql );
    153                ResultSetMetaData rsmd = rs.getMetaData();
    154                FeatureType featureType = createFeatureType( datasource, datasource.getGeometryFieldName(), rsmd );
    155                int ccnt = rsmd.getColumnCount();
    156                int k = 0;
    157
    158                // read each line from database and create a feature from it
    159                while ( rs.next() ) {
    160                    FeatureProperty[] properties = new FeatureProperty[ccnt];
    161                    for ( int i = 0; i < ccnt; i++ ) {
    162                        String name = rsmd.getColumnName( i + 1 );
    163                        Object value = rs.getObject( i + 1 );
    164                        // if column name equals geometry field name the value read from
    165                        // database must be converted into a deegree geometry
    166                        if ( name.equalsIgnoreCase( datasource.getGeometryFieldName() ) ) {
    167                            value = PGgeometryAdapter.wrap( (PGgeometry) value, crs );
    168                        }
    169                        properties[i] = FeatureFactory.createFeatureProperty( featureType.getPropertyName( i ), value );
    170                    }
    171                    // because feature IDs are not important in case of database datasource
    172                    // it is just 'ID' as prefix plus a number of current row
    173                    fc.add( FeatureFactory.createFeature( "ID" + k++, featureType, properties ) );
    174                }
    175                LOG.logDebug( k + " features loaded from database" );
    176            } catch ( Exception e ) {
    177                LOG.logError( e.getMessage(), e );
    178                throw e;
    179            } finally {
    180                try {
    181                    if ( rs != null ) {
    182                        rs.close();
    183                    }
    184                } catch ( Exception e ) {
    185                    // what to do here anyway
    186                }
    187                try {
    188                    if ( stmt != null ) {
    189                        stmt.close();
    190                    }
    191                } catch ( SQLException e ) {
    192                    // what to do here anyway
    193                }
    194                releaseConnection( jdbc, conn );
    195            }
    196            return fc;
    197        }
    198
    199        /**
    200         *
    201         * @param datasource
    202         * @param envelope
    203         * @return featurecollection loaded from a postgis database
    204         * @throws Exception
    205         */
    206        public static FeatureCollection load( DatabaseDataSource datasource, Envelope envelope )
    207                                throws Exception {
    208            return load( datasource, envelope, null );
    209        }
    210
    211        private static PreparedStatement createPreparedStatement( DatabaseDataSource datasource, Envelope envelope,
    212                                                                  Connection conn, CoordinateSystem crs, String sql,
    213                                                                  String extraClauses )
    214                                throws GeometryException, SQLException {
    215            PreparedStatement stmt;
    216
    217            String nativeCRS = getSRSCode( crs );
    218            String envCRS;
    219            if ( envelope.getCoordinateSystem() != null ) {
    220                envCRS = getSRSCode( envelope.getCoordinateSystem() );
    221            } else {
    222                envCRS = nativeCRS;
    223            }
    224
    225            // use the bbox operator (&&) to filter using the spatial index
    226            PGboxbase box = PGgeometryAdapter.export( envelope );
    227            Surface surface = GeometryFactory.createSurface( envelope, envelope.getCoordinateSystem() );
    228            PGgeometry pggeom = PGgeometryAdapter.export( surface, Integer.parseInt( envCRS ) );
    229            StringBuffer query = new StringBuffer( 1000 );
    230            query.append( " (" );
    231            query.append( datasource.getGeometryFieldName() );
    232            query.append( " && transform(SetSRID( ?, " );
    233            query.append( envCRS );
    234            query.append( "), " );
    235            query.append( nativeCRS );
    236            query.append( ")) AND intersects(" );
    237            query.append( datasource.getGeometryFieldName() );
    238            query.append( ",transform(?, " );
    239            query.append( nativeCRS );
    240            query.append( "))" );
    241
    242            if ( extraClauses != null ) {
    243                query.append( extraClauses );
    244            }
    245
    246            if ( sql.indexOf( "$BBOX" ) == -1 ) {
    247                if ( sql.trim().toUpperCase().endsWith( " WHERE" ) ) {
    248                    LOG.logDebug( "performed SQL: ", sql + query );
    249                    stmt = conn.prepareStatement( sql + query );
    250                } else {
    251                    LOG.logDebug( "performed SQL: ", sql + " AND " + query );
    252                    stmt = conn.prepareStatement( sql + " AND " + query );
    253                }
    254            } else {
    255                if ( sql.substring( 0, sql.indexOf( "$BBOX" ) ).trim().toUpperCase().endsWith( " WHERE" ) ) {
    256                    stmt = conn.prepareStatement( sql.replace( "$BBOX", query ) );
    257                } else {
    258                    stmt = conn.prepareStatement( sql.replace( "$BBOX", " and " + query ) );
    259                }
    260                LOG.logDebug( "performed SQL: ", stmt );
    261            }
    262            stmt.setObject( 1, box, java.sql.Types.OTHER );
    263            stmt.setObject( 2, pggeom, java.sql.Types.OTHER );
    264            return stmt;
    265        }
    266
    267        private static String getSRSCode( CoordinateSystem crs ) {
    268            return crs.getLocalName();
    269        }
    270
    271        /**
    272         *
    273         * @param geometryFiedName
    274         * @param rsmd
    275         * @return {@link FeatureType} created from column names and types
    276         * @throws SQLException
    277         */
    278        private static FeatureType createFeatureType( DatabaseDataSource datasource, String geometryFiedName,
    279                                                      ResultSetMetaData rsmd )
    280                                throws SQLException {
    281            int ccnt = rsmd.getColumnCount();
    282            QualifiedName name = new QualifiedName( datasource.getName().getLocalName(), namespace );
    283            PropertyType[] properties = new PropertyType[ccnt];
    284            for ( int i = 0; i < ccnt; i++ ) {
    285                QualifiedName propName = new QualifiedName( rsmd.getColumnName( i + 1 ), namespace );
    286                LOG.logDebug( "propertyname: ", propName );
    287                int typeCode = getTypeCode( geometryFiedName, rsmd.getColumnName( i + 1 ), rsmd.getColumnType( i + 1 ) );
    288                properties[i] = FeatureFactory.createSimplePropertyType( propName, typeCode, true );
    289            }
    290            return FeatureFactory.createFeatureType( name, false, properties );
    291        }
    292
    293        private static int getTypeCode( String geometryFiedName, String columnName, int columnType ) {
    294            if ( columnName.equalsIgnoreCase( geometryFiedName ) ) {
    295                return Types.GEOMETRY;
    296            }
    297            return columnType;
    298        }
    299
    300        private static void releaseConnection( JDBCConnection jdbc, Connection conn ) {
    301            try {
    302                DBConnectionPool pool = DBConnectionPool.getInstance();
    303                pool.releaseConnection( conn, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
    304            } catch ( DBPoolException e ) {
    305                // what to do here anyway
    306            }
    307        }
    308
    309        private static Connection acquireConnection( JDBCConnection jdbc )
    310                                throws DBPoolException, SQLException {
    311            Connection conn;
    312            DBConnectionPool pool = DBConnectionPool.getInstance();
    313            conn = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
    314            PGConnection pgConn = (PGConnection) conn;
    315            pgConn.addDataType( GEOMETRY_DATATYPE_NAME, pgGeometryClass );
    316            pgConn.addDataType( BOX3D_DATATYPE_NAME, pgBox3dClass );
    317            return conn;
    318        }
    319
    320    }