0

I have a coordinate. I want to find the top "n" (n being a variable value) nearest coordinates out of several thousand rows stored on a MySQL database. I also want to be able to define maximum and minimum distances between the coordinate in question and the coordinates in the database.

How best am I to go about this? Would it be bonkers to use PHP as I understand the syntax much better than MySQL?

If I use a MySQL function, how do I move it between databases if I choose to switch servers? How is it stored?

Lastly, what is the most efficient method of getting through all these coordinates accurately - the coordinates are all relatively close to one another?

Thanks for your time,

John.

John Hamelink
  • 1,036
  • 2
  • 15
  • 33
  • 1
    This link was helpful to me: http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL And, another stackoverflow question: http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula – Billiam Apr 13 '10 at 19:39
  • This question has been solved before (even on this site). See @Billiam's comment. The only advice I can give is to avoid hammering the database as much as possible by caching "popular areas" or something. That will give you the most efficient solution. – aduric Apr 13 '10 at 20:01
  • I had a similar problem in my hands a short time ago: a table full of locations, and given a location X (most commonly the user's location), I wanted to find out what are the N closest locations from my table, maybe filtering through other criteria at the same time. I realized that it would be very bad to keep querying the database and also that it would be too much to cache every single query, so I just ended up doing something like calculating the ordered list of locations according to distance from X, (mem)caching that for some time and applying additional display filters on that. – Joaquim Rendeiro Apr 13 '10 at 20:57
  • @aduric: A good trick to reduce database load would be to apply a "WHERE" clause which would limit the number of points to the do further calculations on. For instance, with X being Latitude and Y being Longitude - "WHERE longitude BETWEEN X-3 AND X+3 AND latitude BETWEEN Y-3 AND Y+3". Of course the "3" would vary dependent on the density of points and/or the degrees spanned by the maximum linear distance at the specified Latitudes/Longitudes. – Luke Stevenson Aug 11 '10 at 16:22

1 Answers1

0

It would be bonkers to do this in PHP, and not farm this off to MySQL. If you stick to double precision floating point numbers then you should have better precision than available accuracy (i.e. civilian GPS is accurate to at best about 5m, but your calculations will give a precision of a few centimetres)

Community
  • 1
  • 1
Rowland Shaw
  • 37,700
  • 14
  • 97
  • 166