PHP - Distance from post code

[Lovemonk]

As part of a development project I have to implement a search feature that will return the people 'within 10, 20, 50' miles sort of thing. I have managed to find an open source post code database (http://www.kubelabs.com/ukpostcodedata.php) that has latitiude and longitude details so should suffice. I know i need to use basic trigonometry to get the distance between points based on the latitudes and longitudes which is all good.

What i'm curious about is that it seems wasteful to me that if someone chooses for example, 'within 30 miles' and their location is Edinburgh, that the script would check every line of the database completely. Is this the only way to do it? Sorry if it is a stupid question but feel free to enlighten me anyways.

 

[Inquisitor]

Well, working out the distance to a given point wouldn't be too difficult:

// Find difference in longitude and latitude.
$dLongitude = $startLongitude - $endLongitude;
$dLatitude = $startLatitude - $endLatitude;

// Convert to miles (different values are used because the Earth isn't a perfect sphere).
$dx = 24901.55 * $dLongitude / 360;
$dy = 24859.82 * $dLatitude / 360;

// Use Pythagoras' theorem to find the distance between the points.
$c = sqrt(pow($dx, 2) + pow($dy, 2));

As for querying the database, you'd want to put the checking code in the selection query, something like this:

SELECT * FROM `table`
WHERE SQRT(POW((`longitude` - [B]long[/B]) / 360 * 24901.55, 2) + POW((`latitude` - [B]lat[/B]) / 360 * 24859.82, 2)) <= [B]distance[/B]

Where long, lat, and distance are the parameters you pass into the query.

It's not a very nice query, but I can't see any other way of doing it.