6

We are building an Android App that will use user's current location (lat, long) and show top 50 venues around the current location, sorted by distance.

We have these venues stored in an SQLite DB. We plan to ship with the sqlite DB with the app.

In order to fetch only the relevant top 50 closest venues, we want to define a db function DIST (to calculate distance between two points) and use it in our query.

How can I define a custom SQLite function for Android Apps? What will be the Java API call to do this?

We have successfully implemented this approach in our iPhone App - using Objective C.

Puneet
  • 61
  • 1
  • 1
  • 2
  • http://stackoverflow.com/questions/3126830/query-to-get-records-based-on-radius-in-sqlite – Pentium10 Jun 27 '10 at 09:10
  • hello how did you integrate that on ios ?? because i need to do the same thing , thanks –  Feb 11 '15 at 11:00

4 Answers4

5

Update: The answer is that you can not do this.

from: Android. Is it possible to write custom function on C/C++ and use it in SQL query?

Community
  • 1
  • 1
Joshua Smith
  • 6,561
  • 1
  • 30
  • 28
  • Josh, Thank you! If you don't mind, could you please point me to some sample code - I would then be sure not to make a mistake. Thanks a ton! – Puneet Feb 28 '10 at 20:03
  • 2
    org.sqlite is not an Android API, so that is not relevant. – hackbod Mar 01 '10 at 04:59
  • 1
    You are correct, org.sqlite is not an Android API. However, you can use it to implement a Content Provider that does what he asked. – Joshua Smith Mar 01 '10 at 12:39
  • Actually, I'm rather skeptical on the approach outlined in the content provider answer you link to. `org.sqlite` is not in Android, and it is unclear if there exists an `org.sqlite` implementation that will work in concert with Android's SQLite environment. – Pentium10 Jun 27 '10 at 09:12
  • can u describe the steps to follow. – shashwat Aug 18 '14 at 14:38
1

A workaround might be to fetch all records where one of the values (say, longitude) is close to the current location. If you use this column as an index then fetching these will be very fast.

Then, you have a much reduced subset to iterate over comparing the latitudes and doing your full distance calculations.

Graham Borland
  • 60,055
  • 21
  • 138
  • 179
0

I know this is an old question, but there is a little bit easier solution I think.

This is an example, but it fails when crossing 0 degrees, although you could shift all the latitude/longitudes to adjust.

The overall solution works pretty well for ordering, but just uses the Pythagorean theorem to calculate the relative distance (note: no square root function in sqlite, but that is not relevant).

 String calcDistance = "( ( (abs(" + cLat + ")  - " + Math.abs(lat) + ") * (abs(" + cLat + ")  - " + Math.abs(lat) + ") )" +
                    "+   ( (abs(" + cLng + ") - " + Math.abs(lng) + ") * (abs(" + cLng + ") - " + Math.abs(lng) + ") ) ) as calc_distance";

If you needed to fix this distance, you would want to adjust by adding 90 to all latitudes and 180 to all longitude. Then you could get rid of that nasty abs functions.

Chrispix
  • 17,941
  • 20
  • 62
  • 70
0

It can be done but you cannot load this into the database from the Java API. You will need to provide your own implementation of the sqlite library that is extended to provide the functions you wish at the C level. This is not fun but it works well. Be aware that you may have issues with Android devices using non-standard architectures.

andy.xyz
  • 2,567
  • 1
  • 13
  • 18