smftre — 2012-03-06T09:06:50-05:00 — #1
Hello guys and gals!
I am almost complete with another site but have a feature left that I still need to do, basically it entails the following:
- There are entries stored in a MySQL database, and each entry has a postcode.
- The user uses a "search form" and enters their postcode and selects a distance from a dropdown, the values are "Any, 1mile, 5miles, 10miles, 100miles".
- The user hits the search button and a SQL query is executed and returns all entries in the database which have the above distance from the above postcode.
Anyone know how to achieve this?
I have Googled around and found a few solutions but they aren't exactly what I'm looking for, they all seem to be about determining the distance between 2 given postcodes.
My script is quite different as you can see..
Any thoughts, comments or "full on solutions" are very very welcome
cups — 2012-03-06T10:22:25-05:00 — #2
If you know my postcode, then you also know my postcode location (lat, lng).
If I now want to search for Garden Centres (or whatever) whose location (lat, lng) falls inside a circle then you are going to want an sql search like this one.
If this is just a bell or a whistle on the website you are developing, then you should be fine.
If it turns out that this is generating a lot of traffic (and server-side computations) then seriously consider using Mysql's native spatial abilities by not simply storing lat, lng pairs but POINT(x,y) as well. You should get selects a lot quicker using mysql spatial point -- but as I say, don't bother unless you see proof that you need to learn this -- unless it turns you on of course ...
smftre — 2012-03-06T10:57:00-05:00 — #3
@Cups, thanks for your comments, I am currently taking a look at the post you referred to!
If anyone else has anything to add, oh please do