Query to get records based on Radius in SQLite?

by

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.

More Related Contents:

  1. Android multiple databases open
  2. Selecting more than one value
  3. Is it possible to insert multiple rows at a time in an SQLite database?
  4. SQLite Reset Primary Key Field
  5. Best way to work with dates in Android SQLite [closed]
  6. How do I dump the data of some SQLite3 tables?
  7. SQLite – How do you join tables from different databases?
  8. Declare variable in SQLite and use it
  9. Does SQLite3 not support foreign key constraints?
  10. How to split comma-separated value in SQLite?
  11. How do I add a foreign key to an existing SQLite table?
  12. How to use SQL Order By statement to sort results case insensitive?
  13. Android SQLite: Update Statement
  14. Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine
  15. sqlite alter table add MULTIPLE columns in a single statement
  16. Is there AUTO INCREMENT in SQLite?
  17. Get month from DATETIME in sqlite
  18. How do I import .sql files into SQLite 3?
  19. sqlite equivalent of row_number() over ( partition by …?
  20. SQLite3 UNIQUE constraint failed error
  21. Does SQLite3 have prepared statements in Node.js?
  22. SQlite: select into?
  23. SQL error: misuse of aggregate
  24. IF() statement alternative in SQLite
  25. SQLite query, ‘LIKE’
  26. How to use a calculated column by another calculated column
  27. UPSERT *not* INSERT or REPLACE
  28. Drop column from SQLite table
  29. converting int to real in sqlite
  30. sqlite insert into table select * from

Leave a Comment