Storing and Retrieving Location Data from SQLite

Question

I know there many solutions available in Stackover flow but looks none have gone to address the basic question I have is - I have more than 2500 lon/lat data - I want to store/retrive them from sqlite - Query nearest locations based on user input. Looking for optimum solutions

Note: I have gone through Finding the closest point to a given point

What is this Geohashing all about How can use Geohashing in my this particular problem

score 1 · Answer 1 · answered Feb 14 '12 at 14:20

Geohashing is an encoding of latitude, longitude pairs such that points in proximity to eaxh other have geohashes with a common prefix. However, this does not work with every coordinate on the planet, i.e. there regions where the goehash changes significantly for points in proximity. Depending on the hashing algorithms the area near to equator may be such an area. See here for more detail: http://en.wikipedia.org/wiki/Geohash

For a relatively small database of ca. 500 locations I was able to find the nearest location to a given point of reference (the user's location) very fast by searching for points inside an intervall of 0.1 degrees. Here is the code for the query:

/**
 * Query the airfields table for airfields near the given position.
 * @param dbCon DB connection
 * @param ref_lat latitude
 * @param ref_lon longitude
 * @return Answer the airfield nearest to the given position as array
 *          of objects: id, designator, latitude, longitude.
 *          Answer <code>null</code> if their is no airfield near the
 *          given position plus or minus 0.1 degrees.
 */
private Object[] rangeQuery(final SQLiteDatabase dbCon, final double ref_lat, final double ref_lon) {
    if( DEBUG )
        Log.d( TAG, "rangeQuery lat=" + ref_lat + ", lon=" + ref_lon);
    final SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
    qb.setTables(AirfieldsTable.TABLE_NAME);
    final String[] whereArgs = new String[] {
            Double.toString(ref_lat - 0.1d), Double.toString(ref_lat + 0.1d),
            Double.toString(ref_lon - 0.1d), Double.toString(ref_lon + 0.1d)
    };
    final Cursor crsr = qb.query(dbCon, allFields(), AirfieldsTable.RANGE_CLAUSE, whereArgs, null, null, null);
    final Object[] val = this.scanForNearest(crsr, ref_lat, ref_lon);
    crsr.close();
    if( DEBUG )
        Log.d( TAG, "scanForNearest returned " + val);
    return val;
}

If there is more than one row selected I compare the remaining points directly (thats what scanForNearest() does). Its fast enough to find the airport after the logger (its a logging application) detected a landing.

Thanks Stefan, is 0.1 degree a more like general solutions, my need is for ex: five nearest locations of given location, actually what I'm trying to ask is 1. Good way to store location data in sqlite 2. Faster query of say 5 nearest locations to given point. I tried with soultion pythogorous theorem + edge factor but its terribly slow to query five locations among database of 2500 entries — Shri, Feb 14 '12 at 15:39

Storing and Retrieving Location Data from SQLite

1 Answers1