I'm a little bit stuck.. I have a database with some information about some shops in the UK, I want to allow the user to sort the results by distance from their Postcode. I have a script that generates the distance from the postcode entered, and returns all of the relevant postcodes + distances. However, I have seen that people have used MYSQL's built in functions to minimise the code down, to one query rather than a lot functions, also I would like to combine this query with another that I have for example in psudocode Select * FROM `shops` WHERE `shops`.`postcode` IS WITHIN 50 MILES OF 'dd10' ORDER BY `distance` Something along the lines of 'http://davidsimpson.me/2008/09/28/find-nearest-store-page-using-php-mysql-and-google-maps/' The files on the first post of this thread are the ones that I am using http://forums.digitalpoint.com/showthread.php?t=1823109
Cracked it. SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance,`name` FROM markers HAVING distance < 28 ORDER BY distance LIMIT 0 , 20 that worked.
The original post gives the formula (and the SQL SELECT statement) as $sql = "SELECT ROUND(3983.0 * ACOS(SIN($latitude*PI()/180)*SIN(latitude*PI()/180) + COS($latitude*PI()/180) * COS(latitude*PI()/180) * COS((longitude*PI()/180) - ($longitude*PI()/180))), 1) AS distance, store_name, postcode, postal_address, latitude, longitude FROM stores ORDER BY distance ASC LIMIT 0, 10"; a little less than halfway down.
In-spite using different scripts, there is an online distance calculator using postcode, that allows you to calculate Distance between any two locations using the Postcode. It is working nicely for entire UK and is expanding in other European Region, you must check it out: postcode.org.uk