001: /*
002: * GeoTools - OpenSource mapping toolkit
003: * http://geotools.org
004: * (C) Copyright IBM Corporation, 2005-2007. All rights reserved.
005: *
006: * This library is free software; you can redistribute it and/or
007: * modify it under the terms of the GNU Lesser General Public
008: * License as published by the Free Software Foundation;
009: * version 2.1 of the License.
010: *
011: * This library is distributed in the hope that it will be useful,
012: * but WITHOUT ANY WARRANTY; without even the implied warranty of
013: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
014: * Lesser General Public License for more details.
015: *
016: */
017: package org.geotools.data.db2.filter;
018:
019: import com.vividsolutions.jts.geom.Geometry;
020: import com.vividsolutions.jts.io.WKTWriter;
021:
022: import org.geotools.data.jdbc.FilterToSQL;
023:
024: import org.geotools.filter.DefaultExpression;
025: import org.geotools.filter.FilterCapabilities;
026:
027: import org.opengis.filter.Id;
028: import org.opengis.filter.PropertyIsLike;
029: import org.opengis.filter.expression.Literal;
030: import org.opengis.filter.identity.FeatureId;
031: import org.opengis.filter.spatial.BBOX;
032: import org.opengis.filter.spatial.Beyond;
033: import org.opengis.filter.spatial.BinarySpatialOperator;
034: import org.opengis.filter.spatial.Contains;
035: import org.opengis.filter.spatial.Crosses;
036: import org.opengis.filter.spatial.DWithin;
037: import org.opengis.filter.spatial.Disjoint;
038: import org.opengis.filter.spatial.Equals;
039: import org.opengis.filter.spatial.Intersects;
040: import org.opengis.filter.spatial.Overlaps;
041: import org.opengis.filter.spatial.Touches;
042: import org.opengis.filter.spatial.Within;
043:
044: import java.io.IOException;
045: import java.sql.Types;
046: import java.util.HashMap;
047: import java.util.Iterator;
048: import java.util.Set;
049: import java.util.logging.Logger;
050:
051: /**
052: * Generate a WHERE clause for DB2 Spatial Extender based on a spatial filter.
053: *
054: * <p>
055: * The following spatial filter operations are supported:
056: *
057: * <ul>
058: * <li>
059: * GEOMETRY_BBOX
060: * </li>
061: * <li>
062: * GEOMETRY_CONTAINS
063: * </li>
064: * <li>
065: * GEOMETRY_CROSSES
066: * </li>
067: * <li>
068: * GEOMETRY_DISJOINT
069: * </li>
070: * <li>
071: * GEOMETRY_EQUALS
072: * </li>
073: * <li>
074: * GEOMETRY_INTERSECTS
075: * </li>
076: * <li>
077: * GEOMETRY_OVERLAPS
078: * </li>
079: * <li>
080: * GEOMETRY_TOUCHES
081: * </li>
082: * <li>
083: * GEOMETRY_WITHIN
084: * </li>
085: * <li>
086: * GEOMETRY_DWITHIN
087: * </li>
088: * </ul>
089: * </p>
090: *
091: * @author David Adler - IBM Corporation
092: * @source $URL: http://svn.geotools.org/geotools/tags/2.4.1/modules/unsupported/db2/src/main/java/org/geotools/data/db2/filter/SQLEncoderDB2.java $
093: */
094: public class SQLEncoderDB2 extends FilterToSQL {
095: // public class SQLEncoderDB2 extends SQLEncoder implements FilterVisitor {
096: private static Logger LOGGER = org.geotools.util.logging.Logging
097: .getLogger("org.geotools.data.db2");
098:
099: // Class to convert geometry value into a Well-known Text string
100: private static WKTWriter wktWriter = new WKTWriter();
101:
102: //The standard SQL multicharacter wild card.
103: private static char SQL_WILD_MULTI = '%';
104:
105: //The standard SQL single character wild card.
106: private static char SQL_WILD_SINGLE = '_';
107:
108: // The escaped version of the single wildcard for the REGEXP pattern.
109: private static String escapedWildcardSingle = "\\.\\?";
110:
111: // The escaped version of the multiple wildcard for the REGEXP pattern.
112: private static String escapedWildcardMulti = "\\.\\*";
113: static private HashMap DB2_SPATIAL_PREDICATES = new HashMap();
114:
115: // Only intended for test purposes
116: public HashMap getPredicateMap() {
117: return DB2_SPATIAL_PREDICATES;
118: }
119:
120: // The SELECTIVITY clause to be used with spatial predicates.
121: private String selectivityClause = null;
122:
123: // We need the srid to create an ST_Geometry - default to NAD83 for now
124: private int srid = 1;
125:
126: {
127: DB2_SPATIAL_PREDICATES.put(BBOX.class, "EnvelopesIntersect");
128: DB2_SPATIAL_PREDICATES.put(Contains.class, "ST_Contains");
129: DB2_SPATIAL_PREDICATES.put(Crosses.class, "ST_Crosses");
130: DB2_SPATIAL_PREDICATES.put(Disjoint.class, "ST_Disjoint");
131: DB2_SPATIAL_PREDICATES.put(Equals.class, "ST_Equals");
132: DB2_SPATIAL_PREDICATES.put(Intersects.class, "ST_Intersects");
133: DB2_SPATIAL_PREDICATES.put(Overlaps.class, "ST_Overlaps");
134: DB2_SPATIAL_PREDICATES.put(Touches.class, "ST_Touches");
135: DB2_SPATIAL_PREDICATES.put(Within.class, "ST_Within");
136: DB2_SPATIAL_PREDICATES.put(DWithin.class, "ST_Distance");
137: DB2_SPATIAL_PREDICATES.put(Beyond.class, "ST_Distance");
138: }
139:
140: /**
141: * Construct an SQLEncoderDB2
142: */
143: public SQLEncoderDB2() {
144: super ();
145: }
146:
147: /** HashMap<Class,String> example [BBOX.class,"EnvelopesIntersect"] */
148: static private HashMap getPredicateTable() {
149: return DB2_SPATIAL_PREDICATES;
150: }
151:
152: /**
153: * Construct a geometry from the WKT representation of a geometry
154:
155: *
156: * @param geom the constructor for the geometry.
157: *
158:
159: */
160: public String db2Geom(Geometry geom) {
161: String geomType = geom.getGeometryType();
162: String g1 = geom.toText();
163: String g2 = "db2gse.ST_" + geomType + "('" + g1 + "'," + srid
164: + ")";
165: return g2;
166: }
167:
168: /**
169: * Set the value of the srid value to be used if a DB2 Spatial Extender
170: * geometry needs to be constructed.
171: *
172: * <p>
173: * This is specifically the DB2 Spatial Extender spatial reference system
174: * identifier and not a coordinate system identifier ala EPSG.
175: * </p>
176: *
177: * @param srid Spatial reference system identifier to be used.
178: */
179: public void setSRID(int srid) {
180: this .srid = srid;
181: }
182:
183: /**
184: * Sets the DB2 filter capabilities.
185: *
186: * @return FilterCapabilities for DB2
187: */
188: protected FilterCapabilities createFilterCapabilities() {
189:
190: FilterCapabilities capabilities = new FilterCapabilities();
191:
192: // New capbilities
193: capabilities
194: .addAll(FilterCapabilities.SIMPLE_COMPARISONS_OPENGIS);
195: for (Iterator i = getPredicateTable().keySet().iterator(); i
196: .hasNext();) {
197: capabilities.addType((Class) i.next());
198: }
199: capabilities.addType(PropertyIsLike.class);
200: capabilities.addType(Id.class);
201:
202: // Old capabilities
203: capabilities.addType(FilterCapabilities.LOGIC_OR);
204: capabilities.addType(FilterCapabilities.LOGIC_AND);
205: capabilities.addType(FilterCapabilities.LOGIC_NOT);
206: capabilities.addType(FilterCapabilities.COMPARE_EQUALS);
207: capabilities.addType(FilterCapabilities.COMPARE_NOT_EQUALS);
208: capabilities.addType(FilterCapabilities.COMPARE_LESS_THAN);
209: capabilities.addType(FilterCapabilities.COMPARE_GREATER_THAN);
210: capabilities
211: .addType(FilterCapabilities.COMPARE_LESS_THAN_EQUAL);
212: capabilities
213: .addType(FilterCapabilities.COMPARE_GREATER_THAN_EQUAL);
214: capabilities.addType(FilterCapabilities.LIKE);
215: capabilities.addType(FilterCapabilities.NULL_CHECK);
216: capabilities.addType(FilterCapabilities.BETWEEN);
217: capabilities.addType(FilterCapabilities.FID);
218: capabilities.addType(FilterCapabilities.NONE);
219: capabilities.addType(FilterCapabilities.ALL);
220: capabilities.addType(FilterCapabilities.SPATIAL_BBOX);
221: capabilities.addType(FilterCapabilities.SPATIAL_CONTAINS);
222: capabilities.addType(FilterCapabilities.SPATIAL_CROSSES);
223: capabilities.addType(FilterCapabilities.SPATIAL_DISJOINT);
224: capabilities.addType(FilterCapabilities.SPATIAL_EQUALS);
225: capabilities.addType(FilterCapabilities.SPATIAL_INTERSECT);
226: capabilities.addType(FilterCapabilities.SPATIAL_OVERLAPS);
227: capabilities.addType(FilterCapabilities.SPATIAL_TOUCHES);
228: capabilities.addType(FilterCapabilities.SPATIAL_WITHIN);
229: capabilities.addType(FilterCapabilities.SPATIAL_DWITHIN);
230: capabilities.addType(FilterCapabilities.SPATIAL_BEYOND);
231:
232: // Does this need to be immutable???
233: return capabilities;
234: }
235:
236: /**
237: * Sets a SELECTIVITY clause that can be included with the spatial
238: * predicate to influence the query optimizer to exploit a spatial index
239: * if it exists.
240: *
241: * <p>
242: * The parameter should be of the form: <br>
243: * "SELECTIVITY 0.001" <br>
244: * where the numeric value is the fraction of rows that will be returned
245: * by using the index scan. This doesn't have to be true. The value
246: * 0.001 is typically used to force the use of the spatial in all cases if
247: * the spatial index exists.
248: * </p>
249: *
250: * @param string a selectivity clause
251: */
252: public void setSelectivityClause(String string) {
253: this .selectivityClause = string;
254: }
255:
256: /**
257: * Encodes an FidFilter.
258: *
259: * @param filter
260: *
261: * @throws RuntimeException DOCUMENT ME!
262: *
263: * @see org.geotools.filter.SQLEncoder#visit(org.geotools.filter.FidFilter)
264: */
265: public Object visit(Id filter, Object extraData) {
266: if (mapper == null) {
267: throw new RuntimeException(
268: "Must set a fid mapper before trying to encode FIDFilters");
269: }
270:
271: Set fids = filter.getIdentifiers();
272: LOGGER.finer("Exporting FID=" + fids);
273:
274: // prepare column name array
275: String[] colNames = new String[mapper.getColumnCount()];
276: String[] colDelimiters = new String[mapper.getColumnCount()];
277:
278: for (int i = 0; i < colNames.length; i++) {
279: colNames[i] = mapper.getColumnName(i);
280: int dataType = mapper.getColumnType(i);
281: if ((dataType == Types.VARCHAR) || (dataType == Types.CHAR)
282: || (dataType == Types.CLOB)) {
283: colDelimiters[i] = "'";
284: } else {
285: colDelimiters[i] = "";
286: }
287: }
288:
289: Iterator it = fids.iterator();
290: int i = 0;
291: while (it.hasNext()) {
292: try {
293: FeatureId fid = (FeatureId) it.next();
294: Object[] attValues = mapper
295: .getPKAttributes(fid.getID());
296:
297: out.write("(");
298:
299: for (int j = 0; j < attValues.length; j++) {
300: int colType = mapper.getColumnType(j);
301: out.write(escapeName(colNames[j]));
302: out.write(" = ");
303: out.write(colDelimiters[j]);
304: out.write(attValues[j].toString());
305: out.write(colDelimiters[j]);
306:
307: if (j < (attValues.length - 1)) {
308: out.write(" AND ");
309: }
310: }
311:
312: out.write(")");
313:
314: if (i < (fids.size() - 1)) {
315: out.write(" OR ");
316: }
317: i++;
318: } catch (java.io.IOException e) {
319: LOGGER.warning("IO Error exporting FID Filter.");
320: }
321: }
322: return extraData;
323: }
324:
325: /**
326: * Encode BEYOND and DWITHIN filters using ST_Distance function.
327: *
328: * @param filter a BinarySpatialOperator (should be DWithin or Beyond subclass)
329: * @param distance the distance value
330: * @param distanceUnits the units for the distance operation or blank/null if not used
331: * @param op the distance operator, either <. or >.
332: * @param filter the GeometryDistanceFilter
333: *
334: * @throws RuntimeException
335: */
336: private void encodeDistance(BinarySpatialOperator filter,
337: double distance, String distanceUnits, String op)
338: throws RuntimeException {
339: DefaultExpression left = (DefaultExpression) filter
340: .getExpression1();
341: DefaultExpression right = (DefaultExpression) filter
342: .getExpression2();
343: try {
344: int leftType = left.getType();
345: int rightType = right.getType();
346:
347: // The test below should use ATTRIBUTE_GEOMETRY but only the value ATTRIBUTE
348: if ((DefaultExpression.ATTRIBUTE == leftType)
349: && (DefaultExpression.LITERAL_GEOMETRY == rightType)) {
350: this .out.write("db2gse.ST_Distance(");
351: left.accept(this , null);
352: this .out.write(", ");
353: right.accept(this , Geometry.class);
354: if (!(distanceUnits == null || distanceUnits.length() == 0)) { // if units were specified
355: this .out.write(", \"" + distanceUnits + "\"");
356: }
357: this .out.write(") " + op + " " + distance);
358: addSelectivity(); // add selectivity clause if needed
359: } else {
360: String msg = "Unsupported left and right types: "
361: + leftType + ":" + rightType;
362: LOGGER.warning(msg);
363: throw new RuntimeException(msg);
364: }
365: } catch (java.io.IOException e) {
366: LOGGER
367: .warning("Filter not generated; I/O problem of some sort"
368: + e);
369: }
370: }
371:
372: public Object visit(DWithin filter, Object extraData) {
373:
374: double distance = filter.getDistance();
375: String distanceUnit = filter.getDistanceUnits();
376: encodeDistance(filter, distance, distanceUnit, "<");
377: return extraData;
378: }
379:
380: public Object visit(Beyond filter, Object extraData) {
381:
382: double distance = filter.getDistance();
383: String distanceUnit = filter.getDistanceUnits();
384: encodeDistance(filter, distance, distanceUnit, ">");
385: return extraData;
386: }
387:
388: protected Object visitBinarySpatialOperator(
389: BinarySpatialOperator filter, Object extraData) {
390: throw new RuntimeException(
391: "SQLEncoderDB2 must implement this method in order to handle geometries");
392: // return extraData;
393: }
394:
395: protected Object visitBinarySpatialOperator(
396: BinarySpatialOperator filter, Object extraData,
397: String db2Predicate) {
398: LOGGER.finer("Generating GeometryFilter WHERE clause for "
399: + filter);
400:
401: DefaultExpression left = (DefaultExpression) filter
402: .getExpression1();
403: DefaultExpression right = (DefaultExpression) filter
404: .getExpression2();
405:
406: // neither left nor right expression can be null
407: if ((null == left) || (null == right)) {
408: String msg = "Left or right expression is null - " + filter;
409: LOGGER.warning(msg);
410: throw new RuntimeException(msg);
411: }
412: try {
413: this .out.write("db2gse." + db2Predicate + "(");
414: left.accept(this , extraData);
415: this .out.write(", ");
416: right.accept(this , Geometry.class);
417: this .out.write(") = 1");
418:
419: addSelectivity(); // add selectivity clause if needed
420: } catch (IOException e) {
421: throw new RuntimeException(e);
422: }
423:
424: LOGGER.fine(this .out.toString());
425: return extraData;
426: }
427:
428: /**
429: * Encode a bounding-box filter using the EnvelopesIntersect spatial
430: * predicate.
431: *
432: * @param filter a BBOX filter object
433: * @param extraData not used
434: */
435: private Object encodeBBox(BBOX filter, Object extraData) {
436: LOGGER.finer("Generating EnvelopesIntersect WHERE clause for "
437: + filter);
438:
439: try {
440: String spatialColumn = filter.getPropertyName();
441: // The test below should use ATTRIBUTE_GEOMETRY but only the value ATTRIBUTE
442: this .out.write("db2gse.EnvelopesIntersect(");
443: this .out.write(escapeName(spatialColumn));
444: this .out.write(", ");
445: this .out.write(filter.getMinX() + ", " + filter.getMinY()
446: + ", " + filter.getMaxX() + ", " + filter.getMaxY()
447: + ", " + srid);
448: this .out.write(") = 1");
449: addSelectivity(); // add selectivity clause if needed
450: } catch (java.io.IOException e) {
451: LOGGER
452: .warning("Filter not generated; I/O problem of some sort"
453: + e);
454: }
455: return extraData;
456: }
457:
458: public Object visit(BBOX filter, Object extraData) {
459: return encodeBBox((BBOX) filter, extraData);
460: }
461:
462: public Object visit(Contains filter, Object extraData) {
463: return visitBinarySpatialOperator(
464: (BinarySpatialOperator) filter, extraData,
465: "ST_Contains");
466: }
467:
468: public Object visit(Crosses filter, Object extraData) {
469: return visitBinarySpatialOperator(
470: (BinarySpatialOperator) filter, extraData, "ST_Crosses");
471: }
472:
473: public Object visit(Disjoint filter, Object extraData) {
474: return visitBinarySpatialOperator(
475: (BinarySpatialOperator) filter, extraData,
476: "ST_Disjoint");
477: }
478:
479: public Object visit(Equals filter, Object extraData) {
480: return visitBinarySpatialOperator(
481: (BinarySpatialOperator) filter, extraData, "ST_Equals");
482: }
483:
484: public Object visit(Intersects filter, Object extraData) {
485: return visitBinarySpatialOperator(
486: (BinarySpatialOperator) filter, extraData,
487: "ST_Intersects");
488: }
489:
490: public Object visit(Overlaps filter, Object extraData) {
491: return visitBinarySpatialOperator(
492: (BinarySpatialOperator) filter, extraData,
493: "ST_Overlaps");
494: }
495:
496: public Object visit(Touches filter, Object extraData) {
497: return visitBinarySpatialOperator(
498: (BinarySpatialOperator) filter, extraData, "ST_Touches");
499: }
500:
501: public Object visit(Within filter, Object extraData) {
502: return visitBinarySpatialOperator(
503: (BinarySpatialOperator) filter, extraData, "ST_Within");
504: }
505:
506: /**
507: * Construct the appropriate geometry type from the WKT representation of a literal
508: * expression
509: *
510: * @param expression the expression turn into a geometry constructor.
511: *
512: * @throws IOException Passes back exception if generated by
513: * this.out.write()
514: */
515: public void visitLiteralGeometry(Literal expression)
516: throws IOException {
517: String wktRepresentation = wktWriter
518: .write((Geometry) expression.getValue());
519: int spacePos = wktRepresentation.indexOf(" ");
520: String geomType = wktRepresentation.substring(0, spacePos);
521: this .out.write("db2gse.ST_" + geomType + "('"
522: + wktRepresentation + "', " + this .srid + ")");
523: }
524:
525: protected void addSelectivity() throws IOException {
526: if (this .selectivityClause != null) {
527: this .out.write(" " + this.selectivityClause);
528: }
529: }
530: }
|