zip_code.sql

CREATE FUNCTION `earth_distance_miles`(p1 point, p2 point) RETURNS int(11)
RETURN
((ACOS(SIN(x(p1) * PI() / 180) * SIN(x(p2) * PI() / 180) + COS(x(p1) *
PI() / 180) * COS(x(p2) * PI() / 180) * COS((y(p1) - y(p2)) * PI() /
180)) * 180 / PI()) * 60 * 1.1515)

SELECT earth_distance_miles(
(
SELECT location
FROM zip_code
WHERE zip = '68015'
), (
SELECT location
FROM zip_code
WHERE zip = '68025'
)
);

get all zips in a range
CREATE PROCEDURE `GetZipCodesInRadius`(IN `zipCode` INT(10), IN `distanceInMiles` INT(10))
BEGIN
SELECT DISTINCT dest.zip, earth_distance_miles(orig.location, dest.location) as sdistance
FROM
zip_code orig,
zip_code dest
WHERE
orig.zip = zipCode
having sdistance < distanceInMiles
ORDER BY
sdistance;
END

 

then call it with….

 

Call GetZipCodesInRadius(“68046″,”11″)