    001    //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.4_testing/src/org/deegree/io/datastore/sql/postgis/PostGISDatastore.java $
    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.datastore.sql.postgis;
    038
    039    import static java.lang.Integer.parseInt;
    040    import static org.deegree.model.crs.CRSFactory.create;
    041
    042    import java.io.IOException;
    043    import java.io.InputStream;
    044    import java.sql.Connection;
    045    import java.sql.ResultSet;
    046    import java.sql.SQLException;
    047    import java.sql.Statement;
    048    import java.util.HashMap;
    049    import java.util.Map;
    050    import java.util.Properties;
    051
    052    import org.deegree.framework.log.ILogger;
    053    import org.deegree.framework.log.LoggerFactory;
    054    import org.deegree.i18n.Messages;
    055    import org.deegree.io.JDBCConnection;
    056    import org.deegree.io.datastore.Datastore;
    057    import org.deegree.io.datastore.DatastoreException;
    058    import org.deegree.io.datastore.schema.MappedFeatureType;
    059    import org.deegree.io.datastore.schema.MappedGeometryPropertyType;
    060    import org.deegree.io.datastore.schema.TableRelation;
    061    import org.deegree.io.datastore.schema.content.ConstantContent;
    062    import org.deegree.io.datastore.schema.content.FieldContent;
    063    import org.deegree.io.datastore.schema.content.FunctionParam;
    064    import org.deegree.io.datastore.schema.content.MappingGeometryField;
    065    import org.deegree.io.datastore.schema.content.SQLFunctionCall;
    066    import org.deegree.io.datastore.sql.AbstractSQLDatastore;
    067    import org.deegree.io.datastore.sql.SQLDatastoreConfiguration;
    068    import org.deegree.io.datastore.sql.TableAliasGenerator;
    069    import org.deegree.io.datastore.sql.VirtualContentProvider;
    070    import org.deegree.io.datastore.sql.wherebuilder.WhereBuilder;
    071    import org.deegree.model.crs.CoordinateSystem;
    072    import org.deegree.model.crs.UnknownCRSException;
    073    import org.deegree.model.filterencoding.Filter;
    074    import org.deegree.model.spatialschema.Geometry;
    075    import org.deegree.model.spatialschema.GeometryException;
    076    import org.deegree.ogcbase.SortProperty;
    077    import org.deegree.ogcwebservices.wfs.operation.Query;
    078    import org.postgis.PGgeometry;
    079    import org.postgresql.PGConnection;
    080
    081    /**
    082     * {@link Datastore} implementation for PostGIS/PostgreSQL databases.
    083     *
    084     * @author <a href="mailto:schneider@lat-lon.de">Markus Schneider </a>
    085     * @author <a href="mailto:tfr@users.sourceforge.net">Torsten Friebe </a>
    086     * @author last edited by: $Author: mschneider $
    087     *
    088     * @version $Revision: 23694 $, $Date: 2010-04-20 14:47:40 +0200 (Di, 20. Apr 2010) $
    089     */
    090    public class PostGISDatastore extends AbstractSQLDatastore {
    091
    092        private static final ILogger LOG = LoggerFactory.getLogger( PostGISDatastore.class );
    093
    094        private static final String GEOMETRY_DATATYPE_NAME = "geometry";
    095
    096        private static final String BOX3D_DATATYPE_NAME = "box3d";
    097
    098        private static final String PG_GEOMETRY_CLASS_NAME = "org.postgis.PGgeometry";
    099
    100        private static final String PG_BOX3D_CLASS_NAME = "org.postgis.PGbox3d";
    101
    102        private static Class<?> pgGeometryClass;
    103
    104        private static Class<?> pgBox3dClass;
    105
    106        private static final String SRS_CODE_PROP_FILE = "srs_codes_postgis.properties";
    107
    108        private static Map<String, Integer> nativeSrsCodeMap = new HashMap<String, Integer>();
    109
    110        private static boolean useNativeSrsCodeMap = true;
    111
    112        static {
    113            try {
    114                pgGeometryClass = Class.forName( PG_GEOMETRY_CLASS_NAME );
    115            } catch ( ClassNotFoundException e ) {
    116                LOG.logError( "Cannot find class '" + PG_GEOMETRY_CLASS_NAME + "'.", e );
    117            }
    118            try {
    119                pgBox3dClass = Class.forName( PG_BOX3D_CLASS_NAME );
    120            } catch ( ClassNotFoundException e ) {
    121                LOG.logError( "Cannot find class '" + PG_BOX3D_CLASS_NAME + "'.", e );
    122            }
    123            try {
    124                initSRSCodeMap();
    125            } catch ( IOException e ) {
    126                String msg = "Cannot load native srs code file '" + SRS_CODE_PROP_FILE + "'.";
    127                LOG.logError( msg, e );
    128            }
    129        }
    130
    131        /**
    132         * Returns a specific {@link WhereBuilder} implementation for PostGIS.
    133         *
    134         * @param rootFts
    135         *            involved (requested) feature types
    136         * @param aliases
    137         *            aliases for the feature types, may be null
    138         * @param filter
    139         *            filter that restricts the matched features
    140         * @param sortProperties
    141         *            sort criteria for the result, may be null or empty
    142         * @param aliasGenerator
    143         *            used to generate unique table aliases
    144         * @param vcProvider
    145         * @return <code>WhereBuilder</code> implementation for PostGIS
    146         * @throws DatastoreException
    147         */
    148        @Override
    149        public PostGISWhereBuilder getWhereBuilder( MappedFeatureType[] rootFts, String[] aliases, Filter filter,
    150                                                    SortProperty[] sortProperties, TableAliasGenerator aliasGenerator,
    151                                                    VirtualContentProvider vcProvider )
    152                                throws DatastoreException {
    153            return new PostGISWhereBuilder( rootFts, aliases, filter, sortProperties, aliasGenerator, vcProvider );
    154        }
    155
    156        /**
    157         * Converts a PostGIS specific geometry <code>Object</code> from the <code>ResultSet</code> to a deegree
    158         * <code>Geometry</code>.
    159         *
    160         * @param value
    161         * @param targetCS
    162         * @param conn
    163         * @return corresponding deegree geometry
    164         * @throws SQLException
    165         */
    166        @Override
    167        public Geometry convertDBToDeegreeGeometry( Object value, CoordinateSystem targetCS, Connection conn )
    168                                throws SQLException {
    169            Geometry geometry = null;
    170            if ( value != null && value instanceof PGgeometry ) {
    171                try {
    172                    LOG.logDebug( "Converting PostGIS geometry to deegree geometry ('" + targetCS.getIdentifier() + "')" );
    173                    geometry = PGgeometryAdapter.wrap( (PGgeometry) value, targetCS );
    174                } catch ( Exception e ) {
    175                    throw new SQLException( "Error converting PostGIS geometry to deegree geometry: " + e.getMessage() );
    176                }
    177            }
    178            return geometry;
    179        }
    180
    181        /**
    182         * Converts a deegree <code>Geometry</code> to a PostGIS specific geometry object.
    183         *
    184         * @param geometry
    185         * @param targetSRS
    186         * @param conn
    187         * @return corresponding PostGIS specific geometry object
    188         * @throws DatastoreException
    189         */
    190        @Override
    191        public PGgeometry convertDeegreeToDBGeometry( Geometry geometry, int targetSRS, Connection conn )
    192                                throws DatastoreException {
    193            PGgeometry pgGeometry;
    194            try {
    195                pgGeometry = PGgeometryAdapter.export( geometry, targetSRS );
    196            } catch ( GeometryException e ) {
    197                throw new DatastoreException( "Error converting deegree geometry to PostGIS geometry: " + e.getMessage(), e );
    198            }
    199            return pgGeometry;
    200        }
    201
    202        @Override
    203        protected Connection acquireConnection()
    204                                throws DatastoreException {
    205            JDBCConnection jdbcConnection = ( (SQLDatastoreConfiguration) this.getConfiguration() ).getJDBCConnection();
    206            Connection conn = null;
    207            try {
    208                conn = pool.acquireConnection( jdbcConnection.getDriver(), jdbcConnection.getURL(),
    209                                               jdbcConnection.getUser(), jdbcConnection.getPassword() );
    210                PGConnection pgConn = (PGConnection) conn;
    211                pgConn.addDataType( GEOMETRY_DATATYPE_NAME, pgGeometryClass );
    212                pgConn.addDataType( BOX3D_DATATYPE_NAME, pgBox3dClass );
    213            } catch ( Exception e ) {
    214                String msg = "Cannot acquire database connection: " + e.getMessage();
    215                LOG.logInfo( msg );
    216                throw new DatastoreException( msg, e );
    217            }
    218            return conn;
    219        }
    220
    221        /**
    222         * Returns the next value of the given SQL sequence.
    223         *
    224         * @param conn
    225         *            JDBC connection to be used
    226         * @param sequence
    227         *            name of the SQL sequence
    228         * @return next value of the given SQL sequence
    229         * @throws DatastoreException
    230         *             if the value could not be retrieved
    231         */
    232        @Override
    233        public Object getSequenceNextVal( Connection conn, String sequence )
    234                                throws DatastoreException {
    235
    236            Object nextVal = null;
    237            Statement stmt = null;
    238            ResultSet rs = null;
    239
    240            try {
    241                try {
    242                    stmt = conn.createStatement();
    243                    rs = stmt.executeQuery( "SELECT NEXTVAL('" + sequence + "')" );
    244                    if ( rs.next() ) {
    245                        nextVal = rs.getObject( 1 );
    246                    }
    247                } finally {
    248                    try {
    249                        if ( rs != null ) {
    250                            rs.close();
    251                        }
    252                    } finally {
    253                        if ( stmt != null ) {
    254                            stmt.close();
    255                        }
    256                    }
    257                }
    258            } catch ( SQLException e ) {
    259                String msg = "Could not retrieve value for sequence '" + sequence + "': " + e.getMessage();
    260                throw new DatastoreException( msg, e );
    261            }
    262            return nextVal;
    263        }
    264
    265        /**
    266         * Transforms the incoming {@link Query} so that the {@link CoordinateSystem} of all spatial arguments (BBOX, etc.)
    267         * in the {@link Filter} match the SRS of the targeted {@link MappingGeometryField}s.
    268         * <p>
    269         * NOTE: If this transformation can be performed by the backend (e.g. by Oracle Spatial), this method should be
    270         * overwritten to return the original input {@link Query}.
    271         *
    272         * @param query
    273         *            query to be transformed
    274         * @return query with spatial arguments transformed to target SRS
    275         */
    276        @Override
    277        protected Query transformQuery( Query query ) {
    278            if ( query.getSrsName() == null || canTransformTo( query.getSrsName() ) ) {
    279                return query;
    280            }
    281            return super.transformQuery( query );
    282        }
    283
    284        /**
    285         * Returns whether the datastore is capable of performing a native coordinate transformation (using an SQL function
    286         * call for example) into the given SRS.
    287         *
    288         * @param targetSRS
    289         *            target spatial reference system (usually "EPSG:XYZ")
    290         * @return true, if the datastore can perform the coordinate transformation, false otherwise
    291         */
    292        @Override
    293        protected boolean canTransformTo( String targetSRS ) {
    294            return getNativeSRSCode( targetSRS ) != SRS_UNDEFINED;
    295        }
    296
    297        /**
    298         * Returns an {@link SQLFunctionCall} that refers to the given {@link MappingGeometryField} in the specified target
    299         * SRS using a database specific SQL function.
    300         *
    301         * @param geoProperty
    302         *            geometry property
    303         * @param targetSRS
    304         *            target spatial reference system (usually "EPSG:XYZ")
    305         * @return an {@link SQLFunctionCall} that refers to the geometry in the specified srs
    306         * @throws DatastoreException
    307         */
    308        @Override
    309        public SQLFunctionCall buildSRSTransformCall( MappedGeometryPropertyType geoProperty, String targetSRS )
    310                                throws DatastoreException {
    311
    312            int nativeSRSCode = getNativeSRSCode( targetSRS );
    313            if ( nativeSRSCode == SRS_UNDEFINED ) {
    314                String msg = Messages.getMessage( "DATASTORE_SQL_NATIVE_CT_UNKNOWN_SRS", this.getClass().getName(),
    315                                                  targetSRS );
    316                throw new DatastoreException( msg );
    317            }
    318
    319            MappingGeometryField field = geoProperty.getMappingField();
    320            FunctionParam param1 = new FieldContent( field, new TableRelation[0] );
    321            FunctionParam param2 = new ConstantContent( "" + nativeSRSCode );
    322
    323            SQLFunctionCall transformCall = new SQLFunctionCall( "transform($1,$2)", field.getType(), param1, param2 );
    324            return transformCall;
    325        }
    326
    327        @Override
    328        public String buildSRSTransformCall( String geomIdentifier, int nativeSRSCode )
    329                                throws DatastoreException {
    330            String call = "transform(" + geomIdentifier + "," + nativeSRSCode + ")";
    331            return call;
    332        }
    333
    334        @Override
    335        public int getNativeSRSCode( String srsName ) {
    336            if ( !useNativeSrsCodeMap ) {
    337                try {
    338                    return parseInt( create( srsName ).getCRS().getIdentifier().split( ":" )[1] );
    339                } catch ( NumberFormatException e ) {
    340                    LOG.logError( "Error while checking for srid code", e );
    341                } catch ( UnknownCRSException e ) {
    342                    LOG.logError( "Error while checking for srid code", e );
    343                }
    344            }
    345            Integer nativeSRSCode = nativeSrsCodeMap.get( srsName );
    346            if ( nativeSRSCode == null ) {
    347                return SRS_UNDEFINED;
    348            }
    349            return nativeSRSCode;
    350        }
    351
    352        private static void initSRSCodeMap()
    353                                throws IOException {
    354            InputStream is = PostGISDatastore.class.getResourceAsStream( SRS_CODE_PROP_FILE );
    355            if ( is != null ) {
    356                Properties props = new Properties();
    357                props.load( is );
    358                for ( Object key : props.keySet() ) {
    359                    String nativeCodeStr = props.getProperty( (String) key ).trim();
    360                    try {
    361                        int nativeCode = Integer.parseInt( nativeCodeStr );
    362                        nativeSrsCodeMap.put( (String) key, nativeCode );
    363                    } catch ( NumberFormatException e ) {
    364                        String msg = Messages.getMessage( "DATASTORE_SRS_CODE_INVALID", SRS_CODE_PROP_FILE, nativeCodeStr,
    365                                                          key );
    366                        throw new IOException( msg );
    367                    }
    368                }
    369            } else {
    370                LOG.logInfo( "Not using '" + SRS_CODE_PROP_FILE
    371                             + "' for customizing PostGIS transformations. Not found on classpath." );
    372                useNativeSrsCodeMap = false;
    373            }
    374        }
    375    }