0

Hello I found this article which is helpful but unfortunately I cannot get it to work.

http://davidsimpson.me/2008/09/28/find-nearest-store-page-using-php-mysql-and-google-maps/

I have a list of stores in a MySQL table with postcode,longitude,latitude and various other store specific information.

I'd like the user to be able to enter their postcode on a form, specify a maximum distance in miles (ie a drop down menu, within 10 miles, within 20 miles etc), then for the results to be pulled in order from MySQL database.

Can anybody shed some light on this.

Regards

Rob
  • 1
  • 1

2 Answers2

2

While the approach offered in that example will function, it has to calculate the distance for every row in the table. This is simply too inefficient when working at scale. Have a look at this tutorial. It uses special data types in MySQL for the coordinates and can search very quickly. It should hopefully give you a grounding on where to start.

Your solution may be even more straightforward. It is going to require a list of all the postcodes and also a list of all the stores. As all your inputs and outputs are known factors, you can pre calculate this into "cache" tables which would give you very fast results. Create a table called StoreDistances10Miles (guessing on data types and naming conventions):

postcode CHAR(8) INDEXED, store_id INT(8), metresAway INT(8)

Iterate over each postcode, sort the stores by distance and save the ones within 10 miles into the table. Now, when you want to search by postcode, just search StoreDistances10Miles by postcode and you'll have your list. Make sure you index postcode.

You'll need to reproduce a table for each distance you want to offer on your site.

Phil Kingston
  • 131
  • 1
  • 4
0

Well he clearly gives how to get the miles, so all you need to have using your drop down is "Having miles < {dropdownvalue}" added to your sql statement. so if you have 10 miles, its having miles < 10 etc.

BugFinder
  • 17,474
  • 4
  • 36
  • 51
  • If you look at the initial SQL statement it containts $longitude and $latitude but these PHP variables aren't given any values beforehand. Am I missing something or is the sample PHP code incomplete...? – Rob Jun 08 '11 at 22:02
  • I got the impression that was the longitude, and latitude of the users location, from his explained formula at the top. – BugFinder Jun 08 '11 at 22:15