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 }