Sqlite: select items based on distance (latitude and longitude)

Question

I have a table in wich items are stored using the geographic location based on latitude and longitude. At one point I want to retrieve only those items from the database that are in a specific range (distance or radius) of my current position.

Item (latitude, longitude)

So I followed this really nice post and tried to implement the answer with the highest votes. First thing: I had to add some more columns when storing the items. This enables the select-statement later on:

Item (lat, lat_cos, lat_sin, lng, lng_cos, lng_sin)

So far so good, but when I then try to build the query, I go nuts. If I use the less-symbol < then all items are retrieved (even if too far away). But if I use the greater-symbol > no items are retrieved. But I know that there are items in my range.

Question: So what am I doing wrong with that term?

WHERE (coslat * LAT_COS * (LNG_COS * coslng + LNG_SIN * sinlng)) + sinlat * LAT_SIN > cos_allowed_distance

Here's the code that I'm using for querying my ContentProvider. The ContentInfo class has fields for sortOrder, selection and selectionArgs:

    public static ContentInfo buildDistanceWhereClause(double latitude, double longitude, double distanceInKilometers) {

        final double coslat = Math.cos(Math.toRadians(latitude));
        final double sinlat = Math.sin(Math.toRadians(latitude));
        final double coslng = Math.cos(Math.toRadians(longitude));
        final double sinlng = Math.sin(Math.toRadians(longitude));

        // (coslat * LAT_COS * (LNG_COS * coslng + LNG_SIN * sinlng)) + sinlat * LAT_SIN > cos_allowed_distance
        final String where = "(? * ? * (? * ? + ? * ?)) + ? * ? > ?";
        final String[] whereClause = new String[] {
                String.valueOf(coslat),
                ItemTable.COLUMN_LATITUDE_COS,
                ItemTable.COLUMN_LONGITUDE_COS,
                String.valueOf(coslng),
                ItemTable.COLUMN_LONGITUDE_SIN,
                String.valueOf(sinlng),
                String.valueOf(sinlat),
                ItemTable.COLUMN_LATITUDE_SIN,
                String.valueOf(Math.cos(distanceInKilometers / 6371.0))
        };

        return new ContentInfo(null, where, whereClause);
    }
}

Matthias · Accepted Answer · 2015-11-06T10:29:41.223

I don't actually see the problem, but I re-engineered the selection/where so it's easier to read. And now it works. You can also checkout this fiddle.

public static String buildDistanceWhereClause(double latitude, double longitude, double distanceInKilometers) {

    // see: http://stackoverflow.com/questions/3126830/query-to-get-records-based-on-radius-in-sqlite

    final double coslat = Math.cos(Math.toRadians(latitude));
    final double sinlat = Math.sin(Math.toRadians(latitude));
    final double coslng = Math.cos(Math.toRadians(longitude));
    final double sinlng = Math.sin(Math.toRadians(longitude));

    final String format = "(%1$s * %2$s * (%3$s * %4$s + %5$s * %6$s) + %7$s * %8$s) > %9$s";
    final String selection = String.format(format,
            coslat, COLUMN_LATITUDE_COS,
            coslng, COLUMN_LONGITUDE_COS,
            sinlng, COLUMN_LONGITUDE_SIN,
            sinlat, COLUMN_LATITUDE_SIN,
            Math.cos(distanceInKilometers / 6371.0)
            );

    Log.d(TAG, selection);
    return selection;
}

Hi. I'm having the same problem you have mentioned in your question. Either no values or all values. Is this a working solution?What is ContentInfo here? Please help — Jas, Nov 05 '15 at 10:40
I changed the code and removed my data structure ContentInfo. You just need the string "selection". The code works fine, yes. I'm using it in my app. — Matthias, Nov 06 '15 at 10:30

Sqlite: select items based on distance (latitude and longitude)

1 Answers1