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