CODEWITHSUNDEEP
mysqlsql
dsign
dsign

Reputation: 12700

Is there a way with nearest neighbour search in MySQL?

I have the following table:

 CREATE TABLE numbert_t ( v DOUBLE , id INTEGER, INDEX(v) )

and I want to do a query with a paremeter q that sorts the points in distance abs( q - v ). For example

 SELECT v, id, ABS( q - v ) AS d FROM number_t ORDER BY d

I tried the query above and this one:

 SELECT v, id, (v - q) AS d FROM numbers_t WHERE (v - q) > 0
 ORDER BY d

I also tried slight variations of the above:

 SELECT v, id, (v - q) AS d FROM numbers_t WHERE v > q ORDER BY v

They are not equivalent, but I don't mind to do two queries and have two independent cursors. However, in all cases EXPLAIN says that filesort, no indices would be used. Can I get MySQL to somehow use indices for this problem?

Upvotes: 1

Views: 357

Answers (2)

Joop Eggen
Joop Eggen

Reputation: 109557

Did you try:

SELECT MIN(v), id FROM number_t WHERE v >= q
UNION
SELECT MAX(v), id FROM number_t WHERE v < q

MySQL specific, not standard, because of the id. But the id might be retrieved after you have gotten exact values.

Upvotes: 2

Cybercartel
Cybercartel

Reputation: 12592

You can use the spatial extension and a point datatype. Then you can use a proximity search for example when a point is inside a bounding box. You can also use my quadkey library. It uses a hilbert curve and a mercator projection. You can download my php class hilbert curve @ phpclasses.org.

Upvotes: 1

Related Questions