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

Giovanni Castelli · Answer 2 · 2023-07-31T11:37:26.547

0

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).

edited Jul 31 '23 at 11:37

answered Jul 31 '23 at 11:35

Giovanni Castelli

1
2

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 07 '23 at 08:23

Storing and Retrieving Location Data from SQLite

2 Answers2