MySQL Forums
Forum List  »  GIS

Re: Zip Code Proximity search - Examples II
Posted by: Austin Hastings
Date: March 02, 2006 05:23PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
28366
October 19, 2004 06:04AM
10908
October 19, 2004 07:55AM
9311
October 19, 2004 11:14AM
10345
October 21, 2004 09:28AM
9231
October 19, 2004 06:59PM
7688
October 20, 2004 07:39AM
7694
October 21, 2004 09:10AM
8941
October 22, 2004 07:17AM
6876
October 23, 2004 02:48AM
6296
October 23, 2004 03:09AM
6408
October 23, 2004 03:12AM
6294
October 23, 2004 02:59PM
8064
October 24, 2004 12:34PM
5584
October 24, 2004 01:31PM
13215
October 21, 2005 10:21AM
5642
D C
January 28, 2006 05:24AM
5190
March 02, 2006 04:24PM
7285
October 09, 2007 09:28AM
5799
December 06, 2005 05:34AM
7090
December 06, 2005 06:36AM
5377
December 24, 2005 01:10PM
8934
December 26, 2005 03:49PM
5509
October 09, 2007 09:36AM
Re: Zip Code Proximity search - Examples II
15893
March 02, 2006 05:23PM
29496
December 13, 2007 04:10PM
6243
April 05, 2006 02:59PM
4780
May 02, 2006 03:22PM
5747
May 05, 2006 09:44AM
14681
June 25, 2006 09:32PM
5570
August 30, 2006 12:54PM
6093
July 14, 2007 01:09AM
7700
November 03, 2006 10:25AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.