google maps, geolocation, jquery, php: find zip codes within 30 miles

Question

I have a csv file of names, zip codes, and distances willing to travel. A web-user will input their zip-code and a mile radius of how far they are willing to search. And I'm looking for the names of people, within the csv file, that match that search. I'm not too familar with geolocation searching or free technologies that are currently available (such as google maps). The site is programmed in php and is using jquery. I do not want a map, just an array or tree of the names. Any suggestions on how to set this up?

CSV example

"1","John Smith","ZipCode-12345","TravelDistance-30"

Solution

1. Setup a cronjob to grab csv file and dump in mysql db table
2. Downloaded a db table of cities/states, lat/long, zipcodes
3. Used sql formula to calculate the distances and queries the matches

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) )

* cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

Answer 1

Basically, in a case such as this one, I would go with a database and two tables :

• One table for cities (or zipcodes -- not sure if those correspond to cities) ; in that table, you'd have :
  • id
  • name of the city
  • latitude
  • longitude
• One table for users
  • id
  • name of the user
  • zipcode_id : foreign key to the row of the other table -- the city in which the user lives

When trying to calculate which users are close to a given one, you'd actually calculate which cities are close to the one your user's in -- and, from that, find out the corresponding users.

To determine which cities are close to a given one, I suppose you can find some formula -- for example, see the following questions / answers :

• How do I calculate distance between two latitude longitude points?
• Distance between two coordinates, how can I simplify this and/or use a different technique?

Note that, when initializing your database of cities/zipcodes, you'll have to find out the latitude and longitude of each city -- I suppose you can find some database with coordinates of all cities somewhere on the Internet.

(Another solution would be to send out requests to Google maps' geocoding service -- but that's not allowed by their terms of service, if I remember correctly)