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

问题描述

我的 SQLite 数据库表中存储了一些位置坐标.我想检索距用户当前位置 1 公里范围内的位置.现在我正在从数据库中获取所有值，并编写了一个方法来检索我范围内的值.这给我留下了很大的开销，因为我的表可能包含 1000 多个坐标.

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.

所以我正在寻找一种更好的方法来做到这一点.是否可以直接使用 SELECT 查询直接检索我范围内的位置?我发现 问题 1 和 问题 2 但不能找不到可能的解决方法.任何帮助将不胜感激.

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.

这是我现在的 SELECT 查询:

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

推荐答案

你可以写一个带有以下 where 条件的 SQL 查询:

You can write a SQL query with following where condition :

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

这个想法是使用毕达哥拉斯方法来计算近似位置并在此基础上进行过滤.这里我没有取平方根，因为我猜 SQLite 中的 SQL 函数没有 sqrt.

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 ...

我使用了以下 SQL 并且它工作正常......

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屋！