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

Here is an implementation in Java for building a location based query on an Android device. The idea comes from KennyTM (see accepted response) and implies the addition of 4 columns in your table to store values of sinus and cosinus of latitude and longitudes.

Here is the code preparing the data for a "Shop" table at insert time:

public static void injectLocationValues(ContentValues values, double latitude, double longitude) {     values.put(LocationColumns.LATITUDE, latitude);     values.put(LocationColumns.LONGITUDE, longitude);     values.put(LocationColumns.COSLAT, Math.cos(MathUtil.deg2rad(latitude)));     values.put(LocationColumns.SINLAT, Math.sin(MathUtil.deg2rad(latitude)));     values.put(LocationColumns.COSLNG, Math.cos(MathUtil.deg2rad(longitude)));     values.put(LocationColumns.SINLNG, Math.sin(MathUtil.deg2rad(longitude))); }  public static double deg2rad(double deg) {     return (deg * Math.PI / 180.0); } 

You can then build your projection using the following function:

/**  * Build query based on distance using spherical law of cosinus  *   * d = acos(sin(lat1).sin(lat2)+cos(lat1).cos(lat2).cos(long2−long1)).R  * where R=6371 and latitudes and longitudes expressed in radians  *   * In Sqlite we do not have access to acos() sin() and lat() functions.  * Knowing that cos(A-B) = cos(A).cos(B) + sin(A).sin(B)  * We can determine a distance stub as:  * d = sin(lat1).sin(lat2)+cos(lat1).cos(lat2).(cos(long2).cos(long1)+sin(long2).sin(long1))  *   * First comparison point being fixed, sin(lat1) cos(lat1) sin(long1) and cos(long1)  * can be replaced by constants.  *   * Location aware table must therefore have the following columns to build the equation:  * sinlat => sin(radians(lat))  * coslat => cos(radians(lat))  * coslng => cos(radians(lng))  * sinlng => sin(radians(lng))  *    * Function will return a real between -1 and 1 which can be used to order the query.  * Distance in km is after expressed from R.acos(result)   *    * @param latitude, latitude of search  * @param longitude, longitude of search  * @return selection query to compute the distance  */ public static String buildDistanceQuery(double latitude, double longitude) {     final double coslat = Math.cos(MathUtil.deg2rad(latitude));     final double sinlat = Math.sin(MathUtil.deg2rad(latitude));     final double coslng = Math.cos(MathUtil.deg2rad(longitude));     final double sinlng = Math.sin(MathUtil.deg2rad(longitude));     //@formatter:off     return "(" + coslat + "*" + LocationColumns.COSLAT             + "*(" + LocationColumns.COSLNG + "*" + coslng             + "+" + LocationColumns.SINLNG + "*" + sinlng             + ")+" + sinlat + "*" + LocationColumns.SINLAT              + ")";     //@formatter:on } 

It will inject a response column with the distance on which you need to apply the following formula to convert in kilometers:

public static double convertPartialDistanceToKm(double result) {     return Math.acos(result) * 6371; } 

If you want to order your query using the partial distance, you need to order DESC and not ASC.