Improving performance of geographic queries

We have an Oracle 8.04 database. A table in the DB contains radios. Each radio has a geographic location (latitude and longitude) and a radius of mobility in kilometers. Users perform radius selects against this table, i.e., they specify a center point and a radius. We have a DISTANCE stored function that computes the distance between two geographic locations. Suppose user wants to select radios within 200KM of 30N,45E. The query looks something like this

where DISTANCE(lat, lon, 30.0, 45.0) <= radius_of_mobility + 200

This query is too slow. Obviously, since it involves a function, Oracle cannot optimize it and must perform a full table scan.

My first try at improving performance was to create a trigger on the radios table to calculate a "bounding georectangle" around the radio's circle of mobility. In other words, the trigger computes a southwest and northeast corner latitude and longitude for each record. The resulting fields were each indexed and statistics were run. A bounding georectangle was also computed for the user's query. The idea was to look for any overlap between the user's georectangle and the record's georectangle. By indexing the fields of the georectangle, Oracle would be able to optimize the query. The resulting query looked something like this (for simplicity, I've rounded the numbers)

where SW_Lat <= 35.0 and NE_Lat >= 25.0 and SW_Lon <= 50.0 and NE_Lon
>= 40.0 and( DISTANCE(lat, lon, 3.00, 45.0) <= radius_of_mobility +
200)

Surprisingly, this query was 2 times slower than the original! Looking at the execution plan, Oracle was performing 4 index range scans and merging them. This was slower than doing a full table scan.

In my latest attempt, I dropped the individual indexes on the georectangle coordinates and created a single index as follows

create index radius_select on radios(sw_lat, ne_lat, sw_lon, ne_lon, lat, lon, radius_of_mobility)

I also reran statistics. This halved the time versus the orginal query. Oracle performed a single index range scan. Since the index contains all the fields Oracle needs to satisfy the query, it does not need to go to the base table. However, the query performance is still not satisfactory. Oracle is able to quickly go to the first record meeting the latitude constraint, but it must still scan the rest of the entire index to check the northeast latitude and the longitudes. I know this because removing the longitude constraints from the query make no difference, i.e.,

where SW_Lat <= 35.0 and NE_Lat >= 25.0 and( DISTANCE(lat, lon, 3.00, 45.0) <= radius_of_mobility + 200)

Can anyone offer a better solution for performing geographic queries like this? Thanks. Received on Sat Jul 10 1999 - 14:27:43 CDT