RE: FN-FORUM: Postcode distance matching

date posted 1st February 2005 15:24

Hi Dan,
On the 01 February 2005 10:02, you wrote:

> What's the most common way? Anyone got any ideas? I'm thinking we'll
> have a table of stores, with addresses including postcodes, and some
> sort of crazy matching thing that sorts them by distance using a
> lookup table of some kind... any pointers much appreciated.

I spent months researching this but didn't find much helpful information
at the time as everything was based around US postcode lists.

In the end I found a CSV of postcodes (well, the first part of them, =
i.e.
SY1 from SY1 1LZ) and lat/longs. Then I had to find a formula to return =
all
postcodes within a certain radius.=20

This is the query that I eventually wound up with:

SELECT *,3957 * 2 * =
atan2(sqrt(pow((sin(0.0174*(`latitude`-'".$user_lat."')/2)),2) + =
cos(0.0174*'".$user_lat."') * cos(0.0174*`latitude`) * =
pow((sin(0.0174*(`longitude`-'".$user_long."')/2)),2)), =
sqrt(1-(pow((sin(0.0174*(`latitude`-'".$user_lat."')/2)),2) + =
cos(0.0174*'".$user_lat."') * cos(0.0174*`latitude`) * =
pow((sin(0.0174*(`longitude`-'".$user_long."')/2)),2)))) AS `distance` =
FROM `postcodes` GROUP BY `postcode` HAVING `distance`