Android and distance between two point using sqlite

Question

I am looking for good solution to calculate distance between two points.

I want to sort by distance my Geo points.

I have:

Cursor c = database.rawQuery("SELECT name,latitude,longitude,((longitude-21.15)*(longitude-21.15) +(latitude-54.05)*(latitude-54.05)) AS len FROM Object ORDER BY len;", null);

But this is wrong solution... Distance is wrong...

Please help me.

Take a look at this question: http://stackoverflow.com/questions/3126830/query-to-get-records-based-on-radius-in-sqlite/9536914#9536914 — rciovati, Mar 18 '13 at 10:49

score 3 · Answer 1 · answered Dec 08 '13 at 08:16

The problem you have here is that you're combining:

GPS coordinates (WGS84) that are used to represent a point on a sphere.
Pythagoras theorem, requires a 2D rectangular coordinate system, to determine distance.

This will simply not work if you're after accuracy!

Here are some options that I've used.

Option 1 - Use great circle distance

This formula is used to calculate the distance in meters between two GPS co-ordinates

Android provides a function distanceBetween as part of the android.location.Location class to assist with this.

The downside is you have to pull out all your points from SQLite in order to calculate the distance.

I have also seen some hybrids such as:

treating WGS84 (GPS) as rectangular
specifying a zones in your db to limit the size of data you pull out when calculating distance.

and then using the distanceBetween function to further filter the results.

Option 2 - Use a rectangular coordinate system

If your distances are small then you can solve this problem is by storing the database points in UTM which is rectangular.

Using a formula almost identical, but using easting and northings, the SQLite db can be used to provide the x closest points to a UTM point. Once these points are identified the square-root function can be performed on the x results to get the distance in meters.

Huh?

There are some caveats about using UTM also. It might help you looking at Calculating Distance Between Two Sets of Coordinates

So your solution ultimately depends on your application and is a compromise between speed and accuracy.

score 0 · Answer 2 · answered Mar 18 '13 at 10:36

0

You are calculating the square of the distance. Since SQLite doesn't have a square-root function, you will need to calculate this in your Java code.

If all you need is to sort by distance, then your current code is fine.

answered Mar 18 '13 at 10:36

Graham Borland

60,055
21
138
179

The problem is that they are geo points. When counting in the code distance, sort is bad. – Unmerciful Mar 18 '13 at 10:39
If you need to calculate geodesic distance, you have to do it in Java. Or try that (I've never tested it so good luck): https://code.google.com/p/spatialite-android/ – Benoit Mar 18 '13 at 10:53

score -1 · Answer 3 · answered Mar 18 '13 at 11:32

-1

You can use static method distanceBetween of android.location.Location class. Then based on calculated distance you can produce some sorted collection of object like SortedMap.

answered Mar 18 '13 at 11:32

slkorolev

5,883
1
29
32

Android and distance between two point using sqlite

3 Answers3