mardi 21 juillet 2015

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

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);
    }
}

Aucun commentaire:

Enregistrer un commentaire