i have a query which returns the sighting_id's to which are most further away from a set of XY coordinates. im trying to create function based index on the XY co-ordinates to speed up the query, iv seen how to build one for a field containing a string but im unsure of how to build on the xy coordinates.
the schema for sightings(sighting_id(pk), spotter_id, bird_id, latitude, longitude, date, description)
WITH params as (SELECT -28 as lat, 151 as lon
SELECT sighting_id, sqrt(power(lat - latitude, 2) + power(lon - longitude, 2))
AS distance FROM sightings CROSS JOIN params
WHERE sqrt(power(lat - latitude, 2) + power(lon - longitude, 2)) =
(SELECT MAX(sqrt(power(lat - latitude, 2) + power(lon - longitude, 2)))
FROM sightings CROSS JOIN params);
Generally conventional indexing is very poor for comparing co-ordinate data because you can be near (or far) in two or more directions. It gets even more complicated if your distances cover a significant portion of the Earth and you have to accommodate a curved surface rather than treating space as flat.
If you have a dataset of any significant size to query on a regular basis, go and read the Oracle Spatial guide for your version of Oracle, paying attention to Appendix B (which covers what you are licensed to use without paying for extra options) and indexing.