Take the 2-minute tour ×
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.

There are 1.6 million rows.

Must not compute distance for all rows.

Spatial index is available

This is the table structure. The name of the table is businessauxiliary. So yes I used MyIsam, added my spatial index. Then what? What's the SQL command?

If you can point me to a web it'll be great.

    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        Browse distinct values  Change  Drop    Primary     Unique  Index   Fulltext
With selected: Check All / Uncheck All With selected:
Print viewPrint view Propose table structurePropose table structureDocumentation
Add new fieldAdd field(s) At End of Table At Beginning of Table After
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           

Note:I am not dealing with lines or polygons, only points. I need more direct samples. Also I am not interested in points within a circle. I want 20 closest points. The fact that those points are within a circle or not is irrelevant. (Actually I will use filter, however, that's latter)

share|improve this question
add comment

1 Answer 1

If a SPATIAL INDEX does not work for you, see http://mysql.rjweb.org/doc.php/latlng It is a description of how to efficiently "find the nearest" on the globe.

share|improve this answer
    
I am not asking whether spatial index works for me or not. Obviously it works. I want to know how to use it. –  Jim Thio Jun 21 '12 at 2:47
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.