GIS query mysql

October 30th, 2012 by admin

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

Posted in Flex | 1 comment

Previous post:
Next post:

1 comment on this post.

  1. 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.

Leave a comment