mgroves

Suppose you were creating some sort of dating site or auction site, and you wanted a user to be able to see all his dating matches within a 50 miles from him, or all of a certain auction item within 10 miles. Perhaps the easiest way to do this is to use zipcodes.

The key to using zipcodes is to have a database listing all possible zipcodes and their corresponding latitudes and longitudes. Technically, the latitude and longitudes are probably of the center of the given zipcode, so the farther away someone is from the center of the zip code, the less accurate this method will be. But it will be close enough. I’ve attached a database of zip codes that I got from Zipdy.

So let’s say we have a database table containing every auction for our auction site. First, we find the zipcode of our user (assuming he has logged in and you are storing his zipcode somewhere), and it’s “43130″.

      SELECT lat, lon
      FROM zipcodes
      WHERE zip = '43130'
 

Looking at our table, we see that his lat/lon is 82.6092, 39.68965. These values should be stored in a string variable for use later (I’m assuming you are using PHP or ASP).

The next step is to write a SQL query to figure out how far each auction is from our user and select only the items within, say, 50 miles. Your SQL query may vary based on your table structure and what information you want:

      SELECT i.itemdesc, i.itemprice
      FROM items i, zipcodes z
      WHERE i.zip = z.zip
      AND 3963.0 * acos(
          sin([USERLAT]/57.2958) * sin(z.latitude/57.2958) +
          cos([USERLAT]/57.2958) * cos(z.latitude/57.2958) *
          cos(z.longitude/57.2958 - [USERLONG]/57.2958)
                       )
          <= 50
 

This query is adapted from a forum post from Eamon Daly.

I'm assuming that you would be building this query in ASP or PHP, so you should replace [USERLAT] and [USERLONG] with the user's lat/long that we already put in strings earlier. This query assumes that the earth is a perfect sphere (it's not, but close enough) and uses trig functions to figure out the distance between two points of latitude and longitude.

So now that query returns all items (description and price in this case) within 50 miles of our user. To change the distance, simply change the <= 50 at the end of the query.