Bug #101649 k-nn nearest neighbor, Order by, Calculated value, Spatial index

• Submitted: 17 Nov 2020 16:25
• Modified: 18 Nov 2020 14:13
• Reporter: Brent H
• Status: Verified
• Category: MySQL Server: GIS
• Severity: S4 (Feature request)
• Version: 8.0
• OS: Any
• CPU Architecture: Any
• Tags: knn, spatial index

[17 Nov 2020 16:25] Brent H

Description:
When I search for restaurants within bounds using POINT() and a SPATIAL index, query is very fast (8,000 rows, ~0.05sec).

Then when I ORDER BY distance (calculated value between restaurant and marker/pin ), LIMIT x, query is slow (~4sec) as ORDER BY cannot use an index as the distance is a calculated value...by design.

However, after much digging...it seems that there is a solution for such a problem: k-nn, nearest neighbor algorithm. K-nn is able to utilize the SPATIAL index greatly enhancing performance. 
https://postgis.net/workshops/postgis-intro/knn.html
  
I see that PostgreSQL + PostGIS, and SQL Server both have this extension, can we get it too ;-)

This is not a bug, but a critical limitation in such use cases where fast (<.5sec) filtering/mapping is required.

How to repeat:
n/a

[18 Nov 2020 14:13] MySQL Verification Team

HI Mr. h,

Thank you very much for your feature request.

We have analysed this request and concluded that it would be welcome improvement in our GIS code.

Verified as reported.