Mysql – How to get spatial index feature if I mainly use innodb

innodbmyisamMySQLspatial

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?

Best Answer

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.

Related Question