Find closest lat and long in an SQLite database

Question

I have an SQLite database made up of four columns - ID, WindSpeed, Longitude and Latitude.

What im doing is, getting the Latitude and longitude of the user using the Android location service using the GPS and then querying the latitude and longitude of that location with the database to find the wind speed at that location.

But as ive mentioned ive come into a little bit of a problem, my query work if i use the correct lat and long in the table but what i need to do is find the nearest lat and long and the resulting wind speed of the users current location.

So if the lat and long was 51.652161 and -3.0567927 it would find the nearest value in my database to these two values and the return the resultant wind speed at that row back.

So i put in my current lat and long it find the nearest values to these in the table where the two of these meet and then give the wind speed value where they meet.

Here is my code

public class MyDatabase extends SQLiteAssetHelper {

private static final String DATABASE_NAME = "WindSpeed.sqlite";
private static final int DATABASE_VERSION = 1;
private static final String LAT_VAL = "Latitude";
private static final String LONG_VAL = "Longitude";
private static final String WIND_SPEED= "Windspeed";
private static final String ROW_ID=     " _id";

public MyDatabase(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
 public Double getWindSpeed2(){

    double Long = postcodeInput.getlng();  
    double Lat = postcodeInput.getlat();

    SQLiteDatabase db = getReadableDatabase();
    SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

    String [] sqlSelect = {ROW_ID,WIND_SPEED,LAT_VAL,LONG_VAL}; 
    String sqlTables = "WindSpeed";
    String whereClause = LAT_VAL+" = ? AND "+ LONG_VAL +" = ?";
    String[] whereArgs = new String[] {
            String.valueOf(Lat),
            String.valueOf(Long)
        };
    qb.setTables(sqlTables);
    Cursor d = qb.query(db, sqlSelect, whereClause, whereArgs,
            null, null, null);
   if (d != null ){
       d.moveToFirst();

    String Windd = d.getString(1);

    double win_d = 0.0;

    win_d= Double.parseDouble(Windd);


    return win_d;
   }
   return null;
}
 }

If the value of the lat and long being string causes a problem these can be converted to doubles in my database.

Any help would be massivley appreciated because im in a real pickle

Ive searched high and low for similar problems and a lot mention the havernsine forumla but im not sure how id implement that with mine. Thanks

EDIT

So ive tried to implement what laborno but im unable to get it to work here is the code that im using!

would i have to use two cursors or change my database ?

public class MyDatabase extends SQLiteAssetHelper {

private static final String DATABASE_NAME = "WindSpeed.sqlite";
private static final int DATABASE_VERSION = 1;
private static final String LAT_VAL = "Latitude";
private static final String LONG_VAL = "Longitude";
private static final String WIND_SPEED= "Windspeed";
private static final String ROW_ID=     " _id";
private static final double Lat=   58.0745976;
private static final double Long=  -5.3846407;
public MyDatabase(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

public Double getWindSpeed2(){

    double fudge = Math.pow(Math.cos(Math.toRadians(Lat)),2);

    SQLiteDatabase db = getReadableDatabase();
    SQLiteQueryBuilder qb = new SQLiteQueryBuilder();


    String wind_speed=null;
    double min_distance=99999999;

    String [] sqlSelect = {WIND_SPEED,LAT_VAL,LONG_VAL}; 
    String sqlTables = "WindSpeed";
    String whereClause = " ? between "+LAT_VAL+"- 0.1 and "+LAT_VAL+" + 0.1 and ?     between "+LONG_VAL+" - 0.1 and "+LONG_VAL+" + 0.1";
    String[] whereArgs = new String[] {
            String.valueOf(Lat),
            String.valueOf(Long)};
    qb.setTables(sqlTables);
    Cursor d = qb.query(db, sqlSelect, whereClause, whereArgs,
            null, null, null);
   if (d != null ){
 d.moveToFirst();
  double lat_v =Double.parseDouble(d.getString(3));
    double long_v=Double.parseDouble(d.getString(2));
     double dis = (Math.sqrt(lat_v-Lat)*(lat_v-Lat) + (long_v-Long)*(long_v-Long) );
     if (dis < min_distance){
     wind_speed=d.getString(1);
      double windd = Double.parseDouble(wind_speed);

   return windd;
   }
     }
   return null;
}
}

But it doesnt seem to be computing the result and im getting errors. Here are the error codes

08-04 12:51:32.007: E/AndroidRuntime(1458): FATAL EXCEPTION: main
08-04 12:51:32.007: E/AndroidRuntime(1458):         android.database.CursorIndexOutOfBoundsException: Index 0 requested, with a size of 0
08-04 12:51:32.007: E/AndroidRuntime(1458):     at android.database.AbstractCursor.checkPosition(AbstractCursor.java:424)
08-04 12:51:32.007: E/AndroidRuntime(1458):     at android.database.AbstractWindowedCursor.checkPosition(AbstractWindowedCursor.java:136)
08-04 12:51:32.007: E/AndroidRuntime(1458):     at android.database.AbstractWindowedCursor.getString(AbstractWindowedCursor.java:50)
08-04 12:51:32.007: E/AndroidRuntime(1458):     at com.example.mobilegreenenergyevaluator.MyDatabase.getWindSpeed2(MyDatabase.java:80)
08-04 12:51:32.007: E/AndroidRuntime(1458):     at com.example.mobilegreenenergyevaluator.postcodeInput$2.onClick(postcodeInput.java:67)
08-04 12:51:32.007: E/AndroidRuntime(1458):     at android.view.View.performClick(View.java:4204)
08-04 12:51:32.007: E/AndroidRuntime(1458):     at android.view.View$PerformClick.run(View.java:17355)
08-04 12:51:32.007: E/AndroidRuntime(1458):     at android.os.Handler.handleCallback(Handler.java:725)
08-04 12:51:32.007: E/AndroidRuntime(1458):     at android.os.Handler.dispatchMessage(Handler.java:92)
08-04 12:51:32.007: E/AndroidRuntime(1458):     at android.os.Looper.loop(Looper.java:137)
08-04 12:51:32.007: E/AndroidRuntime(1458):     at android.app.ActivityThread.main(ActivityThread.java:5041)
08-04 12:51:32.007: E/AndroidRuntime(1458):     at java.lang.reflect.Method.invokeNative(Native Method)
08-04 12:51:32.007: E/AndroidRuntime(1458):     at java.lang.reflect.Method.invoke(Method.java:511)
08-04 12:51:32.007: E/AndroidRuntime(1458):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793)
08-04 12:51:32.007: E/AndroidRuntime(1458):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560)
08-04 12:51:32.007: E/AndroidRuntime(1458):     at dalvik.system.NativeStart.main(Native Method)
08-04 12:51:34.327: E/Trace(1475): error opening trace file: No such file or directory (2)

These are all using MySql and im not sure how id convert this to SQLite — user3025572, Aug 04 '14 at 00:49
it seems that the query is not getting results. I changed the IF clause so it won't crash. Also, try a location close to the existing ones in the database. — lalborno, Aug 04 '14 at 13:16
Not sure why it isnt working, the location im using is one thats already in the database and so it should find it. Btw you have been alot of help and i thank you for all that you have done so far — user3025572, Aug 04 '14 at 16:21

lalborno · Answer 1 · 2014-08-04T13:12:49.917

3

To find the nearest ponit you have to calculate the distance between two ponits.

distance=sqrt( (x1-x0)^2 + (y1-y0)^2)

And then find the smallest distance d between user location and the database. But this is quite expensive if you have thousands or millions of data in your database.

I suggest to narrow the database results and then find the nearest point. To narrow the results you can first find the ponits "near" to user location. Something like

String whereClause = " ? between "+LAT_VAL+"- 0.1 and "+LAT_VAL+" + 0.1 and ? between "+LONG_VAL+" - 0.1 and "+LONG_VAL+" + 0.1"

And then find the smallest distance (untested):

String [] sqlSelect = {ROW_ID,WIND_SPEED,LAT_VAL,LONG_VAL}; 
...

String row_id=null;
String wind_speed=null;
double min_distance=99999999;
if (d != null && d.moveToFirst()){
  double lat_v=Double.parseDouble(d.getString(2));
  double long_v=Double.parseDouble(d.getString(3));
  double dis=sqrt( (lat_v-Lat)*(lat_v-Lat) + (long_v-Long)*(long_v-Long) )
  if (dis<min_distance){
    row_id=d.getString(0);
    wind_speed=d.getString(1);
  }
}

return wind_speed;

edited Aug 04 '14 at 13:12

answered Aug 04 '14 at 00:42

lalborno

414
3
5

So im guessing this would be a sort order? – user3025572 Aug 04 '14 at 00:50
Yes. Once you have the points near the user location, then you should sort them by distance to user location. – lalborno Aug 04 '14 at 02:16
How would i be able to implement this with my code, im little confused tbh – user3025572 Aug 04 '14 at 02:25
Any luck with your question? – Leonardo Sibela Sep 11 '16 at 01:44

Find closest lat and long in an SQLite database

1 Answers1