I have data with latitude and longitude stored in my SQLite database, and I want to get the nearest locations to the parameters I put in (ex. My current location – lat/lng, etc.).
I know that this is possible in MySQL, and I've done quite some research that SQLite needs a custom external function for the Haversine formula (calculating distance on a sphere), but I haven't found anything that is written in Java and works.
Also, if I want to add custom functions, I need the org.sqlite
.jar (for org.sqlite.Function
), and that adds unnecessary size to the app.
The other side of this is, I need the Order by function from SQL, because displaying the distance alone isn't that much of a problem – I already did it in my custom SimpleCursorAdapter, but I can't sort the data, because I don't have the distance column in my database. That would mean updating the database every time the location changes and that's a waste of battery and performance. So if someone has any idea on sorting the cursor with a column that's not in the database, I'd be grateful too!
I know there are tons of Android apps out there that use this function, but can someone please explain the magic.
By the way, I found this alternative: Query to get records based on Radius in SQLite?
It's suggesting to make 4 new columns for cos and sin values of lat and lng, but is there any other, not so redundant way?
Best Answer
1) At first filter your SQLite data with a good approximation and decrease amount of data that you need to evaluate in your java code. Use the following procedure for this purpose:
To have a deterministic threshold and more accurate filter on data, It is better to calculate 4 locations that are in
radius
meter of the north, west, east and south of your central point in your java code and then check easily by less than and more than SQL operators (>, <) to determine if your points in database are in that rectangle or not.The method
calculateDerivedPosition(...)
calculates those points for you (p1, p2, p3, p4 in picture).And now create your query:
COL_X
is the name of the column in the database that stores latitude values andCOL_Y
is for longitude.So you have some data that are near your central point with a good approximation.
2) Now you can loop on these filtered data and determine if they are really near your point (in the circle) or not using the following methods:
Enjoy!
I used and customized this reference and completed it.