Help with optimizing the "Radius Search" stored procedure

[J D]

I have found the following code snippet which does a radial search given a zipcode and distance to search outward.

Is there a better optimized way of doing this? There are +70,000 rows in this table, and it appears that it is doing comparisions on every row, instead of just for the valid latitude and longitude ranges. In other words, if I put in the zipcode 90210 (California) and a radius of 5 miles, there should be no reason it would check rows in Michigan. I have read about doing an initial query where you get the latitude and longitude of the zipcode in question. Then you use those to filter out the unnecessary rows. I am not sure how to store the output from the query ( select latitude, longitude from zipcodes where zipcode=myZip) for later use in the stored procedure.

My table (zipcode) has the columns for the zipcode, latitude, longitude which are needed for these calculations.

Another point is that I heard this particular algorithm is good for long distances apart, but not for shrt distances. I would like to have it be more accurate for shorter distances. I found the following algorithm for that, but any time I replace the current algorithm with this one, I get null values in the distance field.

( 2*asin(sqrt((sin((z.latitude-o.latitude)/2))^2 +
cos(z.latitude)*cos(o.latitude)*(sin((z.longitude-o.longitude)/2))^2))
)


Any comments or insights are appreciated. I am a newbie for MySQL stored procedures and coding.

P.S. If anyone knows how to format this post so the code isn't all left-flushed, please let me know.

Thank you



DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_zipSearch`$$
CREATE PROCEDURE `sp_zipSearch`(
IN myZip varchar(5),
IN dist int
)
BEGIN

SELECT o.ZipCode,
(3956 * (2 * ASIN(SQRT(
POWER(SIN(((z.Latitude-o.Latitude)*0.017453293)/2),2) +
COS(z.Latitude*0.017453293) *
COS(o.Latitude*0.017453293) *
POWER(SIN(((z.Longitude-o.Longitude)*0.017453293)/2),2)
)))) AS distance
FROM zipcodes z,
zipcodes o,
zipcodes a
WHERE z.ZipCode = myZip
AND z.ZipCode = a.ZipCode
AND (3956 * (2 * ASIN(SQRT(
POWER(SIN(((z.Latitude-o.Latitude)*0.017453293)/2),2) +
COS(z.Latitude*0.017453293) *
COS(o.Latitude*0.017453293) *
POWER(SIN(((z.Longitude-o.Longitude)*0.017453293)/2),2)
)))) <= dist
ORDER BY distance;

END$$

DELIMITER ;



Edited 1 time(s). Last edit at 07/08/2005 08:43AM by J D.