    001    //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.4_testing/src/org/deegree/io/datastore/sql/postgis/PostGISWhereBuilder.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 org.deegree.i18n.Messages.getMessage;
    040
    041    import java.sql.Types;
    042    import java.util.ArrayList;
    043    import java.util.List;
    044
    045    import org.deegree.i18n.Messages;
    046    import org.deegree.io.datastore.DatastoreException;
    047    import org.deegree.io.datastore.schema.MappedFeatureType;
    048    import org.deegree.io.datastore.schema.MappedGeometryPropertyType;
    049    import org.deegree.io.datastore.sql.StatementBuffer;
    050    import org.deegree.io.datastore.sql.TableAliasGenerator;
    051    import org.deegree.io.datastore.sql.VirtualContentProvider;
    052    import org.deegree.io.datastore.sql.wherebuilder.SpecialCharString;
    053    import org.deegree.io.datastore.sql.wherebuilder.WhereBuilder;
    054    import org.deegree.model.crs.CRSFactory;
    055    import org.deegree.model.crs.UnknownCRSException;
    056    import org.deegree.model.filterencoding.DBFunction;
    057    import org.deegree.model.filterencoding.Expression;
    058    import org.deegree.model.filterencoding.Filter;
    059    import org.deegree.model.filterencoding.FilterEvaluationException;
    060    import org.deegree.model.filterencoding.Function;
    061    import org.deegree.model.filterencoding.OperationDefines;
    062    import org.deegree.model.filterencoding.PropertyIsLikeOperation;
    063    import org.deegree.model.filterencoding.SpatialOperation;
    064    import org.deegree.model.spatialschema.Envelope;
    065    import org.deegree.model.spatialschema.Geometry;
    066    import org.deegree.model.spatialschema.GeometryException;
    067    import org.deegree.ogcbase.SortProperty;
    068    import org.postgis.PGboxbase;
    069    import org.postgis.PGgeometry;
    070
    071    /**
    072     * {@link WhereBuilder} implementation for PostGIS databases.
    073     *
    074     * @author <a href="mailto:schneider@lat-lon.de">Markus Schneider </A>
    075     * @author last edited by: $Author: mschneider $
    076     *
    077     * @version $Revision: 23694 $, $Date: 2010-04-20 14:47:40 +0200 (Di, 20. Apr 2010) $
    078     */
    079    class PostGISWhereBuilder extends WhereBuilder {
    080
    081        private PostGISDatastore ds;
    082
    083        /**
    084         * Creates a new instance of <code>PostGISWhereBuilder</code> from the given parameters.
    085         *
    086         * @param rootFts
    087         *            selected feature types, more than one type means that the types are joined
    088         * @param aliases
    089         *            aliases for the feature types, may be null (must have same length as rootFts otherwise)
    090         * @param filter
    091         *            filter that restricts the matched features
    092         * @param sortProperties
    093         *            sort criteria for the result, may be null or empty
    094         * @param aliasGenerator
    095         *            used to generate unique table aliases
    096         * @param vcProvider
    097         * @throws DatastoreException
    098         */
    099        public PostGISWhereBuilder( MappedFeatureType[] rootFts, String[] aliases, Filter filter,
    100                                    SortProperty[] sortProperties, TableAliasGenerator aliasGenerator,
    101                                    VirtualContentProvider vcProvider ) throws DatastoreException {
    102            super( rootFts, aliases, filter, sortProperties, aliasGenerator, vcProvider );
    103            this.ds = (PostGISDatastore) rootFts[0].getGMLSchema().getDatastore();
    104        }
    105
    106        /**
    107         * Appends an SQL fragment for the given object to the given sql statement.
    108         *
    109         * NOTE: Currently, the method uses a quirk and appends the generated argument inline, i.e. not using
    110         * query.addArgument(). This is because of a problem that occurred in PostgreSQL; the execution of the inline
    111         * version is *much* faster (at least with version 8.0).
    112         *
    113         * @param query
    114         * @param operation
    115         * @throws FilterEvaluationException
    116         */
    117        @Override
    118        protected void appendPropertyIsLikeOperationAsSQL( StatementBuffer query, PropertyIsLikeOperation operation )
    119                                throws FilterEvaluationException {
    120
    121            String literal = operation.getLiteral().getValue();
    122            String escape = "" + operation.getEscapeChar();
    123            String wildCard = "" + operation.getWildCard();
    124            String singleChar = "" + operation.getSingleChar();
    125
    126            SpecialCharString specialString = new SpecialCharString( literal, wildCard, singleChar, escape );
    127            String sqlEncoded = specialString.toSQLStyle( !operation.isMatchCase() );
    128
    129            int targetSqlType = getPropertyNameSQLType( operation.getPropertyName() );
    130
    131            // if isMatchCase == false surround first argument with LOWER (...) and convert characters
    132            // in second argument to lower case
    133            if ( operation.isMatchCase() ) {
    134                appendPropertyNameAsSQL( query, operation.getPropertyName() );
    135            } else {
    136                List<Expression> list = new ArrayList<Expression>();
    137                list.add( operation.getPropertyName() );
    138                Function func = new DBFunction( getFunctionName( "LOWER" ), list );
    139                appendFunctionAsSQL( query, func, targetSqlType );
    140            }
    141
    142            query.append( "::VARCHAR LIKE '" );
    143            query.append( sqlEncoded );
    144            query.append( "'" );
    145        }
    146
    147        /**
    148         * Generates an SQL-fragment for the given object.
    149         *
    150         * TODO: Implement BBOX faster using explicit B0X-constructor
    151         *
    152         * @throws DatastoreException
    153         */
    154        @Override
    155        protected void appendSpatialOperationAsSQL( StatementBuffer query, SpatialOperation operation )
    156                                throws DatastoreException {
    157
    158            try {
    159                switch ( operation.getOperatorId() ) {
    160                case OperationDefines.BBOX: {
    161                    appendBBOXOperationAsSQL( query, operation );
    162                    break;
    163                }
    164                case OperationDefines.INTERSECTS: {
    165                    appendIntersectsOperationAsSQL( query, operation );
    166                    break;
    167                }
    168                case OperationDefines.CROSSES: {
    169                    appendSimpleOperationAsSQL( query, operation, "crosses" );
    170                    break;
    171                }
    172                case OperationDefines.EQUALS: {
    173                    appendSimpleOperationAsSQL( query, operation, "equals" );
    174                    break;
    175                }
    176                case OperationDefines.WITHIN: {
    177                    appendSimpleOperationAsSQL( query, operation, "within" );
    178                    break;
    179                }
    180                case OperationDefines.OVERLAPS: {
    181                    appendSimpleOperationAsSQL( query, operation, "overlaps" );
    182                    break;
    183                }
    184                case OperationDefines.TOUCHES: {
    185                    appendSimpleOperationAsSQL( query, operation, "touches" );
    186                    break;
    187                }
    188                case OperationDefines.DISJOINT: {
    189                    appendSimpleOperationAsSQL( query, operation, "disjoint" );
    190                    break;
    191                }
    192                case OperationDefines.CONTAINS: {
    193                    appendSimpleOperationAsSQL( query, operation, "contains" );
    194                    break;
    195                }
    196                case OperationDefines.DWITHIN: {
    197                    appendDWithinOperationAsSQL( query, operation );
    198                    break;
    199                }
    200                case OperationDefines.BEYOND: {
    201                    appendBeyondOperationAsSQL( query, operation );
    202                    break;
    203                }
    204                default: {
    205                    String msg = "Spatial operator " + OperationDefines.getNameById( operation.getOperatorId() )
    206                                 + " is not supported by '" + this.getClass().toString() + "'.";
    207                    throw new DatastoreException( msg );
    208                }
    209                }
    210            } catch ( GeometryException e ) {
    211                throw new DatastoreException( e );
    212            }
    213
    214        }
    215
    216        private void appendSimpleOperationAsSQL( StatementBuffer query, SpatialOperation operation, String operationName )
    217                                throws GeometryException, DatastoreException {
    218            query.append( operationName );
    219            query.append( "(" );
    220            appendPropertyNameAsSQL( query, operation.getPropertyName() );
    221            query.append( ',' );
    222            appendGeometryArgument( query, this.getGeometryProperty( operation.getPropertyName() ), operation.getGeometry() );
    223            query.append( ')' );
    224        }
    225
    226        private void appendIntersectsOperationAsSQL( StatementBuffer query, SpatialOperation operation )
    227                                throws GeometryException, DatastoreException {
    228
    229            Envelope env = operation.getGeometry().getEnvelope();
    230            MappedGeometryPropertyType geoProperty = this.getGeometryProperty( operation.getPropertyName() );
    231
    232            String argumentSRS = null;
    233            if ( env.getCoordinateSystem() != null ) {
    234                argumentSRS = env.getCoordinateSystem().getIdentifier();
    235            }
    236            String propertySRS = geoProperty.getCS().getIdentifier();
    237            int internalSRS = geoProperty.getMappingField().getSRS();
    238
    239            int createSRSCode = getArgumentSRSCode( argumentSRS, propertySRS, internalSRS );
    240            PGboxbase box = PGgeometryAdapter.export( env );
    241            StringBuffer bbox = new StringBuffer( 323 );
    242            bbox.append( "SetSRID(?," + createSRSCode + ")" );
    243
    244            int targetSRSCode = getTargetSRSCode( argumentSRS, propertySRS, internalSRS );
    245            if ( targetSRSCode != SRS_UNDEFINED ) {
    246                bbox = new StringBuffer( this.ds.buildSRSTransformCall( bbox.toString(), targetSRSCode ) );
    247            }
    248
    249            // use the bbox operator (&&) to filter using the spatial index
    250            query.append( "(" );
    251            appendPropertyNameAsSQL( query, operation.getPropertyName() );
    252            query.append( " && " );
    253            query.append( bbox.toString() );
    254            query.addArgument( box, Types.OTHER );
    255
    256            query.append( " AND intersects (" );
    257            appendPropertyNameAsSQL( query, operation.getPropertyName() );
    258            query.append( ',' );
    259            appendGeometryArgument( query, getGeometryProperty( operation.getPropertyName() ), operation.getGeometry() );
    260            query.append( "))" );
    261        }
    262
    263        private void appendBBOXOperationAsSQL( StatementBuffer query, SpatialOperation operation )
    264                                throws DatastoreException, GeometryException {
    265
    266            Envelope env = operation.getGeometry().getEnvelope();
    267            MappedGeometryPropertyType geoProperty = this.getGeometryProperty( operation.getPropertyName() );
    268
    269            String argumentSRS = null;
    270            if ( env.getCoordinateSystem() != null ) {
    271                argumentSRS = env.getCoordinateSystem().getIdentifier();
    272            }
    273            String propertySRS = geoProperty.getCS().getIdentifier();
    274            int internalSRS = geoProperty.getMappingField().getSRS();
    275            int createSRSCode = getArgumentSRSCode( argumentSRS, propertySRS, internalSRS );
    276            PGboxbase box = PGgeometryAdapter.export( env );
    277            StringBuffer bbox = new StringBuffer( 326 );
    278            bbox.append( "SetSRID(?," + createSRSCode + ")" );
    279
    280            int targetSRSCode = getTargetSRSCode( argumentSRS, propertySRS, internalSRS );
    281            if ( argumentSRS != null && !argumentSRS.equals( propertySRS ) ) {
    282                bbox = new StringBuffer( this.ds.buildSRSTransformCall( bbox.toString(), targetSRSCode ) );
    283            }
    284
    285            // only the && operator uses the spatial index
    286            // intersects, contains etc. do not use spatial indexing!!!!
    287            query.append( "(" );
    288            appendPropertyNameAsSQL( query, operation.getPropertyName() );
    289            query.append( " && " );
    290            query.append( bbox.toString() );
    291            query.addArgument( box, Types.OTHER );
    292
    293            // it is necessary to add an explicit intersects as well, because the && operator only
    294            // checks for intersection of the bbox with the bboxes of the geometries (and not the
    295            // geometries themselves)
    296            query.append( " AND intersects (" );
    297            appendPropertyNameAsSQL( query, operation.getPropertyName() );
    298            query.append( ',' );
    299            query.append( bbox.toString() );
    300            query.addArgument( box, Types.OTHER );
    301            query.append( "))" );
    302        }
    303
    304        private void appendDWithinOperationAsSQL( StatementBuffer query, SpatialOperation operation )
    305                                throws GeometryException, DatastoreException {
    306            query.append( "distance(" );
    307            appendPropertyNameAsSQL( query, operation.getPropertyName() );
    308            query.append( ',' );
    309            appendGeometryArgument( query, this.getGeometryProperty( operation.getPropertyName() ), operation.getGeometry() );
    310            query.append( ")<=" );
    311            query.append( "" + operation.getDistance() );
    312        }
    313
    314        private void appendBeyondOperationAsSQL( StatementBuffer query, SpatialOperation operation )
    315                                throws GeometryException, DatastoreException {
    316            query.append( "distance(" );
    317            appendPropertyNameAsSQL( query, operation.getPropertyName() );
    318            query.append( ',' );
    319            appendGeometryArgument( query, this.getGeometryProperty( operation.getPropertyName() ), operation.getGeometry() );
    320            query.append( ")>" );
    321            query.append( "" + operation.getDistance() );
    322        }
    323
    324        /**
    325         * Construct and append the geometry argument using the correct internal SRS and perform a transform call to the
    326         * internal SRS of the {@link MappedGeometryPropertyType} if necessary.
    327         *
    328         * @param query
    329         * @param geoProperty
    330         * @param geometry
    331         * @throws DatastoreException
    332         * @throws GeometryException
    333         */
    334        private void appendGeometryArgument( StatementBuffer query, MappedGeometryPropertyType geoProperty,
    335                                             Geometry geometry )
    336                                throws DatastoreException, GeometryException {
    337
    338            String argumentSRS = null;
    339            if ( geometry.getCoordinateSystem() != null ) {
    340                argumentSRS = geometry.getCoordinateSystem().getIdentifier();
    341            }
    342            String propertySRS = geoProperty.getCS().getIdentifier();
    343            int internalSRS = geoProperty.getMappingField().getSRS();
    344
    345            int createSRSCode = getArgumentSRSCode( argumentSRS, propertySRS, internalSRS );
    346            PGgeometry argument = PGgeometryAdapter.export( geometry, createSRSCode );
    347
    348            int targetSRSCode = getTargetSRSCode( argumentSRS, propertySRS, internalSRS );
    349            if ( argumentSRS != null && !argumentSRS.equals( propertySRS ) ) {
    350                query.append( ds.buildSRSTransformCall( "?", targetSRSCode ) );
    351            } else {
    352                query.append( '?' );
    353            }
    354            query.addArgument( argument, Types.OTHER );
    355        }
    356
    357        /**
    358         * Returns the internal SRS code that must be used for the creation of a geometry argument used in a spatial
    359         * operator.
    360         *
    361         * @param argumentSRS
    362         * @param propertySRS
    363         * @param internalSrs
    364         * @return the internal SRS code that must be used for the creation of a geometry argument
    365         * @throws DatastoreException
    366         */
    367        private int getArgumentSRSCode( String argumentSRS, String propertySRS, int internalSrs )
    368                                throws DatastoreException {
    369            int argumentSRSCode = internalSrs;
    370            if ( argumentSRS == null ) {
    371                argumentSRSCode = internalSrs;
    372            } else if ( !propertySRS.equals( argumentSRS ) ) {
    373                // normalize SRS to first identifier
    374                try {
    375                    argumentSRS = CRSFactory.create( argumentSRS ).getCRS().getIdentifier();
    376                } catch ( UnknownCRSException e ) {
    377                    throw new DatastoreException( getMessage( "DATASTORE_SRS_UNKNOWN", argumentSRS ) );
    378                }
    379                argumentSRSCode = this.ds.getNativeSRSCode( argumentSRS );
    380                if ( argumentSRSCode == SRS_UNDEFINED ) {
    381                    String msg = Messages.getMessage( "DATASTORE_SQL_NATIVE_CT_UNKNOWN_SRS",
    382                                                      PostGISDatastore.class.getName(), argumentSRS );
    383                    throw new DatastoreException( msg );
    384                }
    385            }
    386            return argumentSRSCode;
    387        }
    388
    389        /**
    390         * Returns the internal SRS code that must be used for the transform call for a geometry argument used in a spatial
    391         * operator.
    392         *
    393         * @param argumentSRS
    394         * @param propertySRS
    395         * @param internalSrs
    396         * @return the internal SRS code that must be used for the transform call of a geometry argument, or -1 if no
    397         *         transformation is necessary
    398         */
    399        private int getTargetSRSCode( String argumentSRS, String propertySRS, int internalSrs )
    400                                throws DatastoreException {
    401            int targetSRS = SRS_UNDEFINED;
    402            if ( argumentSRS != null && !argumentSRS.equals( propertySRS ) ) {
    403                if ( internalSrs == SRS_UNDEFINED ) {
    404                    String msg = Messages.getMessage( "DATASTORE_SRS_NOT_SPECIFIED2", argumentSRS, propertySRS );
    405                    throw new DatastoreException( msg );
    406                }
    407                targetSRS = internalSrs;
    408            }
    409            return targetSRS;
    410        }
    411    }