Android - SQlite Getting nearest locations (with latitude and longitude)

Hello, I don't know if I'm wierd, but I haven't found any topic about the subject. The thing is, I have data with lat and lng 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).

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 nothing yet, that is written in Java and is functioning.

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 DB. That would mean updating the DB everytime 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 :)

Thanks!

Btw: found this alternative: http://stackoverflow.com/questions/3126830/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?

Thanks, your post led me to this: http://code.google.com/p/javageomodel/source/browse/trunk/geocell/test/com/beoui/utils/HowToUseGeocell.java?r=3 But i can't quite figure out how i can query it in the sqlite database. As I said, I need a quite accurate distance from the given location to the locations in the database and the nearest locations ordered by distance. I can calculate the distance with Locations distanceTo() method, but that's outside of the DB and therefore slower.

Jure 2010-09-13 10:30:11

You never mentioned how many data points you are working with, or the use case for that matter. If you really need a list of all of n points in accurate distance order you may want to consider a server side option or if possible just hold your data in memory and apply distance calc for each. For mobile you are on a 'limited' resource machine where a typical use case is search for nearby things, i.e. use a geohash to reduce the result set, and then sort out the stuff in the result set to display to the user. For the geocell/geohash use the hexstring as your index for each POI.

Morrison Chang 2010-09-13 16:43:34

I have about 400 data points. Server side querying isn't really an option, because I want the app to be usable even if it's not online (Mobile network internet isn't free and I don't think users would be glad to have to be online everytime they would want to use the app). As I mentioned, I'm using a content provider to access the DB and it would be redundant to have another "provider" that's different from the DB content provider.

Jure 2010-09-13 20:14:31

Most smartphone users will have data connection, but I get the concern. 400 points isn't that large unless you need to get the distance from the current position for each. Back to your original question, I think the 'magic' is that most apps don't need all 400 points so they use a geohash/geocell to get only the closest points (say 1 km) and then sort and display the results. If you need all 400 points in order, I think you'll need to either create a custom cursor for your content provider which returns POI in order or have other code sort it once the cursor is available.

Morrison Chang 2010-09-13 21:42:56

I had also mentioned putting all POI in a cache (a service maybe?) as another option to avoid the db overhead or to get the performance you require.

Morrison Chang 2010-09-13 21:43:37

Thanks, as you suggested I will probably use geocell to get the closest points in the user defined filter (1km, 5km, ...) and then only calculate the distances to those points (maybe 20 or 30 POI). I'll probably need a custom cursor anyway for the sorting after I get data from the DB.

Jure 2010-09-14 07:29:27

ansaurus

tags:

views:

answers:

Android - SQlite Getting nearest locations (with latitude and longitude)

related questions