2

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.

olix20
  • 794
  • 1
  • 11
  • 23
  • Any constraints there? Like is it a fixed set of pictures or are pictures added and removed? – Tim Mar 02 '12 at 09:57
  • @Tim pictures are continuously added by mobile users. – olix20 Mar 02 '12 at 10:06
  • You've asked specifically about KNN. The chosen answer -- a hversine formula has nothing to do with it. If you want the closest point, you'll have to refine your question. Shy of that, I'm unsure of what you're asking and think this is likely to confuse people who are looking for KNN. – Evan Carroll Aug 08 '18 at 05:28

3 Answers3

2

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.

Community
  • 1
  • 1
Aristotelis Kostopoulos
  • 835
  • 4
  • 15
  • 1
    Use haversine formula means you have to compute distance for all points. Even though you need only 10 nearest points. – user4951 Jun 20 '12 at 21:29
  • 1
    @ Jim Thio: I agree that you need to calculate the distance of all points taking into consideration the link that I posted. Could you please provide a better working solution for this problem? – Aristotelis Kostopoulos Jun 25 '12 at 08:26
  • There seems to be none for my sql. I am moving to mongodb. You can emulate it by searching in small rectangle first. If mysql has a command to do so it'll be good enough for me. – user4951 Jun 25 '12 at 08:28
1

No support for KNN.

A more proper and accurate response is simply that, as of now, neither MySQL nor MariaDB support KNN. Though PostGIS the free spatial extension to PostgreSQL does.

Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
0

Currently there is no support for K-Nearest Neighbors as stated elsewhere.

However, I submitted a feature request for it in 11/2020, https://bugs.mysql.com/bug.php?id=101649

Maybe if enough people express the need, they will add it sooner rather than later. You can 'vote' for it by using "Impact on me: Affects Me".

Tag: a calculated value (distance between two lat/long points) is slow to limit/filter, a spatial index cannot be used on calculated columns.

BrentH
  • 27
  • 4