如何获取一组内的特定范围内的位置，从用户的位置，距离SQLite数据库 [英] How to retrieve a set of locations within particular range from user's location from SQLite database

问题描述

我有一些位置坐标存储在我的的SQLite 数据库表。我想要从使用当前的位置在1公里范围内的位置。现在我从读取数据库中的所有值，并写了我的范围内检索值的方法。这使我很大的开销，因为我的表可能包含1000个以上的坐标。

所以，我正在寻找一种更好的方式来做到这一点。是否有可能直接检索直接使用SELECT查询我的范围之内的位置？ 我发现<一href="http://stackoverflow.com/questions/27616498/android-how-to-get-those-locations-from-my-database-what-are-within-a-given-di">question 1 和<一href="http://stackoverflow.com/questions/3695224/sqlite-getting-nearest-locations-with-latitude-and-longitude">question 2 但无法找到一个可能的workaround.Any帮助将大大AP preciated。

这是我的选择查询现在：

 字符串selectQuery =SELECT+ COLUMN_OBJECTID +，+ COLUMN_OBJECTNAME_ENGLISH +，
                      + COLUMN_OBJECTNAME_ARABIC +，+ COLUMN_OBJECTLATITUDE +，+ COLUMN_OBJECTLONGITUDE +，
                              + COLUMN_OBJECTCATEGORYID +，+ COLUMN_OBJECTADDRESS_ENGLISH +，+ COLUMN_OBJECTADDRESS_ARABIC
                              +FROM+ TABLE_OBJECTLIST +WHERE+ COLUMN_OBJECTCATEGORYID +=+的categoryId +和+
                              ((userLat-Double.parseDouble(COLUMN_OBJECTLATITUDE))*(userLat-Double.parseDouble(COLUMN_OBJECTLATITUDE))
                                      +(userLong-Double.parseDouble(COLUMN_OBJECTLONGITUDE))*(userLong-Double.parseDouble(COLUMN_OBJECTLONGITUDE))<=range);
 

解决方案

您可以编写一个SQL查询具有下列条件的地方：

 其中，（（my_lat  -  LAT）*（my_lat  -  LAT）+（my_lon  -  LON）*（my_lon  -  LON））＆LT; = 1KM
 

的思想是，利用毕达哥拉斯方法来计算基于该大约位置和过滤器。 在这里，我没有采取平方根，因为我想SQL函数SQLite中没有开方。

这是很好的近似计算...

我用下面的SQL和它的工作...

  //表列，字符串和浮动
CREATE TABLE位置（IDINTEGER PRIMARY KEY AUTOINCREMENT NOT NULL，lat_stringVARCHAR，long_stringVARCHAR，lat_val浮动，long_valFLOAT）

//插入示例数据
INSERT INTO位置VALUES（1，'12 0.9587926'，'77.7477416'，12.9587926,77.7477416）;
INSERT INTO位置VALUES（2，'12 0.9973486'，'77.6967362'，12.9973486,77.69673619999999）;
INSERT INTO位置VALUES（3，'12 0.9715987'，'77.5945627'，12.9715987,77.5945627）;
INSERT INTO位置VALUES（4，'12 0.9629354'，'77.7122996'，12.9629354,77.7122996）;

//选择当列格式字符串。这在源码
SELECT ID，（（77.7580827  -  long_string）*（77.7580827  -  long_string）+（12.9905542  -  lat_string）*（12.9905542  -  lat_string））作为DIST从位置

//选择时，列格式是浮动。这在源码
SELECT ID，（（77.7580827  -  long_val）*（77.7580827  -  long_val）+（12.9905542  -  lat_val）*（12.9905542  -  lat_val））作为DIST从位置
 

I have some location coordinates stored in my SQLite database table. I want to retrieve the locations within 1 km range from the use's current location. Right now I am fetching all values from database and have written a method to retrieve the values within my range. This leaves me great overhead as my table might contain more than 1000 coordinates.

So i am looking for a better way to do this. Is it possible to directly retrieve locations within my range directly using SELECT query? I found question 1 and question 2 but couldn't find a possible workaround.Any help will be greatly appreciated.

This is my SELECT Query now :

String selectQuery = "SELECT  "+COLUMN_OBJECTID+","+COLUMN_OBJECTNAME_ENGLISH+"," 
                      +COLUMN_OBJECTNAME_ARABIC+","+COLUMN_OBJECTLATITUDE+","+COLUMN_OBJECTLONGITUDE+"," 
                              +COLUMN_OBJECTCATEGORYID+","+COLUMN_OBJECTADDRESS_ENGLISH+","+COLUMN_OBJECTADDRESS_ARABIC 
                              +" FROM " + TABLE_OBJECTLIST+" WHERE "+COLUMN_OBJECTCATEGORYID+"='"+categoryId+"' AND "+ 
                              ((userLat-Double.parseDouble(COLUMN_OBJECTLATITUDE))*(userLat-Double.parseDouble(COLUMN_OBJECTLATITUDE)) 
                                      +(userLong-Double.parseDouble(COLUMN_OBJECTLONGITUDE))*(userLong-Double.parseDouble(COLUMN_OBJECTLONGITUDE))<=range);

解决方案

You can write a SQL query with following where condition :

where ( (my_lat - LAT)*(my_lat - LAT) + (my_lon - LON)*(my_lon - LON) ) <= 1KM

The idea is that use Pythagoras method to calculate approx location and filter based on that. Here I have not taken the square root because I guess SQL functions in SQLite don't have sqrt.

This is good for approximate calculations ...

I used following SQL and it worked ...

// Table with columns as String and Float
CREATE TABLE "locations" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "lat_string" VARCHAR, "long_string" VARCHAR, "lat_val" FLOAT, "long_val" FLOAT)

// Insert example data
INSERT INTO "locations" VALUES(1,'12.9587926','77.7477416',12.9587926,77.7477416);
INSERT INTO "locations" VALUES(2,'12.9973486','77.6967362',12.9973486,77.69673619999999);
INSERT INTO "locations" VALUES(3,'12.9715987','77.5945627',12.9715987,77.5945627);
INSERT INTO "locations" VALUES(4,'12.9629354','77.7122996',12.9629354,77.7122996);

// Select when column format is string. This works in SQLIte
SELECT id, ( (77.7580827 - long_string)*(77.7580827 - long_string) + (12.9905542 - lat_string)*(12.9905542 - lat_string) ) as dist FROM locations

// Select when column format is float. This works in SQLIte
SELECT id, ( (77.7580827 - long_val)*(77.7580827 - long_val) + (12.9905542 - lat_val)*(12.9905542 - lat_val) ) as dist FROM locations

这篇关于如何获取一组内的特定范围内的位置，从用户的位置，距离SQLite数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！