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 command.
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)
- I do not want to compute distance for any region which are still inefficient
- 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.
My table structure is like this:
BusinessID varchar(250) utf8_unicode_ci No None Browse distinct values Change Drop Primary Unique Index Fulltext Prominent double No None Browse distinct values Change Drop Primary Unique Index Fulltext LatLong point No None Browse distinct values Change Drop Primary Unique Index Fulltext FullTextSearch varchar(600) utf8_bin No NoneIndexes: Documentation Action Keyname Type Unique Packed Field Cardinality Collation Null Comment Edit Drop PRIMARY BTREE Yes No BusinessID 1611454 A
Edit Drop Prominent BTREE No No Prominent 0 A
Edit Drop LatLong BTREE No No LatLong (25) 0 A
Edit Drop sx_mytable_coords SPATIAL No No LatLong (32) 0 A
Edit Drop FullTextSearch FULLTEXT No No FullTextSearch 0
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 comman I need to cast?