I have a table of zip codes which includes the center lat, lng for each zip code. I use it to get a list of zip codes within a given mile radius from any arbitrary point.
It just occurred to me that, just because a zip's center point is not within a given radius, does not mean that the zip itself is not within the radius.
I used my super advanced art skills to illustrate the point here:
The green stripy blobs represent zip codes A, B and C.
The red smudges are the geographic centers for each zip code
The fuchsia dot is the target location, and..
The lumpy blue circle is a 1 mile radius from the target location
If I run a query for all the zip codes within a 1 mile radius from the pink smudge, only zip codes B and C will be returned as the center point for zip A is not within the one mile radius, even though the pink smudge itself is clearly in zip code A.
SELECT *, p.distance_unit * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.y)) * COS(RADIANS(p.longpoint) - RADIANS(z.x)) + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.y)))) AS dist FROM standard_zip AS z JOIN ( /* these are the query parameters */ SELECT $lat AS latpoint, $lng AS longpoint, $miles AS radius, 69 AS distance_unit ) AS p ON 1=1 WHERE z.y BETWEEN p.latpoint - (p.radius / p.distance_unit) AND p.latpoint + (p.radius / p.distance_unit) AND z.x BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint)))) AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint)))) ORDER BY dist
How the heck do I write a query that will include zip A in the results?
I have access to spatial/geometry for each zip code that I can add to the table if needed, but I have no idea how I would use it for this purpose in MySQL.
Edit: I spent a day reading the Oracle and MySQL docs for spatial data and managed to successfully convert my spatial data to MySQL. How do I go about writing a similar query that uses the geometry column instead of the lat and long? I am using 2D data.. the geometry are polygons and multipolygons only..
I think I sort of figured it out..
select * from ( select MIN(st_distance(geom, POINT(-82.765136, 28.0914015))) * 69 as miles, zip from zip_spatial group by zip order by miles asc ) d where d.miles < 5
I'll leave the bounty open for now in case someone has a better, more efficient solution.