I am putting together a website that needs to be able to show the nearest X locations to a given postcode.
In order to do so, I have imported a very large table of 1.7 million records, which is a complete list of all postcodes in the UK. My intention was to use MySQL to calculate the nearest locations, through using the Haversine formula in conjunction with the latitude and longitude, taken from the postcode table.
However, I have only just started test this and have come to realise the massive resource implication, given that in order to find the 20 nearest, I would need to individually calculate the distance from X of all 1.7 million records, then sort by distance...
Here is the MySQL that i was originally playing around with:
SELECT type_place.postcode, type_place.place_title, postcodelatlng.latitude, postcodelatlng.longitude,
111.045*haversine(latitude,longitude,latpoint, longpoint) AS distance_in_km
INNER JOIN type_place
INNER JOIN (
SELECT 54.97 AS latpoint, -1.607 AS longpoint
) AS p
WHERE postcodelatlng.latitude < 56 AND postcodelatlng.latitude > 54 AND postcodelatlng.longitude<-1.5 AND postcodelatlng.longitude>-1.7
ORDER BY distance_in_km
As youi can see, I added a WHERE clause to limit the search to +/- 1 point on lat/long. However, when i ran the test it took 30 secs to complete..
Could someone suggest a better approach to the problem?