0

I know how to get object from my closest area:

db.execSQL("CREATE TABLE Task ("
                + "id smallint(6) NOT NULL,"
                + "project_id smallint(6) NOT NULL," 
                + "add_date varchar(15) NOT NULL,"
                + "geo_lat double NOT NULL,"
                + "geo_long double NOT NULL,"
                + "additional_info TEXT NULL" + ");");

and 

"SELECT * FROM Task WHERE (geo_lat BETWEEN " + lat_bot + " AND " + lat_top + ") AND (geo_long" + " BETWEEN " + long_bot + " AND " + long_top + ")"

but my question is how to Order them by the closest?

MattiahIT
  • 87
  • 9
  • there is no easy way to do it MYSQL, you will need to build the index by yourself! unless you change to use MongoDB, where every geolocation stored will be indexed! The easiest way you can do, but not scalable is to get the all the query and calculate the shortest distance! – Tim Jun 12 '14 at 07:36

1 Answers1

0

You should sort your objects by DISTANCE from your area. Below formula is complicated enough to be built on Application layer, NOT sql.

For your reference. Use the ‘haversine’ formula to calculate the great-circle distance between two points:

Haversine:   a = sin²(Δφ/2) + cos φ1 ⋅ cos φ2 ⋅ sin²(Δλ/2)
formula:     c = 2 ⋅ atan2( √a, √(1−a) )
             d = R ⋅ c

where φ is latitude, λ is longitude, R is earth’s radius (mean radius = 6,371km); note that angles need to be in radians to pass to trig functions!

Reference: Calculate distance, bearing and more between Latitude/Longitude points

MinhD
  • 1,790
  • 11
  • 14
  • I can get distance using Android/Java function 'distanceTO()' but in this way I have to use that function on every record from query and then sort them... is there no other way? – MattiahIT Jun 12 '14 at 08:32
  • As I said earlier, you should do that on Java. But you can also check this answer: http://stackoverflow.com/questions/13136746/how-can-i-query-in-mysql-tablewhere-lat-and-long-stored-that-which-are-the-nea?rq=1 – MinhD Jun 12 '14 at 08:41
  • Thanks that is usefull but there is no math functions in android SQLite... It should be done by NDK : [http://stackoverflow.com/questions/12279205/sqlite-android-math-functions] – MattiahIT Jun 12 '14 at 10:56