CODEWITHSUNDEEP
phpmysqlstorepostal-code
Rob
Rob

Reputation: 1

PHP MySQL nearest to postcode

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

Upvotes: 0

Views: 1661

Answers (2)

Phil Kingston
Phil Kingston

Reputation: 131

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.

Upvotes: 2

BugFinder
BugFinder

Reputation: 17858

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.

Upvotes: 0

Related Questions