GIS query mysql

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″)

1 comment on this post.
  1. coldfusionPaul:

    that’s a point to centroid query which in some instances (depending on zipcode topology) won’t give you a “correct” answer.

    mySQL’s GIS functionality isn’t very sophisticated or even that good. if “accuracy” is important, postGIS or sql server is a better bet. if not, you can get more “accurate” results by constructing bounding boxes (min/max XY pairs) for each zipcode polygon & calculating the distances to those.

Leave a comment