--- In firebird-support@yahoogroups.com, Lester Caine <lester@...> wrote:

>
> 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
>

Hi

i see two possible optimzations
1. first calculate rectangle - you try to find distanse then calculate first distanse in degrees(radians) along latitude and lognitude - when you got this rectangle you can get index use in query
and next you can filter by power function

2. or store in table distanse e.g. to 0 lat and 0 lognitude and ask distanse between distanse a and b
and next you can filter by power function

Karol Bieniaszewski