Hi,
I recently got a mandate to expose the SDO_NN Oracle functionality as part
of
filtering constructs, want to discuss a bit how this could be done.
Generally speaking both Oracle and PostGIS 2.0 K nearest neighbours
functionality
depends on using a particular function/operator to specify the distance
ordering,
and a modification to the limit of records returned by the features.
This is ill suited for a generic spatial operator that can be put in any
place in a complex
filter, but it's doable if we use a local subquery to run the KNN operator,
something like:
select *
from mytable
where category > 3 and
id in (select id from mytable where SDO_NN(the_geom,
MDSYS.SOD_GEOMETRY(...), 'sod_batch_size=0') = TRUE and ROWNUM < 11)
to return the 10 features close to the reference geometry.
For postgis 2.0 it would be similar:
select *
from mytable
where category > 3 and
id in (select id from mytable order by the_geom <-> 'wkt'::geometry limit
10)
(that operator only works against the bbox centers, so it's not a true KNN,
but close enough I guess)
One interesting twist about the above queries is that they return the
features
with category > 3 among the 10 that are closest to the reference.
A different query is needed in order to get the 10 closest among those that
have category > 3.
To satisfy both modes of operation I guess a filter function like the
following would be suitable:
nearest(attributeName, maxFeatures, filter)
where filter is an optional CQL expression representing the extra filters
that we want to run
before the KNN filtering is done. For example, the above query would be
represented by:
category > 3 and
while to get the 10 closest features among the ones that have category > 3
one would use:
nearest(the_geom, 10, 'category > 3')
which should be translated into:
select *
from mytable
where
id in (select id from mytable where SDO_NN(the_geom,
MDSYS.SOD_GEOMETRY(...), 'sdo_batch_size=0') = TRUE and category > 3 and
ROWNUM < 11)
The filter function would have no in memory working mode, and would just be
used as a placeholder
for datastores that do support native translation, if called in memory it
would throw an exception.
Actually, there is one more thing. Both Oracle and Postgis implementations
would benefit
from extra vendor specific hints, for example the SDO_NN can be optimized
by providing
a batch size > 0, the postgis one has a choice between the <-> operator and
the <#> one.
So I'm wondering about adding a 4th optional parameter that would serve as
the vendor specific
hint, for oracle it would be the batch size, for postgis a flag specifying
what NN operator to use.
Btw, so far I've talked both about postgis and oracle, but the funding is
directed towards
having the operator only in Oracle, I'm just trying to see if we can get a
generic filter function
enough that would be useful against other backends.
Otherwise a different sensible choice could be to call the function SDO_NN,
so that it's
very explicit it is geared towards Oracle only and makes sense only if the
backend is the
specified one
Opinions?
Cheers
Andrea
--
==
Our support, Your Success! Visit http://opensdi.geo-solutions.it for more
information.
==
Ing. Andrea Aime
@geowolf
Technical Lead
GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549
http://www.geo-solutions.it
http://twitter.com/geosolutions_it
-------------------------------------------------------
------------------------------------------------------------------------------
Don't let slow site performance ruin your business. Deploy New Relic APM
Deploy New Relic app performance management and know exactly
what is happening inside your Ruby, Python, PHP, Java, and .NET app
Try New Relic at no cost today and get our sweet Data Nerd shirt too!
http://p.sf.net/sfu/newrelic-dev2dev
_______________________________________________
GeoTools-Devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geotools-devel
