Asked  7 Months ago    Answers:  5   Viewed   79 times

I have data with latitude and longitude stored in my SQLite database, and I want to get the nearest locations to the parameters I put in (ex. My current location - lat/lng, etc.).

I know that this is possible in MySQL, and I've done quite some research that SQLite needs a custom external function for the Haversine formula (calculating distance on a sphere), but I haven't found anything that is written in Java and works.

Also, if I want to add custom functions, I need the org.sqlite .jar (for org.sqlite.Function), and that adds unnecessary size to the app.

The other side of this is, I need the Order by function from SQL, because displaying the distance alone isn't that much of a problem - I already did it in my custom SimpleCursorAdapter, but I can't sort the data, because I don't have the distance column in my database. That would mean updating the database every time the location changes and that's a waste of battery and performance. So if someone has any idea on sorting the cursor with a column that's not in the database, I'd be grateful too!

I know there are tons of Android apps out there that use this function, but can someone please explain the magic.

By the way, I found this alternative: Query to get records based on Radius in SQLite?

It's suggesting to make 4 new columns for cos and sin values of lat and lng, but is there any other, not so redundant way?

 Answers

20

1) At first filter your SQLite data with a good approximation and decrease amount of data that you need to evaluate in your java code. Use the following procedure for this purpose:

To have a deterministic threshold and more accurate filter on data, It is better to calculate 4 locations that are in radius meter of the north, west, east and south of your central point in your java code and then check easily by less than and more than SQL operators (>, <) to determine if your points in database are in that rectangle or not.

The method calculateDerivedPosition(...) calculates those points for you (p1, p2, p3, p4 in picture).

enter image description here

/**
* Calculates the end-point from a given source at a given range (meters)
* and bearing (degrees). This methods uses simple geometry equations to
* calculate the end-point.
* 
* @param point
*           Point of origin
* @param range
*           Range in meters
* @param bearing
*           Bearing in degrees
* @return End-point from the source given the desired range and bearing.
*/
public static PointF calculateDerivedPosition(PointF point,
            double range, double bearing)
    {
        double EarthRadius = 6371000; // m

        double latA = Math.toRadians(point.x);
        double lonA = Math.toRadians(point.y);
        double angularDistance = range / EarthRadius;
        double trueCourse = Math.toRadians(bearing);

        double lat = Math.asin(
                Math.sin(latA) * Math.cos(angularDistance) +
                        Math.cos(latA) * Math.sin(angularDistance)
                        * Math.cos(trueCourse));

        double dlon = Math.atan2(
                Math.sin(trueCourse) * Math.sin(angularDistance)
                        * Math.cos(latA),
                Math.cos(angularDistance) - Math.sin(latA) * Math.sin(lat));

        double lon = ((lonA + dlon + Math.PI) % (Math.PI * 2)) - Math.PI;

        lat = Math.toDegrees(lat);
        lon = Math.toDegrees(lon);

        PointF newPoint = new PointF((float) lat, (float) lon);

        return newPoint;

    }

And now create your query:

PointF center = new PointF(x, y);
final double mult = 1; // mult = 1.1; is more reliable
PointF p1 = calculateDerivedPosition(center, mult * radius, 0);
PointF p2 = calculateDerivedPosition(center, mult * radius, 90);
PointF p3 = calculateDerivedPosition(center, mult * radius, 180);
PointF p4 = calculateDerivedPosition(center, mult * radius, 270);

strWhere =  " WHERE "
        + COL_X + " > " + String.valueOf(p3.x) + " AND "
        + COL_X + " < " + String.valueOf(p1.x) + " AND "
        + COL_Y + " < " + String.valueOf(p2.y) + " AND "
        + COL_Y + " > " + String.valueOf(p4.y);

COL_X is the name of the column in the database that stores latitude values and COL_Y is for longitude.

So you have some data that are near your central point with a good approximation.

2) Now you can loop on these filtered data and determine if they are really near your point (in the circle) or not using the following methods:

public static boolean pointIsInCircle(PointF pointForCheck, PointF center,
            double radius) {
        if (getDistanceBetweenTwoPoints(pointForCheck, center) <= radius)
            return true;
        else
            return false;
    }

public static double getDistanceBetweenTwoPoints(PointF p1, PointF p2) {
        double R = 6371000; // m
        double dLat = Math.toRadians(p2.x - p1.x);
        double dLon = Math.toRadians(p2.y - p1.y);
        double lat1 = Math.toRadians(p1.x);
        double lat2 = Math.toRadians(p2.x);

        double a = Math.sin(dLat / 2) * Math.sin(dLat / 2) + Math.sin(dLon / 2)
                * Math.sin(dLon / 2) * Math.cos(lat1) * Math.cos(lat2);
        double c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
        double d = R * c;

        return d;
    }

Enjoy!

I used and customized this reference and completed it.

Tuesday, June 1, 2021
 
xrock
answered 7 Months ago
23

First you need a geographic location for each hotspot ideally using latitude and longitude. You can use the Google Geocoding API to go through each record and store lat/long.

Then you can match nearby hotspots with this SQL query and order by distance.

Hope that helps.

Saturday, May 29, 2021
 
Farnabaz
answered 7 Months ago
39

The

<uses-library android:name="com.google.android.maps" />

should be contained in the <application> node and not the <activity> node. That should fix the problem because as of now, you are not requesting for the maps permission.

Friday, August 6, 2021
 
linjuming
answered 4 Months ago
15

Your subject line is misleading. The exception in your stacktrace says

Couldn't read row 0, col -1 from CursorWindow

which essentially means that getColumnIndex() could not find the specified column in the Cursor and -1 was returned as column index.

Your selection SELECT * contains all columns from the table.

Your CREATE TABLE seems to contain all columns you're requesting the index of with getColumnIndex() in getNotes().

Chances are that you've added a column but the test device still has a database file without that column. Just uninstall your app or clear its data to remove the old database file and make your onCreate() recreate the database on the next run.

Saturday, August 14, 2021
 
Zizoo
answered 4 Months ago
52

You should use CREATE TABLE IF NOT EXISTS instead of CREATE TABLE. Your code would then look like this:

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE IF NOT EXISTS" + TABLE_NAME + " (" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_DATE + " TEXT, " +
            COLUMN_EXERCISE + " TEXT, " +
            COLUMN_WEIGHT + " TEXT, " +
            COLUMN_REPS + " TEXT, " +
            COLUMN_SET_TIME + " TEXT, " +
            COLUMN_WORKOUT_TIME + " TEXT, " +
            COLUMN_DISTANCE + " TEXT, " +
            COLUMN_SPEED + " TEXT, " +
            COLUMN_REST_TIME + " TEXT, " +
            COLUMN_NUMBER_OF_SETS + " TEXT, " +
            COLUMN_FIELDS_PARAMETER + " TEXT, " +
            COLUMN_NOT_ON_LIST_EXERCISE + " TEXT, " +
            COLUMN_HERAT_BEAT + " TEXT, " +
            COLUMN_CALORIES + " TEXT, " +
            COLUMN_COMMENT + " TEXT);");

    db.execSQL("CREATE TABLE IF NOT EXISTS" + SECOND_TABLE_NAME + " (" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_AGE + " TEXT, " +
            COLUMN_AGE_PW + " TEXT, " +
            COLUMN_GENDER + " TEXT, " +
            COLUMN_HEIGHT + " TEXT, " +
            COLUMN_HEIGHT_PW + " TEXT, " +
            COLUMN_BODY_WEIGHT + " TEXT, " +
            COLUMN_BODY_WEIGHT_PW + " TEXT, " +
            COLUMN_PROFILE_PIC + " TEXT, " +
            COLUMN_FAT + " TEXT, " +
            COLUMN_FAT_PW + " TEXT);");

    db.execSQL("CREATE TABLE IF NOT EXISTS" +  MEDIA_TABLE + " (" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_PICS + " TEXT, " +
            COLUMN_VIDEO + " TEXT);");

}       

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    db.execSQL("DROP TABLE IF EXISTS " + SECOND_TABLE_NAME);
    db.execSQL("DROP TABLE IF EXISTS " + MEDIA_TABLE);
    onCreate(db);   
}
Wednesday, September 15, 2021
 
Hong
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share