# GIS query mysql

`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”)

coldfusionPaul:

October 30th, 2012 at 3:19 am

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.