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