Databases
StackOverflow2013
Postsdbo
POAndroid sqlite sort on calculated column (co-ordinates distance)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAndroid sqlite sort on calculated column (co-ordinates distance)
    text
    copied!<p>I am using an SQLITE database to store latitudes and longitudes of locations.</p> <p>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.</p> <p>What I want is a query that will create a virtual column that I am able to sort the results by.</p> <p>I currently use a function to show the distance for a selected record:</p> <p><code> float pk = (float) (180/3.14159);<br> float a1 = (float) (db_lat / pk);<br> float a2 = (float) (db_lon / pk);<br> float b1 = (float) (current_lat / pk);<br> float b2 = (float) (current_lon / pk);<br> float t1 = FloatMath.cos(a1)*FloatMath.cos(a2)*FloatMath.cos(b1)*FloatMath.cos(b2);<br> float t2 = FloatMath.cos(a1)*FloatMath.sin(a2)*FloatMath.cos(b1)*FloatMath.sin(b2);<br> float t3 = FloatMath.sin(a1)*FloatMath.sin(b1);<br> double tt = Math.acos(t1 + t2 + t3);<br> double dist = (6366000*tt); </code></p> <p>For example, a MySQL select could be (taken from: www.movable-type.co.uk):</p> <p><code> Select Lat, Lon, acos(sin($lat)*sin(radians(Lat)) + cos($lat)*cos(radians(Lat))<em>cos(radians(Lon)-$lon))</em>$R As dist From MyTable ORDER BY dist DESC </code></p> <p>Currently I select locations using the following:</p> <p><code> 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; } } </code></p> <p>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?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload