0

I want to make a table of with columns in a MySQL database:

  1. Index
  2. Latitude
  3. Longitude
  4. Places like place of Countries, Cities, People, Building etc.

With huge number of rows, in order of hundred thousands until million of rows.

If I want to get nearest places of a selected row in the table, how can I do that in the fastest way?

It is no problem if more information, indexing, or presorting are necessary.

======
Edit 1:
I have read the answer and the answer is using a formula, for example from the best answer:
(((acos(sin((".$latitude."*pi()/180)) * sin((geo_latitude*pi()/180))+cos((".latitude."*pi()/180)) * cos((geo_latitude*pi()/180)) * cos(((".$longitude."- geo_longitude)*pi()/180))))*180/pi())*60*1.1515*1.609344)

If I have 1 million rows, that means, there are 1 million of expensive calculation. I thing it will be very slow.

Are the optimization, for example using filtering in the beginning:
1. If the input is City A in location 10.000, 20.000, then filter cities that located at 9.000 to 11.00.
2. Calculate with the formula above.

How to optimize the speed of that algorithm?

====
Edit 2:
Sorry, I've only read the best answer.
I found what I've looked for in the other answer: http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

Yohanes Nurcahyo
  • 601
  • 8
  • 19

1 Answers1

0

You can use a quadkey. A quadkey is a spatial index like a quadtree. It sort the points into a grid and then you can search the grid around the center point. It's not easy to understand but you can download my php class hilbert-curve @ phpclasses.org. Or you can use the native MySQL spatial extension and the point datatype. However my implementation uses a quadkey and a hilbert-curve and can be better. It depends much on the data. The problem with the harvesine formula is that it is very slow. But you can use both algorithms together to achieve better results.

Micromega
  • 12,486
  • 7
  • 35
  • 72