001 //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.3_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: 18864 $, $Date: 2009-08-03 17:11:49 +0200 (Mo, 03. Aug 2009) $
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
276 int createSRSCode = getArgumentSRSCode( argumentSRS, propertySRS, internalSRS );
277 PGboxbase box = PGgeometryAdapter.export( env );
278 StringBuffer bbox = new StringBuffer( 323 );
279 bbox.append( "SetSRID(?," + createSRSCode + ")" );
280
281 int targetSRSCode = getTargetSRSCode( argumentSRS, propertySRS, internalSRS );
282 if ( targetSRSCode != SRS_UNDEFINED ) {
283 bbox = new StringBuffer( this.ds.buildSRSTransformCall( bbox.toString(), targetSRSCode ) );
284 }
285
286 // only the && operator uses the spatial index
287 // intersects, contains etc. do not use spatial indexing!!!!
288 query.append( "(" );
289 appendPropertyNameAsSQL( query, operation.getPropertyName() );
290 query.append( " && " );
291 query.append( bbox.toString() );
292 query.addArgument( box, Types.OTHER );
293
294 // it is necessary to add an explicit intersects as well, because the && operator only
295 // checks for intersection of the bbox with the bboxes of the geometries (and not the
296 // geometries themselves)
297 query.append( " AND intersects (" );
298 appendPropertyNameAsSQL( query, operation.getPropertyName() );
299 query.append( ',' );
300 query.append( bbox.toString() );
301 query.addArgument( box, Types.OTHER );
302 query.append( "))" );
303 }
304
305 private void appendDWithinOperationAsSQL( StatementBuffer query, SpatialOperation operation )
306 throws GeometryException, DatastoreException {
307 query.append( "distance(" );
308 appendPropertyNameAsSQL( query, operation.getPropertyName() );
309 query.append( ',' );
310 appendGeometryArgument( query, this.getGeometryProperty( operation.getPropertyName() ), operation.getGeometry() );
311 query.append( ")<=" );
312 query.append( "" + operation.getDistance() );
313 }
314
315 private void appendBeyondOperationAsSQL( StatementBuffer query, SpatialOperation operation )
316 throws GeometryException, DatastoreException {
317 query.append( "distance(" );
318 appendPropertyNameAsSQL( query, operation.getPropertyName() );
319 query.append( ',' );
320 appendGeometryArgument( query, this.getGeometryProperty( operation.getPropertyName() ), operation.getGeometry() );
321 query.append( ")>" );
322 query.append( "" + operation.getDistance() );
323 }
324
325 /**
326 * Construct and append the geometry argument using the correct internal SRS and perform a transform call to the
327 * internal SRS of the {@link MappedGeometryPropertyType} if necessary.
328 *
329 * @param query
330 * @param geoProperty
331 * @param geometry
332 * @throws DatastoreException
333 * @throws GeometryException
334 */
335 private void appendGeometryArgument( StatementBuffer query, MappedGeometryPropertyType geoProperty,
336 Geometry geometry )
337 throws DatastoreException, GeometryException {
338
339 String argumentSRS = null;
340 if ( geometry.getCoordinateSystem() != null ) {
341 argumentSRS = geometry.getCoordinateSystem().getIdentifier();
342 }
343 String propertySRS = geoProperty.getCS().getIdentifier();
344 int internalSRS = geoProperty.getMappingField().getSRS();
345
346 int createSRSCode = getArgumentSRSCode( argumentSRS, propertySRS, internalSRS );
347 PGgeometry argument = PGgeometryAdapter.export( geometry, createSRSCode );
348
349 int targetSRSCode = getTargetSRSCode( argumentSRS, propertySRS, internalSRS );
350 if ( targetSRSCode != SRS_UNDEFINED ) {
351 query.append( ds.buildSRSTransformCall( "?", targetSRSCode ) );
352 } else {
353 query.append( '?' );
354 }
355 query.addArgument( argument, Types.OTHER );
356 }
357
358 /**
359 * Returns the internal SRS code that must be used for the creation of a geometry argument used in a spatial
360 * operator.
361 *
362 * @param argumentSRS
363 * @param propertySRS
364 * @param internalSrs
365 * @return the internal SRS code that must be used for the creation of a geometry argument
366 * @throws DatastoreException
367 */
368 private int getArgumentSRSCode( String argumentSRS, String propertySRS, int internalSrs )
369 throws DatastoreException {
370 int argumentSRSCode = internalSrs;
371 if ( argumentSRS == null ) {
372 argumentSRSCode = internalSrs;
373 } else if ( !propertySRS.equals( argumentSRS ) ) {
374 // normalize SRS to first identifier
375 try {
376 argumentSRS = CRSFactory.create( argumentSRS ).getCRS().getIdentifier();
377 } catch ( UnknownCRSException e ) {
378 throw new DatastoreException( getMessage( "DATASTORE_SRS_UNKNOWN", argumentSRS ) );
379 }
380 argumentSRSCode = this.ds.getNativeSRSCode( argumentSRS );
381 if ( argumentSRSCode == SRS_UNDEFINED ) {
382 String msg = Messages.getMessage( "DATASTORE_SQL_NATIVE_CT_UNKNOWN_SRS",
383 PostGISDatastore.class.getName(), argumentSRS );
384 throw new DatastoreException( msg );
385 }
386 }
387 return argumentSRSCode;
388 }
389
390 /**
391 * Returns the internal SRS code that must be used for the transform call for a geometry argument used in a spatial
392 * operator.
393 *
394 * @param argumentSRS
395 * @param propertySRS
396 * @param internalSrs
397 * @return the internal SRS code that must be used for the transform call of a geometry argument, or -1 if no
398 * transformation is necessary
399 */
400 private int getTargetSRSCode( String argumentSRS, String propertySRS, int internalSrs )
401 throws DatastoreException {
402 int targetSRS = SRS_UNDEFINED;
403 if ( argumentSRS != null && !argumentSRS.equals( propertySRS ) ) {
404 if ( internalSrs == SRS_UNDEFINED ) {
405 String msg = Messages.getMessage( "DATASTORE_SRS_NOT_SPECIFIED2", argumentSRS, propertySRS );
406 throw new DatastoreException( msg );
407 }
408 targetSRS = internalSrs;
409 }
410 return targetSRS;
411 }
412 }