[Geoserver-devel] spatial filters on Oracle

GeoServer GeoServer Developer
1

Hi,

When filtering features spatially in a GetFeature request, I noticed that on an Oracle 10g database, Geoserver is quite slow using for instance DWithin and Intersects, but it gives the correct feature(s) back. The table to filter on isn't that big (65.000 records), but it takes Geoserver 10-20 seconds to give the repsonse.

In the SQLEncoderOracle, is stated that only filters on a GEOMETRY_BBOX are implemented, while there is a void
doSdoRelate(). My question is twofold:

1. Geoserver being slow on filters like DWithin, is that caused by Geotools taking the filter over (and thus selecting all 65.000 features before filtering), because the filter is not implemented for Oracle?

Maybe I'll try to implement some other filters for Oracle, but that could take me a while I think... so therefore I'd like to know whether support for other filters is expected/planned for Oracle (I can't see anything on it in Jira).

Thanks!

Thijs

------------------------------------------------------------
Geodan IT b.v.
President Kennedylaan 1
1079 MB Amsterdam (NL)
------------------------------------------------------------
Tel: +31 (0)20 - 5711 311
Fax: +31 (0)20 - 5711 333
------------------------------------------------------------
E-mail: thijs.brentjens@anonymised.com
Website: www.geodan.nl
Disclaimer: www.geodan.nl/disclaimer
------------------------------------------------------------

2

In the SQLEncoderOracle, is stated that only filters on a GEOMETRY_BBOX

are

implemented, while there is a void
doSdoRelate().

There is a void doSdoRelate() ? Are you looking at the one in 2.0.x
branch of GeoServer? It should be there. The comment at the top is
wrong, Sean wrote support for more than BBOX, and I fixed up a few to get
them working right.

1. Geoserver being slow on filters like DWithin, is that caused by

Geotools

taking the filter over (and thus selecting all 65.000 features before
filtering), because the filter is not implemented for Oracle?

Dwithin is not implemented, your suspicions are correct there (as are the
reasons for it being slow). But Intersects should be (though if you're
using geoserver pre-1.2.3 then make sure it is Intersect, since that's the
correct way, for 1.2.3 I made it so intersects works as well, since it is
badly defined in the ogc specs as to which it should be when, in which
case the filter isn't being used at all, it would really return all
records). The createFilterCapabilities method shows which ones are
supported . BBOX, contains, crosses, disjoint, equals, intersects,
overlaps, touches, and within. Dwithin and Beyond are the two ones not
implemented.

Maybe I'll try to implement some other filters for Oracle, but that

could

take me a while I think... so therefore I'd like to know whether support
for other filters is expected/planned for Oracle (I can't see anything

on

it in Jira).

I'm not planning dwithin and beyond anytime soon, and they would be a
great contribution. The rest should work fine, if they're not let me
know, and try out Intersect in the filter instead of Intersects. Go ahead
and make a jira task for yourself for those two.

Best regards,

--
Chris