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?


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.

  TableBusiness AS TB
  TableAuxiliary as TA on TA.BusinessID=TB.ID
  //bla bla bla do some spatial thingy here
  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.

  • Data rarely change. I guess I add an "attached" myIsam table for all my need – user4951 Jun 16 '12 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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