Query to get records based on Radius in SQLite?

Question

I have this query which does work fine in MySQL

SELECT ((ACOS(SIN(12.345 * PI() / 180) * SIN(lat * PI() / 180) +
         COS(12.345 * PI() / 180) * COS(lat * PI() / 180) * COS((67.89 - lon) * 
         PI() / 180)) * 180 / PI()) * 60 * 1.1515 * 1.609344) AS distance, poi.* 
FROM poi
WHERE lang='eng' 
HAVING distance<='30'

distance is in Kilometers, the input is lat=12.345 and lon=67.89

The SQLite is 3, and I can't run custom functions with it as it's on Android. I also don't have acos() etc... as that is not part of the standard SQLite.

How would be the above query in SQLite?

Answer 1

You can create 4 new columns, being sin and cos of lat and lon. Since cos(a+b) = cos a cos b - sin a sin b, and other appearances of sin and cos like SIN(12.345 * PI() / 180) can be calculated in the program before running the query, the big "distance" expression reduces to something of the form P * SIN_LAT + Q * COS_LAT + ... that can be handled by SQLite3.

BTW, see also http://stackoverflow.com/questions/2352320/sqlite-on-android-how-to-create-a-sqlite-dist-db-function-to-be-used-in-the-ap.