Geoserver CQL + SQL Server Plugin - .Filter Statement

Question

Does anyone know why GeoServer's SQL Server Plugin when making a WFS request with a spatial cql/ecql predicate ends up adding a SQL .Filter to the generated query in addition to the spatial predicate included in the request? And/or how to make it not do so?

Example:

cql_filter=WITHIN(the_geom, POLYGON((-117.86681324243546 33.63613597564614,-117.86551505327225 33.63613597564614,-117.86551505327225 33.635347676345916,-117.86681324243546 33.635347676345916,-117.86681324243546 33.63613597564614)))

Translates to the following SQL Query: SELECT count( * ) FROM (SELECT * FROM dbo.Whatever) as "vtable" WHERE "the_geom".Filter(geometry::STGeomFromText('POLYGON ((-117.86681324243546 33.63613597564614, -117.86551505327225 33.63613597564614, -117.86551505327225 33.635347676345916, -117.86681324243546 33.635347676345916, -117.86681324243546 33.63613597564614))', 4269)) = 1 AND "the_geom".STWithin(geometry::STGeomFromText('POLYGON ((-117.86681324243546 33.63613597564614, -117.86551505327225 33.63613597564614, -117.86551505327225 33.635347676345916, -117.86681324243546 33.635347676345916, -117.86681324243546 33.63613597564614))', 4269)) = 1

It seems that .Filter is superfluous and in many cases drastically increases the query execution time (compared to the same query with just one or the other). I can't seem to figure out why this would be necessary nor how to tell it to stop.

From my testing I'm finding that every request that has a spatial cql/ecql predicate that executes a layer from a SQL Server DataStore does this. Meaning that all of our spatial WFS queries have an additional WHERE clause that potentially increases their execution time. We've found this to be true on several different versions of GeoServer (2.10.0, 2.12.1), all versions of WFS (1.0, 1.1.1, 2.0) and changing the spatial predicate (CONTAINS, WITHIN, INTERSECTS, etc.) doesn't seem to change anything. When the spatial predicate is removed, so are BOTH the spatial SQL statements.

Full Example Request:

http://localhost:8080/geoserver/wfs?service=wfs&REQUEST=GetFeature&OUTPUTFORMAT=application/json&version=2.0&typeName=Whatever&cql_filter=WITHIN(the_geom, POLYGON((-117.86681324243546 33.63613597564614,-117.86551505327225 33.63613597564614,-117.86551505327225 33.635347676345916,-117.86681324243546 33.635347676345916,-117.86681324243546 33.63613597564614)))

UPDATE Based on Andrea's information below, perhaps the "superfluous" categorization is going a bit far. I personally don't know if SQL 2016 still requires a .Filter to force spatial index usage...so it's entirely possible they're right on the money and it does need to be there for that purpose. In any case, this explains WHY this happens and hopefully will help someone else who is scratching their head on this one in the future.

Answer 1

When the plugin was developed Filter was the only way to force usage of a spatial index. Maybe more recent versions of SQLServer do not have this requirement anymore and will leverage the spatial index seeing a simple STWithin?

As an alternative hypothesis, the query above is using the spatial index in a case where it would be better to do a full scan, e.g, when the query selects most of the data (indexes are best used only when quite selective).

Changing this behavior is possible, you can make a pull request against the GeoTools code. Before doing so, make sure you test different versions of SQLServer or find documentation reporting from which version .Filter is no more needed.

As an alternative, you can look in commercial support, they will do the coding and research needed for you (it's hard to find people willing to crush their spare time on a commercial database integration). See here: http://geoserver.org/support/