We help IT Professionals succeed at work.
Get Started

Calculating 'nearest' 10 locations to a postcode

173 Views
1 Endorsement
Last Modified: 2015-11-01
Experts,

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
 FROM postcodelatlng
 INNER JOIN type_place
 ON type_place.postcode=postcodelatlng.postcode
 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
 LIMIT 15

Open in new window


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?

Regards
Dean
Comment
Watch Question
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 4 Answers and 10 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE