I want to order the results from a database according to the distance to an object. Both my object and my database items have lat and lng and I can calculate distance with haversine.
The problem is that they need to be selected by distance from the database. I can't pull out the whole database, make calculations and then take the results I need, because ... a kitty would die every time I do it.
Some people suggest putting haversine formula to the order_by. How many kittens will die?
Some people suggest some kind of spatial plugins. What do they do? Which one should I use?
Another alternative that seemed ugly was that you take manually some distance and make a square by ruling out everything bigger and smaller of the lat and lng. If you don't have enough results, or you have too many results, you correct the distance and run query again.
Can anyone tell what is the best way that I should go with pro's con's. Also I am using LAMP/MAMP with CakePHP.
Use both:
WHERE table.latitude BETWEEN $latmin AND $latmax
AND table.longitude BETWEEN $lonmin AND $lonmax
ORDER BY haversine(....)
HAVING haversine(...) < $your_desired_distance
It'll still be a heavy duty order-by, but you'll have eliminated any "obvious" out-of-range candidates with the very simple/light-weight WHERE filters, so you'll be doing the actual distance calculations on a small(er?) subset of all possible records.
You could use a quadkey. It's a spatial index using a space filling curve. It's not an exact solution but very fast and you are free to use the haversine formulae. You can download my php class hilbert -curve @ phpclasses.org. It also uses a mercator projection.