GeoTools : SQLEncoder Upgrade to GeoAPI Filters

A small step on the long road to internal use of GeoAPI Filters

There's a long road to internally using GeoAPI filters rather than Geotools filters. This is just a little piece of the work.

My motivation:

I was itching to get the ArcSDE datastore plugin moved completely off of the "old" filters and completely onto the "new" filters.
Most of the ArcSDE datastore plugin moved quite cleanly over, but there were a few bits of main and jdbc that were being pulled in, and which were using deprecated interfaces. So I set about to:

  1. Clean up, moving it from using "old" filters to "new" filters
  2. Stop using the SQLUnpacker class and instead use PostPreFilterSplittingVisitor for the SDE internal filter splitting needs

In order to complete this work I had to make two non-trivial decisions that I'd like to run by any interested parties (particularly the main and jdbc module maintainers: jody and cory!) Currently all tests pass and I'm synced to the latest svn revision with no conflicts.

Current status:

Items need to be completed in descending order.




class renaming/package questions answered


code committed to trunk


code QA'd/reviewed


(warning) supported datastores moved from SQLEncoder to 'new' SQLEncoder


(warning) unsupported datastores moved from SQLEncoder to 'new' SQLEncoder


SDE datastore moved from SQLEncoder to 'new' SQLEncoder

(warning) I'm committed to tracking down any code that uses the old SQLUnpacker or SQLEncoder classes and either:

  • Bugging the maintainer to change it
  • Changing it myself it it's not maintained (in unsupported)

Cleaning up SQLEncoder

Trying to clean up turned out to be a bad idea. There are many datastore plugins which subclass this class as a way of getting easy filter->SQL encoding support, plus a few customizations. Changing the underlying methods of SQLEncoder was no good as I don't really have the expertise to fix all the datastores at the same time.

So instead I created a new port of the class (called 'GeoAPIFilterToSQLEncoder') in the package. This new class is really just a port of the old SQLEncoder class, but made to use "new" filters. I then deprecated the old org.geotools.filter.SQLEncoder class (and it's exceptions) and ported its tests/exceptions over to the new GeoAPIFilterToSQLEncoder.

I left a note in SQLEncoder about which class replaces this now-deprecated class.

QUESTION: What should the name of the new SQLEncoder class be? Is the new different name for the class in a different package a good one? Or should it keep the same name, just change package?


Saul's original solution

SQLEncoder gets deprecated. New GeoAPI filter encoder is named GeoAPIFilterToSQLEncoder


Saul's alternate suggestion

SQLEncoder gets deprecated. New GeoAPI filter encoder is named identically, but is in different package. Possible confusion due to identical names for different classes.

(tick) - chosen because saul liked it

Jody's suggestion 1

SQLEncoder gets deprecated. Long: "FilterToSQL" - ArcsdeFilterToSQL, OracleFilterToSQL, PostgisFilterToSQL


Jody's suggestion 2

SQLEncoder gets deprecated. Medium: "FilterEncoder" - ArcsdeFilterEncoder, OracleFilterEncoder, PostgisFilterEncoder


Jody's suggestion 3

SQLEncoder gets deprecated. Short: "QL" - ArcsdeSQL, OracleQL, PostGISQL


Jody's suggestion 4

SQLEncoder gets deprecated. Abstract: "Dialect" - ArcsdeDialect, OracleDialect, PostGISDialect,

QUESTION: What should the package be of the new SQLEncoder class (regardless of its name)? The old SQLEncoder lived in org.geotools.filter. SHould the new one live in org.opengis.filter? Or is living in good enough/better?


Saul's original solution

'new' SQLEncoder is put into package Jody's comment on this: ' is probably for the best'


Jody's alternate suggestion

Copy what was done for CQL - 'new' SQLEncoder is put into packgae org.geotools.sql

After creating and testing this new class, I then ported the SQLEncoderSDE over to use the new class, and everything works great with 100% "new" filters.

Move from SQLUnpacker to PostPreProcessFilterSplittingVisitor

Moving from SQLUnpacker to PostPreProcessFilterSplittingVisitor is fairly painless, once you've figured out how PPPFSV works. I ported PPPFSV to use the new filters, and it works just fine now.

The problem of FilterCapabilities

FilterCapabilities now represents two things. It represents the capabilites of a filter as expressed with old filters, and the capabilities of a filter as expressed with new filters. This means that the FilterCapabilities class is a bit of a frankenstein, with code handling each individual case/bit in each method and use-case.

QUESTION: What do do with FilterCapabilities?


Dual nature is accepted as an "ok thing", and the class is re-architected to account for that.


Dual nature is rejected as a confusing/bad thing, and the class is split into an older, deprecated version and a newer version called "FilterCapabilitiesImpl" which implements the org.opengis.filter.capabilities.FilterCapabilities interface (or whatever the right interface is in geoapi).

This is not the first time this issue has been raised. It's a part of the 2.5.x timeline and it'll raise its head in a few places. Just pointing out some possible routes here. Personally I vote for the latter route.

Jody's comment:
'I would like to move towards the opengis.filter.capabilities - but I imagine we will learn a few things (and may need to revise the interfaces - justin already ran into some limitations around Function?).'

A list of files changed and the nature of the changes

Index: modules/library/jdbc/src/main/java/org/geotools/filter/
Index: modules/library/jdbc/src/main/java/org/geotools/filter/

Change to support "new" filters rather than "old" filters
Index: modules/library/main/src/main/java/org/geotools/filter/visitor/

Minor changes/cleanup to support the new PostPreProcessFilterSplittingVisitor:
Index: modules/library/jdbc/src/main/java/org/geotools/data/jdbc/
Index: modules/library/main/src/test/java/org/geotools/filter/visitor/
Index: modules/library/main/src/test/java/org/geotools/filter/visitor/
Index: modules/library/main/src/test/java/org/geotools/filter/visitor/
Index: modules/library/main/src/test/java/org/geotools/filter/visitor/
Index: modules/plugin/wfs/src/main/java/org/geotools/data/wfs/

More substantial changes were required to get FilterCapabilities to work as a representation of both new and old FilterCapabilities.
Index: modules/library/main/src/main/java/org/geotools/filter/

New files:


Prepared object in WHERE clause of SQL query

Raw text SQL to be used with usual JDBC statements is not appropriate solution for real production applications while geometries may have thousands of vertices. Practice has shown that Oracle JDBC driver just fails with a bit more bigger than average size geometries when they are represented as a text in SQL query. As concerns Oracle Spatial the prepared statement and the geometry being encoded into Oracle STRUCT object and passed to the statement as a prepared value is a solution. Geometries do not only come in ResultSet from SELECT query or being passed in UPDATE or INSERT queries. Also the complex geometry may be used as a spatial filter in WHERE clause.

FilterToSQL does not contain vendor specific details of geometries encoding. So, the proposal is a PreparedFilterToSQL abstract class extending the basic one FilterToSQL  for implementation of custom SQL encoders that handle geometries as prepared objects.

 public abstract class PreparedFilterToSQL extends FilterToSQL {

    protected ArrayList preparedObjectsList = new ArrayList();

    public PreparedFilterToSQL() {

    public PreparedFilterToSQL( Writer out ){

    protected void addPreparedObject( Object preparedObject ) {

     * Returns an array of objects for prepared SQL query.
     * <p>
     * @return
    public Object[] getPreparedObjects() {
        return preparedObjectsList.toArray();

     * Returns the count of objects for prepared SQL query.
     * @return the number of prepared object.
    public int getPreparedObjectsCount() {
        return preparedObjectsList.size();

 OraclePreparedFilterToSQL extends this class and adds prepared objects to the list if necessary. Then the caller of the SQL encoder that performs query executing may just ask it to provide an array of objects and pass them without knowing the their nature.

	Object[] preparedObjects = ((PreparedFilterToSQL) sqlEncoder).getPreparedObjects();
	pstmt = conn.prepareStatement(selectQuery);

	for( int i = 0; i < preparedObjects.length; i++ ) {
		pstmt.setObject(i + 1, preparedObjects[i]);
Posted by at Jul 02, 2007 13:39