Java – Ordering with SQLITE by nearest latitude & longitude coordinates

androidjavalatitude-longitudesqlite

I must obtain a SQLite SQL Sentence for ordering by nearest latitude and longitude coordinates, given a initial location.

THis is a example sentence for my table in the sqlite database:

SELECT id, name, lat, lng FROM items

EXAMPLE RESULT: 1, Museu, 41375310.0, 2175970.0

I must achieve this with SQLite, and with that table. I can't use another techniques because this is for a existen SQlite database that i can't change.

Exists a way to achieve this with Android and SQlite? I checked a lot of stackoverflow posts and i didn't find the way to achieve that

Thanks

Best Answer

SELECT * AS distance FROM items ORDER BY ABS(location_lat - lat) + ABS(location_lng - lng) ASC

This should roughly sort the items on distance in MySQL, and should work in SQLite.
If you need to sort them preciser, you could try using the Pythagorean theorem (a^2 + b^2 = c^2) to get the exact distance.

Related Solutions

Calculate distance between two latitude-longitude points? (Haversine formula)

This link might be helpful to you, as it details the use of the Haversine formula to calculate the distance.

Excerpt:

This script [in Javascript] calculates great-circle distances between the two points – that is, the shortest distance over the earth’s surface – using the ‘Haversine’ formula.

function getDistanceFromLatLonInKm(lat1,lon1,lat2,lon2) {
  var R = 6371; // Radius of the earth in km
  var dLat = deg2rad(lat2-lat1);  // deg2rad below
  var dLon = deg2rad(lon2-lon1); 
  var a = 
    Math.sin(dLat/2) * Math.sin(dLat/2) +
    Math.cos(deg2rad(lat1)) * Math.cos(deg2rad(lat2)) * 
    Math.sin(dLon/2) * Math.sin(dLon/2)
    ; 
  var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a)); 
  var d = R * c; // Distance in km
  return d;
}

function deg2rad(deg) {
  return deg * (Math.PI/180)
}

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

There are a few steps to see the tables in an SQLite database:

List the tables in your database:
```
.tables
```
List how the table looks:
```
.schema tablename
```
Print the entire table:
```
SELECT * FROM tablename;
```
List all of the available SQLite prompt commands:
```
.help
```

Best Answer

Related Solutions

Calculate distance between two latitude-longitude points? (Haversine formula)

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

Related Topic