101ldaniels 101ldaniels - 1 month ago 5
SQL Question

function based index XY coordinates

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
FROM dual)
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);

Answer

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.