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.

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?

share|improve this question
add comment

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Data rarely change. I guess I add an "attached" myIsam table for all my need –  Jim Thio Jun 16 '12 at 16:28
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.