what is better? Do calculations using the programming language or by using the database server

advertisements

suppose I've a table of locations(latitude, longitude) and I want to get all the location within a specified range to my current location, my question which is better: to include the calculations in the where clause of the query, something like, select * from locations where /* formula of cosines and sines of the latitude and longitude */ < distance.
my second choice is to get all the locations and do the calculations locally in the program. I'm using java and mysql if it matters


Depending on the data and complexity of the calculation, you might try performing a mix of the two. Use a simpler, less precise, but more expedient calculation (capable of taking advantage of indices) to reduce the results sent back for final processing.

For example, if the criteria is "5 miles away", instead of performing the equation involving geometric functions; you could calculate a bounding box. Retrieve the rows in that range, and then use the more complex function to exclude the "corners".

Alternately, if your server is powerful and expected clients relatively weak computationally speaking; it might be better to off load those calculations to the server anyway.

Edit: Another possibility is to put the more complex calculation in a HAVING clause, keeping the simpler one in the WHERE, that way it will only need performed on the ones that pass the simpler, more index friendly, filter.

Edit2: To give a generic example (since I am unacquainted with using long and lat; your equations probably need to account for the globe "wrap").

SELECT *
FROM theTable
WHERE x BETWEEN [minX] AND [maxX]
AND y BETWEEN [minY] AND [maxY]
HAVING POW(x-[originX], 2) + POW(y-[originY], 2) <= POW([distance], 2)
;