Say I want to find 20 closest business near me. Latter I want to see the next 20 closest, etc. Yes I have myisam. I have spatial indexes on the point. I am looking for actual SQL command I should send.
To make it clear:
Note:
- I am using mysql myisam spatial index. I surf google back and fro and found nothing about my exact need.
- I do not want to compute distance for the whole table (that's too inefficient). The whole point of spatial index is that at least you can get rid a lot of points efficiently. If some points to your left and up do not qualify, then all points to the left and up of that point wouldn't qualify either.
- I do not want to compute distance for any region which are still inefficient. My region of interest is 10km and there are 65k businesses there.
- I do not mind computing distance for reasonable number of points because I want to sort the points by distance and be able to display point 1-20, 21-40, 41-60, etc.
- I found a way to do so for postgis http://blog.opengeo.org/2011/09/28/indexed-nearest-neighbour-search-in-postgis/ I want something similar but for mysql myisam.
- I want a website with actual sql code as sample or actual sample sql command. So please don't just say, "use spatial." Duh. I know. I want the actual sql command.
- I do not want to do complicated loop of searching a small region first. There has to be an sql command that'll do this in one stroke efficiently done by mysql engine.
- Cartesian distance is fine. No need to know that we live in a globe unless you're in northern or southern pole, which is not where most of our customers are.
There are 1.6 million bizs. Of course it's stupid to compute distance for all of them and then sort it.
That's where geo spatial index kicks in right?
So what SQL command I need to send?
