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

>
> I need a bit of help understanding how the following query will get processed.
> I've tidy up the postcode table to include the latitude and longitude ready
> converted to radians, and the hard coded 'SA5' will be a parameter along with
> the number of records returned and the distance filter. Its working nicely with
> the short postcodes, but the question is how can I improve performance when
> 2000000 full postcodes are provided in the postcode table?
>
> I'm probably OK using the short postcodes to identify the areas of interest and
> then using the result of this to pre-filter the full postcode table?
>
> Another question is if POWER(x,2) is better replaced by simply duplicating the
> content. FlameRobin would not seem to be accurate enough to compare times.
>
> WITH
> Z AS
> ( SELECT r.ID, r.OUTCODE, r.LAT AS LAT_D, r.LNG AS LNG_D,
> ( SELECT LNG_R FROM OUTCODEPOSTCODES
> WHERE OUTCODE = 'SA5') AS LNG_H,
> ( SELECT LAT_R FROM OUTCODEPOSTCODES
> WHERE OUTCODE = 'SA5') AS LAT_H
> FROM OUTCODEPOSTCODES r
> WHERE OUTCODE <> 'SA5'
> ),
>
> Y AS
> ( SELECT OUTCODE,
> SQRT( POWER((LNG_D - LNG_H) * COS( (LAT_D + LAT_H) / 2.0000) ,2)
> + POWER((LAT_D - LAT_H) ,2) ) * 3958.50 AS DIST
> FROM Z
> ORDER BY DIST
> )
>
> SELECT FIRST 10 OUTCODE, DIST FROM Y
> WHERE DIST < 20
>
> First attempt at testing on the full postcode table took some time before the
> 8Gb of real memory and 8Gb of swap was exhausted - but I know that lookup was
> wrong just as I hit 'run' ...
>
> 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.
>
> --
> 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
>

(Assuming you are using UK postcodes) then perhaps another option is to use the national grid northing/easting values and then apply pythag, maybe simpler and quicker?

Also, do you really need the level of accuracy to individual postcode level? Isn't searching against the first part of the postcode (outward code) sufficient for most purposes?
Tom

Tom