2

i am using mysql to store geolocation data. i would like to know how to optimize the table holding the (x,y) coordinates so that queries will be optimized against it. (the x,y will be lat/long).

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

at sometime later, i will perform a query: select * from geoloc where sqrt( (x-@x)^2 + (y-@y)^2 ) < delta

please note, i have no idea how the actual SQL statement will work right now, so the above is just a very rough idea of what i want.

so what do i have to do to optimize this table for this type of query? any pointers are greatly appreciated.

Improve this question

2 Answers 2

Reset to default
3

You'll have a hard time optimizing for that kind of query. A better option would be to compute a bounding box from the (x,y) coordinates and delta passed in. Then query for any locations where the coordinates fall in that box. That query would be much simpler and would be able to use any indexes you might have on the x and y fields.

Of course the results from that query aren't as exact since it's a bounding box rather than a circle. If you want better results, you can take the results from the bounding box query, then use the slower euclidean method to filter out the ones that don't fall into the circle.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

would the bounding box be inside the circle, or the circle inside the bounding box? could you show an example of such a sql query? right now, i found a lot of information on how to query for points within a circle. but as you said, the more accuracy you want the more computationally expensive it is.
@user - The circle would be inside the bounding box. This article gives a good example of how to do exactly what I'm talking about movable-type.co.uk/scripts/latlong-db.html
thanks. that seems to be exactly what i want. i will test it. i tested the equations from here meridianworlddata.com/Distance-Calculation.asp and they were acceptable (less than 1 second) for a mysql database with 1,000,000 records. however, with a database of over 5,000,000 records, the time for 1 query was a little under 3 minutes! this result was consistent regardless of delta. i don't need the precision/accuracy, just something that works. let me try and i'll get back to you. by the way, mysql has geospatial capabilities, is this another option to speed up querying?
yes. that link HELPED! thank you so much. it is lightning fast for 5,000,000 records! major vote up!
0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.