001: /*
002: * GeoTools - OpenSource mapping toolkit
003: * http://geotools.org
004: * (C) 2002-2006, GeoTools Project Managment Committee (PMC)
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: package org.geotools.filter;
017:
018: import java.io.IOException;
019: import java.sql.Date;
020: import java.util.logging.Logger;
021:
022: import org.geotools.filter.FilterType;
023: import org.opengis.filter.ExcludeFilter;
024: import org.opengis.filter.Id;
025: import org.opengis.filter.IncludeFilter;
026: import org.opengis.filter.PropertyIsBetween;
027: import org.opengis.filter.PropertyIsLike;
028: import org.opengis.filter.PropertyIsNull;
029: import org.opengis.filter.expression.Add;
030: import org.opengis.filter.expression.Divide;
031: import org.opengis.filter.expression.Multiply;
032: import org.opengis.filter.expression.Subtract;
033: import org.opengis.filter.spatial.BBOX;
034: import org.opengis.filter.spatial.Contains;
035: import org.opengis.filter.spatial.Crosses;
036: import org.opengis.filter.spatial.Disjoint;
037: import org.opengis.filter.spatial.Equals;
038: import org.opengis.filter.spatial.Intersects;
039: import org.opengis.filter.spatial.Overlaps;
040: import org.opengis.filter.spatial.Touches;
041: import org.opengis.filter.spatial.Within;
042:
043: import com.vividsolutions.jts.geom.Geometry;
044: import com.vividsolutions.jts.geom.LineString;
045: import com.vividsolutions.jts.geom.LinearRing;
046: import com.vividsolutions.jts.io.WKTWriter;
047:
048: /**
049: * Encodes a filter into a SQL WHERE statement for postgis. This class adds
050: * the ability to turn geometry filters into sql statements if they are
051: * bboxes.
052: *
053: * @author Chris Holmes, TOPP
054: *
055: * @task TODO: integrated with SQLEncoderPostgisGeos.java, as there no real
056: * reason to have two different classes. We just need to do testing to
057: * make sure both handle everything. At the very least have the geos
058: * one extend more intelligently.
059: * @source $URL: http://svn.geotools.org/geotools/tags/2.4.1/modules/plugin/postgis/src/main/java/org/geotools/filter/SQLEncoderPostgis.java $
060: */
061: public class SQLEncoderPostgis extends SQLEncoder implements
062: org.geotools.filter.FilterVisitor {
063: /** Standard java logger */
064: private static Logger LOGGER = org.geotools.util.logging.Logging
065: .getLogger("org.geotools.filter");
066:
067: /** To write geometry so postgis can read it. */
068: private static WKTWriter wkt = new WKTWriter();
069:
070: /**
071: * The srid of the schema, so the bbox conforms. Could be better to have
072: * it in the bbox filter itself, but this works for now.
073: */
074: private int srid;
075:
076: /** The geometry attribute to use if none is specified. */
077: private String defaultGeom;
078:
079: /** Whether the BBOX filter should be strict (using the exact geom), or
080: * loose (using the envelopes) */
081: protected boolean looseBbox = false;
082:
083: /**
084: * Whether the installed PostGIS has GEOS support. Default is false for
085: * backwards compatibility.
086: */
087: protected boolean supportsGEOS = false;
088:
089: /**
090: * Empty constructor TODO: rethink empty constructor, as BBOXes _need_ an
091: * SRID, must make client set it somehow. Maybe detect when encode is
092: * called?
093: *
094: */
095: public SQLEncoderPostgis() {
096: capabilities = createFilterCapabilities();
097: setSqlNameEscape("\"");
098: }
099:
100: public SQLEncoderPostgis(boolean looseBbox) {
101: this ();
102: this .looseBbox = looseBbox;
103: }
104:
105: /**
106: *
107: * @see org.geotools.filter.SQLEncoder#createFilterCapabilities()
108: */
109: protected FilterCapabilities createFilterCapabilities() {
110: FilterCapabilities capabilities = new FilterCapabilities();
111:
112: capabilities.addType(FilterCapabilities.NONE);
113: capabilities.addType(IncludeFilter.class);
114: capabilities.addType(FilterCapabilities.ALL);
115: capabilities.addType(ExcludeFilter.class);
116: capabilities.addType(FilterCapabilities.FID);
117: capabilities.addType(Id.class);
118: capabilities.addType(FilterCapabilities.NULL_CHECK);
119: capabilities.addType(PropertyIsNull.class);
120: capabilities.addType(FilterCapabilities.BETWEEN);
121: capabilities.addType(PropertyIsBetween.class);
122: capabilities.addType(FilterCapabilities.LOGICAL);
123: capabilities.addAll(FilterCapabilities.LOGICAL_OPENGIS);
124: capabilities.addType(FilterCapabilities.SIMPLE_ARITHMETIC);
125: capabilities.addType(Add.class);
126: capabilities.addType(Multiply.class);
127: capabilities.addType(Subtract.class);
128: capabilities.addType(Divide.class);
129: capabilities.addType(FilterCapabilities.SIMPLE_COMPARISONS);
130: capabilities
131: .addAll(FilterCapabilities.SIMPLE_COMPARISONS_OPENGIS);
132: capabilities.addType(FilterCapabilities.SPATIAL_BBOX);
133: capabilities.addType(BBOX.class);
134: capabilities.addType(FilterCapabilities.LIKE);
135: capabilities.addType(PropertyIsLike.class);
136:
137: if (supportsGEOS) {
138: capabilities.addType(FilterCapabilities.SPATIAL_CONTAINS);
139: capabilities.addType(Contains.class);
140: capabilities.addType(FilterCapabilities.SPATIAL_CROSSES);
141: capabilities.addType(Crosses.class);
142: capabilities.addType(FilterCapabilities.SPATIAL_DISJOINT);
143: capabilities.addType(Disjoint.class);
144: capabilities.addType(FilterCapabilities.SPATIAL_EQUALS);
145: capabilities.addType(Equals.class);
146: capabilities.addType(FilterCapabilities.SPATIAL_INTERSECT);
147: capabilities.addType(Intersects.class);
148: capabilities.addType(FilterCapabilities.SPATIAL_OVERLAPS);
149: capabilities.addType(Overlaps.class);
150: capabilities.addType(FilterCapabilities.SPATIAL_TOUCHES);
151: capabilities.addType(Touches.class);
152: capabilities.addType(FilterCapabilities.SPATIAL_WITHIN);
153: capabilities.addType(Within.class);
154: }
155:
156: // TODO: add SPATIAL_BEYOND, DWITHIN to capabilities and support in
157: // visit(GeometryFilter)
158:
159: return capabilities;
160: }
161:
162: /**
163: * Constructor with srid.
164: *
165: * @param srid spatial reference id to encode geometries with.
166: */
167: public SQLEncoderPostgis(int srid) {
168: this (true);
169: this .srid = srid;
170: }
171:
172: /**
173: * Sets whether the Filter.BBOX query should be 'loose', meaning that it
174: * should just doing a bounding box against the envelope. If set to
175: * <tt>false</tt> then the BBOX query will perform a full intersects
176: * against the geometry, ensuring that it is exactly correct. If
177: * <tt>true</tt> then the query will likely perform faster, but may not
178: * be exactly correct.
179: *
180: * @param isLooseBbox whether the bbox should be loose or strict.
181: */
182: public void setLooseBbox(boolean isLooseBbox) {
183: this .looseBbox = isLooseBbox;
184: }
185:
186: /**
187: * Gets whether the Filter.BBOX query will be strict and use an intersects
188: * or 'loose' and just operate against the geometry envelopes.
189: *
190: * @return <tt>true</tt> if this encoder is going to do loose filtering.
191: */
192: public boolean isLooseBbox() {
193: return looseBbox;
194: }
195:
196: /**
197: * Sets a spatial reference system ESPG number, so that the geometry can be
198: * properly encoded for postgis. If geotools starts actually creating
199: * geometries with valid srids then this method will no longer be needed.
200: *
201: * @param srid the integer code for the EPSG spatial reference system.
202: */
203: public void setSRID(int srid) {
204: this .srid = srid;
205: }
206:
207: /**
208: * Sets the default geometry, so that filters with null for one of their
209: * expressions can assume that the default geometry is intended.
210: *
211: * @param name the name of the default geometry Attribute.
212: *
213: * @task REVISIT: pass in a featureType so that geometries can figure out
214: * their own default geometry?
215: */
216: public void setDefaultGeometry(String name) {
217: //Do we really want clients to be using malformed filters?
218: //I mean, this is a useful method for unit tests, but shouldn't
219: //fully formed filters usually be used? Though I guess adding
220: //the option wouldn't hurt. -ch
221: this .defaultGeom = name;
222: }
223:
224: public void setSupportsGEOS(boolean supports) {
225: boolean oldValue = this .supportsGEOS;
226: this .supportsGEOS = supports;
227: if (capabilities == null || supports != oldValue) {
228: //regenerate capabilities
229: capabilities = createFilterCapabilities();
230: }
231: }
232:
233: public boolean getSupportsGEOS() {
234: return supportsGEOS;
235: }
236:
237: private void encodeGeomFilter(GeometryFilter filter,
238: String function, String comparison, boolean useIndex) {
239: //this method blindly assumes that the filter is supported
240: DefaultExpression left = (DefaultExpression) filter
241: .getLeftGeometry();
242: DefaultExpression right = (DefaultExpression) filter
243: .getRightGeometry();
244:
245: try {
246: //should we use the index?
247: if (useIndex) {
248: encodeExpression(left);
249: out.write(" && ");
250: encodeExpression(right);
251: }
252:
253: // looseBbox only applies to GEOMETRY_BBOX, so unless this is a
254: // BBOX, we will always generate the full SQL.
255: if (filter.getFilterType() != AbstractFilter.GEOMETRY_BBOX
256: || !looseBbox) {
257: if (useIndex) {
258: out.write(" AND ");
259: }
260: out.write(function + "(");
261: encodeExpression(left);
262: out.write(", ");
263: encodeExpression(right);
264: out.write(")" + comparison);
265: }
266: } catch (java.io.IOException ioe) {
267: LOGGER.warning("Unable to export filter" + ioe);
268: }
269: }
270:
271: private void encodeExpression(DefaultExpression expr)
272: throws IOException {
273: if (expr == null) {
274: out.write("\"" + defaultGeom + "\"");
275: } else {
276: expr.accept(this );
277: }
278: }
279:
280: /**
281: * Turns a geometry filter into the postgis sql bbox statement.
282: *
283: * @param filter the geometry filter to be encoded.
284: *
285: * @throws RuntimeException for IO exception (need a better error)
286: */
287: public void visit(GeometryFilter filter) throws RuntimeException {
288: LOGGER.finer("exporting GeometryFilter");
289:
290: short filterType = filter.getFilterType();
291: DefaultExpression left = (DefaultExpression) filter
292: .getLeftGeometry();
293: DefaultExpression right = (DefaultExpression) filter
294: .getRightGeometry();
295:
296: //if geos is not supported, all we can use is distance = 0 for bbox
297: if (!supportsGEOS) {
298: if (filterType != AbstractFilter.GEOMETRY_BBOX) {
299: throw new RuntimeException(
300: "without GEOS support, only the BBOX function is supported; failed to encode "
301: + filterType);
302: }
303: encodeGeomFilter(filter, "distance", " < 0.00001", true);
304: return;
305: }
306:
307: // Figure out if we need to constrain this query with the && constraint.
308: int literalGeometryCount = 0;
309:
310: if ((left != null)
311: && (left.getType() == DefaultExpression.LITERAL_GEOMETRY)) {
312: literalGeometryCount++;
313: }
314:
315: if ((right != null)
316: && (right.getType() == DefaultExpression.LITERAL_GEOMETRY)) {
317: literalGeometryCount++;
318: }
319:
320: boolean constrainBBOX = (literalGeometryCount == 1);
321: boolean onlyBbox = filterType == AbstractFilter.GEOMETRY_BBOX
322: && looseBbox;
323:
324: try {
325:
326: // DJB: disjoint is not correctly handled in the pre-march 22/05
327: // version
328: // I changed it to not do a "&&" index search for disjoint because
329: // Geom1 and Geom2 can have a bbox overlap and be disjoint
330: // I also added test case.
331: // NOTE: this will not use the index, but its unlikely that using
332: // the index
333: // for a disjoint query will be the correct thing to do.
334:
335: // DJB NOTE: need to check for a NOT(A intersects G) filter
336: // --> NOT( (A && G) AND intersects(A,G))
337: // and check that it does the right thing.
338:
339: constrainBBOX = constrainBBOX
340: && (filterType != AbstractFilter.GEOMETRY_DISJOINT);
341:
342: if (constrainBBOX) {
343: encodeExpression(left);
344: out.write(" && ");
345: encodeExpression(right);
346:
347: if (!onlyBbox) {
348: out.write(" AND ");
349: }
350: }
351:
352: String closingParenthesis = ")";
353:
354: if (!onlyBbox) {
355: if (filterType == AbstractFilter.GEOMETRY_EQUALS) {
356: out.write("equals");
357: } else if (filterType == AbstractFilter.GEOMETRY_DISJOINT) {
358: out.write("NOT (intersects");
359: closingParenthesis += ")";
360: } else if (filterType == AbstractFilter.GEOMETRY_INTERSECTS) {
361: out.write("intersects");
362: } else if (filterType == AbstractFilter.GEOMETRY_CROSSES) {
363: out.write("crosses");
364: } else if (filterType == AbstractFilter.GEOMETRY_WITHIN) {
365: out.write("within");
366: } else if (filterType == AbstractFilter.GEOMETRY_CONTAINS) {
367: out.write("contains");
368: } else if (filterType == AbstractFilter.GEOMETRY_OVERLAPS) {
369: out.write("overlaps");
370: } else if (filterType == AbstractFilter.GEOMETRY_BBOX) {
371: out.write("intersects");
372: } else if (filterType == AbstractFilter.GEOMETRY_TOUCHES) {
373: out.write("touches");
374: } else {
375: // this will choke on beyond and dwithin
376: throw new RuntimeException(
377: "does not support filter type "
378: + filterType);
379: }
380: out.write("(");
381:
382: encodeExpression(left);
383: out.write(", ");
384: encodeExpression(right);
385:
386: out.write(closingParenthesis);
387: }
388: } catch (java.io.IOException ioe) {
389: LOGGER.warning("Unable to export filter" + ioe);
390: throw new RuntimeException("io error while writing", ioe);
391: }
392: }
393:
394: /**
395: * Checks to see if the literal is a geometry, and encodes it if it is, if
396: * not just sends to the parent class.
397: *
398: * @param expression
399: * the expression to visit and encode.
400: *
401: * @throws IOException
402: * for IO exception (need a better error)
403: */
404: public void visitLiteralGeometry(LiteralExpression expression)
405: throws IOException {
406: Geometry bbox = (Geometry) expression.evaluate(null,
407: Geometry.class);
408: String geomText = null;
409: if (bbox instanceof LinearRing) {
410: //postgis does not handle linear rings, convert to just a line string
411: LineString lineString = new LineString(((LinearRing) bbox)
412: .getCoordinateSequence(), bbox.getFactory());
413: geomText = wkt.write(lineString);
414: } else {
415: geomText = wkt.write(bbox);
416: }
417:
418: out.write("GeometryFromText('" + geomText + "', " + srid + ")");
419: }
420:
421: public void visit(LikeFilter filter)
422: throws UnsupportedOperationException {
423: char esc = filter.getEscape().charAt(0);
424: char multi = filter.getWildcardMulti().charAt(0);
425: char single = filter.getWildcardSingle().charAt(0);
426: String pattern = LikeFilterImpl.convertToSQL92(esc, multi,
427: single, filter.getPattern());
428:
429: DefaultExpression att = (DefaultExpression) filter.getValue();
430:
431: try {
432: out.write(" ( ");
433: att.accept(this );
434:
435: out.write(" LIKE '");
436: out.write(pattern);
437: out.write("' ");
438:
439: //JD: this is an ugly ugly hack!! hopefully when the new feature model is around we can
440: // fix this
441: //check for context for a date
442: if (att instanceof AttributeExpression && context != null
443: && java.util.Date.class.isAssignableFrom(context)) {
444: //if it is a date, add additional logic for a timestamp, or a timestamp with
445: // timezone
446: out.write(" OR ");
447: att.accept(this );
448: out.write(" LIKE '");
449: out.write(pattern + " __:__:__'"); //timestamp
450:
451: out.write(" OR ");
452: att.accept(this );
453: out.write(" LIKE '");
454: out.write(pattern + " __:__:_____'"); //timestamp with time zone
455: }
456:
457: out.write(" ) ");
458:
459: } catch (java.io.IOException ioe) {
460: throw new RuntimeException(IO_ERROR, ioe);
461: }
462:
463: }
464:
465: /**
466: * Checks to see if the literal is a geometry, and encodes it if it is, if
467: * not just sends to the parent class.
468: *
469: * @param expression the expression to visit and encode.
470: *
471: * @throws RuntimeException for IO exception (need a better error)
472: */
473: public void visit(LiteralExpression expression)
474: throws RuntimeException {
475: LOGGER.finer("exporting LiteralExpression");
476:
477: try {
478: if (expression.getType() == DefaultExpression.LITERAL_GEOMETRY) {
479: visitLiteralGeometry(expression);
480: } else {
481: super .visit(expression);
482: }
483: } catch (java.io.IOException ioe) {
484: LOGGER.warning("Unable to export expression" + ioe);
485: throw new RuntimeException("io error while writing", ioe);
486: }
487: }
488:
489: /**
490: * Writes the SQL for a Compare Filter.
491: *
492: * DJB: note, postgis overwrites this implementation because of the way
493: * null is handled. This is for <PropertyIsNull> filters and <PropertyIsEqual> filters
494: * are handled. They will come here with "property = null".
495: * NOTE:
496: * SELECT * FROM <table> WHERE <column> isnull; -- postgresql
497: * SELECT * FROM <table> WHERE isnull(<column>); -- oracle???
498: *
499: * @param filter the comparison to be turned into SQL.
500: *
501: * @throws RuntimeException for io exception with writer
502: */
503: public void visit(CompareFilter filter) throws RuntimeException {
504: LOGGER.finer("exporting SQL ComparisonFilter");
505:
506: DefaultExpression left = (DefaultExpression) filter
507: .getLeftValue();
508: DefaultExpression right = (DefaultExpression) filter
509: .getRightValue();
510: LOGGER.finer("Filter type id is " + filter.getFilterType());
511: LOGGER
512: .finer("Filter type text is "
513: + comparisions.get(new Integer(filter
514: .getFilterType())));
515:
516: String type = (String) comparisions.get(new Integer(filter
517: .getFilterType()));
518:
519: try {
520: // a bit hacky, but what else can we really do?
521: if ((right == null)
522: && (filter.getFilterType() == FilterType.COMPARE_EQUALS)) {
523: left.accept(this );
524: out.write(" isnull");
525: } else {
526: //check for case insentivity (TODO: perhaps move this up to jdbc)
527: if (!filter.isMatchingCase()) {
528: //only for == or !=
529: if (filter.getFilterType() == Filter.COMPARE_EQUALS
530: || filter.getFilterType() == Filter.COMPARE_NOT_EQUALS) {
531:
532: //only for strings
533: if (left.getType() == Expression.LITERAL_STRING
534: || right.getType() == Expression.LITERAL_STRING) {
535:
536: out.write("lower(");
537: left.accept(this );
538: out.write(")");
539: out.write(" " + type + " ");
540: out.write("lower(");
541: right.accept(this );
542: out.write(")");
543:
544: return;
545: }
546: }
547: }
548:
549: //normal execution
550: left.accept(this );
551: out.write(" " + type + " ");
552: right.accept(this );
553: }
554: } catch (java.io.IOException ioe) {
555: throw new RuntimeException(IO_ERROR, ioe);
556: }
557: }
558:
559: }
|