Re: Improving performance of geographic queries

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 10 Jul 1999 21:15:17 +0100
Message-ID: <931637977.13665.0.nnrp-04.9e984b29@news.demon.co.uk>

You could either buy in Oracle's Spatial Data Cartridge which exists to cope with exactly this type of query, or you could simply clone the technology.

Your bounding geo-rectangle is a good starting point, but the main trick is to cover the entire space with uniform rectangles, then create an 'inverse index' table which associates covering rectangles with your geo-rectangles (if you want to be really clever you could associate the covering rectangles with the actual circle for the radio).

You get a two column table:

    radio-id, covering rectanlge_id

        where each radio-id may need a number of covering rectangles.

Index this on covering rectangle_id
Write a trigger on insert into the radio table to generate rows for this table.

Your query is then:

    select

        radio detail
    from radio table
    where radio_id in
    (

        select
            distinct radio_id
        from
            two_column_table
        where
            covering_rectangle_id in
                   (select (generate(rectangle_ids that cover the radius and

center of interest))

    )
    and (something to check distances for radios)

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Gary Cramblitt wrote in message <3787965b.69441393_at_news.clark.net>...
>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.
Received on Sat Jul 10 1999 - 15:15:17 CDT