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