My current strategy is to create another table in myisam that contains those spacial information. Then if I want to find 20 closest business, I'll just use join.
Is this a good idea?
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 only takes a minute to sign up.Sign up to join this community
The way I did it is I add another table that is in myisam containing latitutude and longitude. Then set the ID of the original table as the ID of the auxiliary table. When I look up I do join.
SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude FROM TableBusiness AS TB TableAuxiliary as TA on TA.BusinessID=TB.ID WHERE //bla bla bla do some spatial thingy here LIMIT 0, 20
However, myisam spatial index is very dissapointing.
I can't even do nearest neighbor search. At least there is no way to do so with just one mysql command. I got to try a small rectangle and then make the rectangle bigger and bigger.
If there is, please tell me what it is.
Using MyISAM is probably your best and only bet to be able to use a spatial index. You might consider storing the data in an InnoDB table and then export data continuously or by batch, according to your needs, to a myisam table. Then use the myisam table for your queries.
I wouldn't trust myisam as storage engine if data is of importance.
Seems like you figured out the solution yourself.