Note: In this question I want actual mysql command that I should send. Not concept, and stuff.
This question is similar with how to find 20 closest points efficiently
However in that question I forget to mention that I am using mysql myisam. Rather than modifying the question to include that and to show respect for those who have spend the time answering, I gave them all +1 and select some answer anyway. So I made another quetion.
I want answers like what I see how to find 20 closest points efficiently but for mysql myisam.
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:
- 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.
- I do want to compute 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?
I starting to doubt that mysql myisam support nearest neighbor search at all. If they don't, an answer saying and confirming that they don't would suffice.