CODEWITHSUNDEEP
androidsqlitedistance
Unmerciful
Unmerciful

Reputation: 1335

Android and distance between two point using sqlite

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.

Upvotes: 1

Views: 3208

Answers (3)

Allan
Allan

Reputation: 31

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.

Upvotes: 3

slkorolev
slkorolev

Reputation: 6001

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.

Upvotes: -1

Graham Borland
Graham Borland

Reputation: 60691

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.

Upvotes: 0

Related Questions