CODEWITHSUNDEEP
android
Shri
Shri

Reputation: 1243

Storing and Retrieving Location Data from SQLite

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

Upvotes: 0

Views: 1537

Answers (2)

Giovanni Castelli
Giovanni Castelli

Reputation: 1

Consider using Spatialite.

SpatiaLite is an open-source library intended to extend the SQLite core to support fully fledged Spatial SQL capabilities. You can use spatial index and specialized functions to calculate distances between geometries (points, lines, polygons).

Upvotes: 0

Stefan
Stefan

Reputation: 4705

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.

Upvotes: 1

Related Questions