Does anyone know of a way to fetch all polygons in a MySQL db within a given distance from a point? The actual distance is not that important since it's calculated for each found polygon later, but it would be a huge optimization to just do that calculation for the polygons that are "close".
I've looked at the MBR and contains functions but the problem is that some of the polygons are not contained within a bounding box drawn around the point since they are very big, but some of their vertices are still close.
Any suggestions?
解决方案
A slow version (without spatial indexes):
SELECT *
FROM mytable
WHERE MBRIntersects(mypolygon, LineString(Point(@X - @distance, @Y - @distance), Point(@X + @distance, @Y + @distance))
To make use of the spatial indexes, you need to denormalize your table so that each polygon vertex is stored in its own record.
Then create the SPATIAL INDEX on the field which contains the coordinates of the vertices and just issue this query:
SELECT DISTINCT polygon_id
FROM vertices
WHERE MBRContains(vertex, LineString(Point(@X - @distance, @Y - @distance), Point(@X + @distance, @Y + @distance))
The things will be much more easy if you store UTM coordinates in your database rather than latitude and longitude.