Frank Ingermann wrote:

> Hi Lester,
>
> <snip>
>> I am trying to work out a fast way of identifying points of interest
>> within a
>> radius of the selected home location ... and potentially there could
>> be 100+
>> entries per postcode eventually although probably not 200,000,000
>> records to scan.
>
> I did something similar a while ago with my Firebird FreeDB server
> (remember that session way back in Fulda? ;-) ).

Life was a lot easier then ... But at least now we have CTE's

> What did the trick speed-wise was to use a square instead of a circle:
>
> - filter out those coords where lng BETWEEN (dest_lng-searchradius) and
> (dest_lng+searchradius)
> plus same construction for the lat. in the upper CTE. Narrows down the
> possible matches to
> just a handful, and is pretty fast when you have indexes on the coord
> fields.
>
> - with those results, do the exact circle matching with those expensive
> math functions as you did in the lower CTE.

I think that is were I was heading, but I have the possible advantage that I can
use the coarse postcode areas as a pre-filter. There are only 3000 of those and
the processing is in the milliseconds.

The question I am really asking is just how much processing IS done in the upper
CTE select. I'm presuming it does have to scan the whole table, so I need to
limit range starting at that level? I'm probably better off looking up the data
for the base location in a separate query, and then pass the 'home' data to the
list function CTE. I'm probably starting by combining too much already.

--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php