sqlite的:根据距离选择项目(经度和纬度) [英] Sqlite: select items based on distance (latitude and longitude)
问题描述
我在至极项的表格使用的是基于纬度和经度的地理位置存储。有一次,我要检索只从我的当前位置的特定范围(距离或半径)数据库的项目。
项目(纬度,经度)
块引用>于是我跟着这真的<一个href=\"http://stackoverflow.com/questions/3126830/query-to-get-records-based-on-radius-in-sqlite\">nice帖子并试图实现与得票最高的答案。第一件事:我有存储项目时多补充一些列。这使得select语句后面:
项目(纬度,lat_cos,lat_sin,LNG,lng_cos,lng_sin)
块引用>到目前为止好,但是当我再尝试构建查询,我发疯。如果我使用较少的符号的&LT; 的那么所有的项目检索(即使太远)。但是,如果使用更大的符号的> 的任何项目检索。但我知道,有我的一系列项目。
问:所以我在做什么错词
WHERE(coslat * LAT_COS *(* LNG_COS + coslng * LNG_SIN sinlng))+ sinlat * LAT_SIN> cos_allowed_distance
块引用>这里的code,我使用了我的查询的ContentProvider。该ContentInfo类有中将sortOrder,选择字段和selectionArgs两个:
公共静态ContentInfo buildDistanceWhereClause(双纬度,经度双,双distanceInKilometers){ 最终双coslat = Math.cos(Math.toRadians(纬度));
最终双sinlat = Math.sin(Math.toRadians(纬度));
最终双coslng = Math.cos(Math.toRadians(经度));
最终双sinlng = Math.sin(Math.toRadians(经度)); //(coslat * LAT_COS *(LNG_COS * coslng + LNG_SIN * sinlng))+ sinlat * LAT_SIN&GT; cos_allowed_distance
最终的字符串,其中=+ *&GT(*(* + *)??????);??
最终的String [] = whereClause新的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))
}; 返回新ContentInfo(空,其中,whereClause);
}
}
解决方案我没有真正看到问题,但我重新设计了选择/在那里,以便更容易阅读。现在,它的工作原理。你也可以结帐这个小提琴。
公共静态字符串buildDistanceWhereClause(双纬度,经度双,双distanceInKilometers){ //看到:http://stackoverflow.com/questions/3126830/query-to-get-records-based-on-radius-in-sqlite 最终双coslat = Math.cos(Math.toRadians(纬度));
最终双sinlat = Math.sin(Math.toRadians(纬度));
最终双coslng = Math.cos(Math.toRadians(经度));
最终双sinlng = Math.sin(Math.toRadians(经度)); 最后的字符串格式=(%1 $ S *%2 $ S *(%3 $ S *%$ 4 S +%5 $ S *%$ 6 S)+%7 $ S *%$ 8 S)&GT; %9 $ S;
最后弦乐选择=的String.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,选择);
返回选择;
}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); } }
解决方案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; }
这篇关于sqlite的:根据距离选择项目(经度和纬度)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!