001 //$HeadURL: svn+ssh://jwilden@svn.wald.intevation.org/deegree/base/branches/2.5_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 }