Find near locations with SQLite and Android

Question

I have a data table in my SQLite database with information about longitude, latitude and description. Given a certain location, I would like to get all the rows with a near distance (10 blocks) from that location.

This is how data is loaded:

values.put(LONGITUD_COLUMN,  (int) (-64.14802 * 1E6));
values.put(LATITUD_COLUMN, (int) (-31.36498 * 1E6));

So far this is what I have in code:

private int cuadras = (int)(-0.02 * 1E6) ; //10 blocks aprox, I guess
private int current_latitude = 0;
private int current_longitude = 0;

current_latitude and current_longitude, are loaded with (from LocationListener):

public void onLocationChanged(Location location) {
 current_latitude = (int) (location.getLatitude() * 1E6);
 current_longitude = (int) (location.getLongitude() * 1E6);
}

And the SQLite query is as follows:

String sql = "LATITUD_COLUMN > (" + (current_latitude + cuadras) + ") AND "
+ "LATITUD_COLUMN < (" + (current_latitude - cuadras) +") AND "
+ "LONGITUD_COLUMN > ("+ (current_longitude - cuadras) +") AND "
+ "LONGITUD_COLUMN < ("+ (current_longitude + cuadras) +")";

With this result:

LATITUD_COLUMN > (-31398170) AND LATITUD_COLUMN < (-31358170) AND LONGITUD_COLUMN > (-64132838) AND LONGITUD_COLUMN < (-64172838)

That query returns no results at all, my question is, how can I query the DataBase bringing all the locations near my current location?

Thanks!

Have you incorporated that condition into an actual sql query of the form: SELECT * from yourtablename WHERE ....; ? (The dots represent you String sql) — NickT, Nov 22 '11 at 20:06
Follow this answer: http://stackoverflow.com/a/12997900/779408 — Bob, Oct 11 '15 at 08:37

score 4 · Accepted Answer · answered Nov 22 '11 at 20:09

You appear to have your + and - mixed up:

String sql = "LATITUD_COLUMN > (" + (current_latitude + cuadras) + ") AND "
+ "LATITUD_COLUMN < (" + (current_latitude - cuadras) +") AND "
+ "LONGITUD_COLUMN > ("+ (current_longitude - cuadras) +") AND "
+ "LONGITUD_COLUMN < ("+ (current_longitude + cuadras) +")";

should be

String sql = "LATITUD_COLUMN > (" + (current_latitude + cuadras) + ") AND "
+ "LATITUD_COLUMN < (" + (current_latitude - cuadras) +") AND "
+ "LONGITUD_COLUMN > ("+ (current_longitude + cuadras) +") AND "
+ "LONGITUD_COLUMN < ("+ (current_longitude - cuadras) +")";

Note the swapped + and - in the last two lines.

LONGITUD_COLUMN > (-64132838) AND LONGITUD_COLUMN < (-64172838) is always false. :-)

Find near locations with SQLite and Android

1 Answers1