[SOLVED] K-Nearest Neighbors and MySql Geographical Index

Issue

I have a set of geo-tagged pictures in mySql database. You can consider my Pictures table to be:

create table `Pictures` (

location Point NOT NULL, 
timeCreated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

SPATIAL INDEX(location)

)ENGINE= MyISAM DEFAULT CHARSET=utf8;

I intend to perform a K-Nearest Neighbor algorithm based on the location of the pictures. Say, if K=10, select the 10 pictures which are geographically closest to the user location.

I appreciate your suggestions.

Solution

Have a look to the link below. I believe it addresses the same problem that you have by solely using MySQL queries (if your problem is to find the 10 nearest pictures to users location).

MySQL Great Circle Distance (Haversine formula)

I hope that this can help you solve your problem.

Answered By – Aristotelis Kostopoulos

Answer Checked By – Cary Denson (BugsFixing Admin)