Android sqlite sort on calculated column (co-ordinates distance)

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.

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

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:

• 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

yeah, that works perfectly.

it can be made into a stored procedure like so:

http://www.thismuchiknow.co.uk/?p=71 [Distance function for sqlite]

there's also the Perst spatial database for android which is excellent, and the SpatiaLite spatial database which is also awesome, which you could link to in your app.

w/out using a specialized lib, you could approximate the distance a few ways (calculate it just as if it were planar (rectangular) then use the Haversine formula to sort the subset later, use a lookup table that approximates cos, sin, etc, group locations into 5 sq. mile zones and search neighboring cells up to the max, etc...)

In my app BostonBusMap I used an approximation to speed up the calculation of closest objects to a point. You can scale the longitude by cos(latitude) and then just use the Pythagorean formula to calculate a sorting distance (omitting the square route since it's not necessary for a comparison distance). It works reasonably well for small distances.

Source: http://en.wikipedia.org/wiki/Geographical_distance#Spherical_Earth_projected_to_a_plane