我使用的SQLite数据库来存储位置的经度和纬度。

I am using an SQLITE database to store latitudes and longitudes of locations.

我希望能够通过粗略距离的结果从当前位置进行排序。我已经有设备的当前位置在数据库中的双（纬度，经度）的纬度和经度也加倍。

I want to be able to sort the results by rough distance from the current location. I already have the current location of the device as a double (lat, lng), the lat and lng in the database are also doubles.

我要的是一个能够创建，我能够通过对结果进行排序的虚拟列的查询。

What I want is a query that will create a virtual column that I am able to sort the results by.

我目前使用的函数来显示的距离选定的记录：

I currently use a function to show the distance for a selected record:

浮PK =（浮点）（180 / 3.14159）; 浮A1 =（浮点）（db_lat /包）; 浮动A2 =（浮点）（db_lon /包）; 浮B1 =（浮点）（current_lat /包）; 浮B2 =（浮点）（current_lon /包）; 浮T1 = FloatMath.cos（A1）* FloatMath.cos（a2）的* FloatMath.cos（b1）中* FloatMath.cos（b2）中; 浮T2 = FloatMath.cos（A1）* FloatMath.sin（a2）的* FloatMath.cos（b1）中* FloatMath.sin（b2）中; 浮T3 = FloatMath.sin（A1）* FloatMath.sin（B1）; 双TT = Math.acos（T1 + T2 + T3）; 双DIST =（6366000 * TT）;

float pk = (float) (180/3.14159); float a1 = (float) (db_lat / pk); float a2 = (float) (db_lon / pk); float b1 = (float) (current_lat / pk); float b2 = (float) (current_lon / pk); float t1 = FloatMath.cos(a1)*FloatMath.cos(a2)*FloatMath.cos(b1)*FloatMath.cos(b2); float t2 = FloatMath.cos(a1)*FloatMath.sin(a2)*FloatMath.cos(b1)*FloatMath.sin(b2); float t3 = FloatMath.sin(a1)*FloatMath.sin(b1); double tt = Math.acos(t1 + t2 + t3); double dist = (6366000*tt);

例如，一个MySQL选择可（摘自：www.movable-type.co.uk）：

For example, a MySQL select could be (taken from: www.movable-type.co.uk):

选择纬度，经度，ACOS（SIN（$ LAT）*罪（弧度（LAT））+ COS（$ LAT）* COS（弧度（LAT））的 COS（弧度（经度） - $ LON）） $ R作为DIST 从MyTable的 ORDER BY DIST降序

Select Lat, Lon, acos(sin($lat)*sin(radians(Lat)) + cos($lat)*cos(radians(Lat))cos(radians(Lon)-$lon))$R As dist From MyTable ORDER BY dist DESC

目前我选择使用以下位置：

Currently I select locations using the following:

    公共光标locationGetAllRows（长组ID）     {         尝试         {             返回db.query（LOCATION_DATABASE_TABLE，新的String [] {               _id，纬度，经度，组ID}，               组ID =+组ID，NULL，NULL，NULL，NULL）;         }         赶上（的SQLException E）         {             Log.e（异常的询问：e.toString（））;             返回null;         }     }

public Cursor locationGetAllRows(long groupid) { try { return db.query(LOCATION_DATABASE_TABLE, new String[] { "_id", "lat","lon","groupid"}, "groupid="+groupid, null, null, null, null); } catch (SQLException e) { Log.e("Exception on query: ", e.toString()); return null; } }

行，所以是有可能使用SQLite数据库以这种方式？如果不是唯一的选择，我能想到的是有一个额外的列，遍历运行的每个行的上述功能，并填写一个额外的列行上，然后在该列排序行？

OK so is it possible to use the SQLITE database in this way? If not the only option I can think of is to have an extra column, iterate through the rows running the above function on each row and filling out an extra column on the row, then sorting on that column?

推荐答案

这将不完全的帮助，但是对于这样的情况下，认真考虑使用 rawQuery（）而不是对查询（），所以你可以传递一个完整的SQL语句主场迎战不必把它切成片。

This won't completely help, but for situations like this, seriously consider using rawQuery() instead of query(), so you can pass in a full SQL statement vs. having to chop it into pieces.

您更大的问题是，我没有看到，SQLite的有三角函数。

Your bigger problem is that I don't see that SQLite has trigonometric functions.

您不表明你如何使用光标您从您的查询找回。例如，如果你把光标成某种的CursorAdapter 中，您可以：

You do not indicate how you are using the Cursor you are getting back from your query. For example, if you are putting the Cursor into some sort of CursorAdapter, you could:

转换光标到的ArrayList＆LT;位置＆GT; ，其中位置是一些你和你的数据定义的Java类 关闭光标，以释放它占用的内存 排序的ArrayList＆LT;位置＆GT; 使用 Arrays.sort（） 敷的ArrayList＆LT;位置＆GT; 在 ArrayAdapter＆LT;位置＆GT; ，并使用您一直在使用，你的的CursorAdapter convert the Cursor into an ArrayList<Position>, where Position is some Java class you define with your data close the Cursor, to release the RAM it takes up sort the ArrayList<Position> using Arrays.sort() wrap the ArrayList<Position> in an ArrayAdapter<Position> and use that where you had been using your CursorAdapter