title: Find closest lat and long in an SQLite database
body: <p>I have an SQLite database made up of four columns - ID, WindSpeed, Longitude and Latitude. </p>
  
  <p>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. </p>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Here is my code</p>
  
  <pre><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;
  }
   }
  </code></pre>
  
  <p>If the value of the lat and long being string causes a problem these can be converted to doubles in my database. </p>
  
  <p>Any help would be massivley appreciated because im in a real pickle </p>
  
  <p>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 </p>
  
  <p>EDIT</p>
  
  <p>So ive tried to implement what laborno but im unable to get it to work here is the code that im using!</p>
  
  <p>would i have to use two cursors or change my database ?</p>
  
  <p>public class MyDatabase extends SQLiteAssetHelper {</p>
  
  <pre><code>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 &lt; min_distance){
       wind_speed=d.getString(1);
        double windd = Double.parseDouble(wind_speed);
  
     return windd;
     }
       }
     return null;
  }
  }
  </code></pre>
  
  <p>But it doesnt seem to be computing the result and im getting errors. 
  Here are the error codes</p>
  
  <pre><code>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)
  </code></pre>