Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Ordering with SQLITE by nearest latitude & longitude coordinates

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

like image 785
NullPointerException Avatar asked Oct 01 '12 11:10

NullPointerException


2 Answers

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.

like image 136
Darkwater Avatar answered Nov 12 '22 21:11

Darkwater


The answer from Darkwater is nearly correct. To be correct, you need to use the square of the differences. As the square function is not available on SqLite, you need to multiply the differences by themselves. No need to calculate the square roots.

SELECT * AS distance FROM items ORDER BY ((location_lat-lat)*(location_lat-lat)) + ((location_lng - lng)*(location_lng - lng)) ASC
like image 37
chris Avatar answered Nov 12 '22 22:11

chris