by Fiaz Khan (Technical Director)

A frequently requested feature is to allow users to search for the nearest place or store based on a postcode. To achieve this you need to carry out two steps:

• Determine the longitude and latitude of the users postcode
• Search through the database and return results ordered by the distance starting with the nearest first

Fetching users longitude and latitude from their postcode

This requires the use of a third party service. (of which there are several). The one I chose to use is Yahoo Maps Geocoding Service. For this you will need a Yahoo App ID.

Once you have and App ID, you can start making requests to the service. For the sake of this tutorial we are going to use PHP, but the server side language is irrelevant.

$request =  'http://local.yahooapis.com/MapsService/V1/geocode?appid=YOUR_APP_ID&output=php&state=uk&street=YOUR POSTCODE';
$response = file_get_contents($request);
$responseData = unserialize($response);
foreach ($responseData as $item) {
    $longitude = $item['Result']['Longitude'];
    $latitude = $item['Result']['Latitude'];
}
?>

The important bits are the arguments passed to the api, in this case we have asked for the returned data to arrive as serialized PHP (output=php), alternative format is XML. For state we forced the search in UK only and the street, even though this says street you can in fact pass in any “address” element, such as city, postcode etc.

Now that we have the users longitude and latitude, we need to search the database and return stores ordered by distance. To do this, all stores in the database must also have longitude and latitude values. If yours does not, you can batch query your stores against several services. e.g. www.batchgeocode.com.

Once you have a database of stores each with valid longitude and latitude values, all that is left is to query the database using the following SQL query.

SELECT id, ( 3959 * acos( cos( radians(YOUR_LATITUDE) ) * cos( radians( YOUR_DB_LAT_FIELD ) ) * cos( radians( YOUR_DB_LNG_FIELD )
- radians(YOUR_LONGITUDE) ) + sin( radians(YOUR_LATITUDE) ) * sin( radians( YOUR_DB_LAT_FIELD ) ) ) ) AS distance 
FROM YOUR_DB_TABLE HAVING distance < 25 ORDER BY distance ASC;

YOUR_LATITUDE and YOUR_LONGITUDE are where you enter the values returned by Yahoo, YOUR_DB_LAT_FIELD and YOUR_DB_LNG_FIELD are the fields containing the longitude and latitude values for each store and finally YOUR_DB_TABLE is the database table you are querying. “distance < 25″ tells the database to only return values that are less than 25 miles away. You can modify or omit this to return all.

That’s all there is to it, note that I have not taken care of any error catching or legal issues with storing longitude and latitude values in the database, this is something you will need to take care of. Any comments please post below.