Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  1. I am using mysql myisam spatial index. I surf google back and fro and found nothing about my exact need.
  2. I do not want to compute distance for the whole table (that's too inefficient)
  3. I do not want to compute distance for any region which are still inefficient
  4. 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.
  5. 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  None        
    

    Indexes: 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?

share|improve this question
If someone would answer this saying no there is no such thing I will mark that as answer too :) Bad myisam. Bad myisam. It doesn't have to be very efficient. Trying small boxes first would suffice. – Jim Thio Jun 27 '12 at 6:48

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.