I have a MySQL table, that stores routes. The table stores the NorthEast and SouthWest coordinates of the route bounding box as POINT type. Also it stores 10 key coordinates along the route as a Google Polyline (adjusted polyline algorithm that stores 6 digit lat/lon precision, default google polyline is 5 digit).
I'd like to query that table to return all routes that intersect with a given bounding box / square map area. So, even if the route only partially (at least one of the key coordinates) is in that bounding box / map area, it should be returned as query result
To do this as efficient and fast as possible, I figured I first get all routes for which the bounding box intersects with the given bounding box like so:
select * from myTable where MBR_Intersects(g1, g2) = 1;
side question... how would I create g1 and g2? g1 would be bounding box from coordinates in text format received through the server script post data, g2 would be bounding box from the SE and SW coordinate in the table)
...and then just loop through the results to see if at least one of the key points is actually in the given bounding box. (Because even though the bounding boxes intersect does not necessarily mean the route itself passes through the given map area). I would do that loop in server (php) code.
However I'm not sure that is the fastest way. Probably there is a faster / more efficient way, but not really familiar with MySQL Spacial functions.