I'd recommend you do the following:
1. Figure out the starting lat/long coordinates. See Rasmus Lerdorf's PHP-based example at
http://toys.lerdorf.com/archives/35-GeoCool!.html.
The upshot is that you can feed a request to Yahoo's API and get a very precise result back.
You want to cache this information! Don't piss off Yahoo, or they might throttle your requests. Worse yet, they might throttle mine. Cache!
2. Compute a bounding rectangle based on the search area requested by the user. Note that the bounding rectangle should allow for a fair amount of slop, especially if the search area is small, since the "center" of a zip code might be inconveniently far away.
Consider for example that I live in New Jersey. The "center" of Pennsylvania, the next state west, is hundreds of miles away. But Philadelphia, PA, is a 10 minute drive. If you were doing a state-based, instead of zip-code based, search, you'd need that much "slop" to make sure that Pennsylvania was included in the "surrounding states" box. It's the same for zip codes, except they're smaller.
Assuming that you're search distances (call the search radius "R") are 5km, 10km, 20km, you might make your "slop factor" be 8km. Then you need to compute the latitude and longitude for X + (8 + R), X - (8 + R), Y + (8 + R), Y - (8 + R) km. In this case, assume the earth is flat and compute the lat/long offset you're going to need once (make them constants).
This gives us a rectangle that we can use for a simple select statement. Some things in the corners of the rectangle will be too far away, but everything that should be in the circle definitely will be within the rectangle.
3. Query your database for zip codes:
SELECT * FROM zip_table WHERE lat >= ... AND lat <= ... AND long >= ... AND long <= ...
You'll want to modify this query to filter on whatever other attributes you have. Probably you'll wind up with something like this:
SELECT *
FROM addresses addr
JOIN zip_table zip ON addr.zip_code = zip.zip_code
WHERE addr.business_type = 'drug_store' -- or whatever
AND addr.expensive = 'F' -- or whatever
AND addr.sells_viagra = 'T' -- or whatever
AND zip.lat >= ... AND zip.lat <= ...
AND zip.long >= ... AND zip.long <= ...
4. Having done that, you'll want to order them by distance. Peter Brawley's excellent link above (http://www.artfulsoftware.com/queries.php#16) leads to this function for computing distance:
SET GLOBAL log_bin_trust_routine_creators=TRUE;
DROP FUNCTION IF EXISTS GeoDistKM;
DELIMITER |
CREATE FUNCTION GeoDistKM( lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT ) RETURNS float
BEGIN
DECLARE pi, q1, q2, q3 FLOAT;
DECLARE rads FLOAT DEFAULT 0;
SET pi = PI();
SET lat1 = lat1 * pi / 180;
SET lon1 = lon1 * pi / 180;
SET lat2 = lat2 * pi / 180;
SET lon2 = lon2 * pi / 180;
SET q1 = COS(lon1-lon2);
SET q2 = COS(lat1-lat2);
SET q3 = COS(lat1 lat2);
SET rads = ACOS( 0.5*((1.0 q1)*q2 - (1.0-q1)*q3) );
RETURN 6378.388 * rads;
END;
|
DELIMITER ;
There are two possibilities:
4a. If you don't know the precise lat/long of the address you've come back with, you can use the zip-code coordinates. This has the advantage of letting you add the computation to the select statement, but the disadvantage of putting Philadelphia right next to Harrisburg (see state comments, above).
In this scenario, you could simply add a column "distance" defined as the GeoDistKM() result of the user and target-address locations.
4b. You could compute the precise location of the target-address using the Yahoo (or some other) API as referenced above. Once you've done this computation, of course, you will CACHE IT! in your address database.
Doing the web-service lookup requires that you pull the results out of the database and compute the location in your application. Then you can sort on computed distance and proceed.
I'd recommend that you do both: cache the exact location in the address table, then if those fields are NULL you can set the distance -1 or some such. Sorting on this will put "I need to be looked up" targets at the front, where you can recognize them and resort the list:
... ORDER BY IF (addr.lat IS NULL, -1, GeoDistKM(...))
Hope this helps,
=Austin