Hi,
I need to implement a search on local businesses that returns the N closet businesses from a specified location. Example: Find gas stations in 10 miles radius from a location, sort by proximity.
We have longitude and latitude as well as zip code for every business.
I did some reading on this forum and others, and now I consider a few options. I am concerned that all the simple alternatives (mostly use distance functions) seem to require an evaluation of the entire table, which will not scale well.
Ideally, the solution will not require a complete table scan.
I can use many approximations. Since we want to sort the businesses by proximity, there is no harm if the results have more records that are beyond the search distance.
Option 1:
Store longitude and latitude data and search on a rectangle.
+ very simple
- Requires complete table scan.
Option 2:
Store longitude and latitude data and search on a distance function (many implementations on the web and in this forum).
+ very simple
- Requires complete table scan
- Slow math, even after some optimizations.
Option 3:
Same as option 1, but store the coordinates in a Point geometry object and define a spatial index on that column.
+ very simple
- I believe this also requires complete table scan, I did not find documentation that indicates otherwise.
Option 4:
Do it by zip code. Create a table that stores the distances between zip codes that are 100 miles or less from each other.
The query first finds the zips in the search radius.
Then return all the businesses in those zips, ordered by distance.
See:
http://forums.mysql.com/read.php?23,3868,4236
+ Does not require complete table scan
- Complex implementation, requires additional tables that need to be calculated form zip code data.
I am aware that the spatial functionality is planar. This can be easily corrected by a simple calculation that will slightly increase the search area. A few more records in the results are not a problem as long as the search does not require a complete table scan.
It seems that option 4 is the best because it is the only way to avoid a complete table scan.
So the question is, if I define a geometry column and a spatial index on it:
CREATE TABLE geom (locationPoint GEOMETRY NOT NULL, SPATIAL INDEX(locationPoint));
will a query that uses Distance(locationPoint, myLocation) avoid a complete table scan?
Any help appreciated!