So, I'm trying to get a zip code proximity query that will be the most efficient way to select a number of users within a certain zip code (w/ PHP/MySQL). Here's my setup:
- Each user in the users table has a zipcode assigned to him/her.
- I have a zipcodes table which contains columns zip code, latitude and longitude
- The search/browse lists users that fit a certain criteria.
- The form used passes variables like whether the users listed should be male or female, age range, etc., and two variables pertaining to zip code location: miles from the zip code and the zip code itself.
- The function the form passes those values to returns a result set of users which fit the criteria.
So far, I start with this query:
SELECT user_id, fname FROM users, zipcodes WHERE
Then I add on whatever conditions the user chose on a case by case basis:
AND country_id = $cid
AND name LIKE '%$name%'
Etc. Now, for the zip code, here's the pseudo-query I constructed:
(sin( zipcodes.latitude(user's zip) / 57.29577951)
* sin( zipcodes.latitude($requestedzip) / 57.29577951)
+ cos( zipcodes.latitude(user's zip) / 57.29577951)
* cos( zipcodes.latitude($requestedzip) / 57.29577951)
* cos( (zipcodes.longitude(userszip) - zipcodes.longitude($requestedzip) ) / 57.29577951))
))) < $milesfromzip
As you can see, I want to only select users whose zipcode lat and long are within $milesfromzip distance of the $requestedzip. My question is, how do I resolve the zip codes? I would have to access the zip code database basically for each user (ideally, having resolved the $requestedzip's lat and long). Is this the most efficient way to do this? Any help or suggestions is strongly appreciated. Thanks very much!