Quick comments on: nearest( attributeName, maxFeatures, filter )
- attributeName: can this be any geometry expression or is it limited to
attribute name
- filter: you mention this as CQL but I expect an actual filter can be passed in
For the "vendor specific hint":
- would rather have both parameters their, and marked as optional (i.e. may be
supported by the implementation)
Supporting both modes of operation seems to be throwing this design a curve
ball.
Some random ideas on that (but nothing good):
- bad idea - two pass: could be used is as an aggregate function, stage your
feature collection using the "category > 3" and then treat "nearest neighbour"
as an aggregate function like unique or union.
- high level optimisation: Make use of Query sortBy with a distance expression,
filter by category 3, and set max features to 10. Recognise the whole mess as
an attempt to call SQL_NN and implement it as such.
--
Jody Garnett
On Tuesday, 2 October 2012 at 11:03 PM, Andrea Aime wrote:
> 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]
> (mailto:[email protected])
> https://lists.sourceforge.net/lists/listinfo/geotools-devel
>
>
------------------------------------------------------------------------------
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
