How to optimize geolocation database in mysql to search / query Euclidean distance

Question

I use mysql to store geolocation data. I would like to know how to optimize a table containing coordinates (x, y) so that queries are optimized against it. (x, y will be lat / long).

for example, I have a table with the following fields: id, x, y, notes.

after a while I will select the query: Select * from the geoloc where sqrt ((x- @x) ^ 2 + (y- @y) ^ 2) delta

Please note: I don’t know how the actual SQL statement will work right now, so the above is just a very crude idea of ​​what I want.

What do I need to do to optimize this table for this type of query? any pointers are welcome.

Answer 1

You will have a difficult optimization for this kind of query. A better option would be to compute the bounding box from the coordinates (x,y) and delta passed to. Then a request for any places where the coordinates fall into this field. This query would be much simpler and could use any indexes that may be in the x and y fields.

Of course, the results of this query are not so accurate, since it is a bounding box, not a circle. If you want to get better results, you can take the results from the query frame, and then use the slower Euclidean method to filter those that do not fall into the circle.

Answer 2

Perhaps you could try Voronoi diagrams. Take a look here http://www.cs.sunysb.edu/~algorith/files/nearest-neighbor.shtml