Order with SQLITE at the nearest latitude and longitude coordinates

Question

I have to get SQLite SQL Sentence to sort by the nearest latitude and longitude, given the starting location.

This is an example sentence for my table in sqlite database:

SELECT id, name, lat, lng FROM items EXAMPLE RESULT: 1, Museu, 41375310.0, 2175970.0 

I have to achieve this with SQLite and with this table. I cannot use other methods because it is for an existing SQlite database which I cannot change.

Is there a way to achieve this using Android and SQlite? I checked a lot of stackoverflow posts and I did not find a way to achieve this.

thanks

Answer 1

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

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

Answer 2

The answer from Darkwater is almost correct. To be true, you need to use the square of differences. Since the square function is not available in SqLite, you need to propagate the differences separately. No need to calculate square roots.

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

Answer 3

If you can upload records; convert them to locations and then use the distanceTo function I would recommend this, but ...

You can get closer to the distance between two points using simple SQL, and the various approaches are outlined here clearly . The further you compare the points you compare, then your values ​​may become more and more wrong if you use a simple calculation

If you figure these things out yourself and your locations can be anywhere, then you may need to know the values ​​flowing around if you are comparing locations around an international date line.

Answer 4

The solution proposed by Chris:

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

is correct when we are close to the equator. To work correctly in other latitudes, I suggest:

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

where we must pre-compose:

 cos_lat_2 = cos(location_lat) ^ 2 

PROBLEM:

If we are at the equator and we move one degree in longitude (east or west), we do this in a circle of 40,000 km, representing a distance of 40,000 / 360. If we move one degree in latitude (north or south), we do it in a circle intersecting both poles, which also includes a distance of 40,000 / 360 (given that the earth is a sphere).

But if we are in the south of England with a latitude of 50 °, and we move one degree in longitude (east or west), we do this on the 50th parallel, which has a smaller circle than the equator. The distance is perimeter_parallel_50 / 360. The calculation of this perimeter is simple: perimeter_parallel_50 = cos (50) * 2 * PI * EARHT_RADIUS = 0.64 * 40,000 km. This decrease in distance is not observed if we move one step south or north. The circle around which we move has a perimeter of 40,000 km.

DECISION:

Since location_lat is a value known in advance, we can pre-calculate the value of cos (location_lat) so that it can be used as a scaling factor, so the offset in longitude and latitude are equivalent. Moreover, we pre-square it so as not to double it.

Note:

This is still an approximation, and it will give incorrect results when moving long distances, especially near the poles and when crossing the 180th meridian.

Answer 5

If you know that 1 degree of latitude is about 111111 meters, and 1 degree of longitude is 111111 * cos (latitude) meters, then you can easily get all the places inside a certain square.

 SELECT * FROM items WHERE latitude BETWEEN %f AND %f AND longitude BETWEEN %f AND %f 

This query is very fast, even with millions of lines. But don't forget to create an index for latitude and longitude:

 CREATE INDEX position ON items (latitude, longitude) 

I use this in Objective-C to get all the interesting places that are within 3 km of the current location:

 double latDist = 1.0 / 111111.0 * 3.0; double lonDist = 1.0 / ABS(111111.0*cos(location.coordinate.latitude)) * 3.0; FMResultSet *results = [database executeQueryWithFormat:@"SELECT * FROM items WHERE latitude BETWEEN %f AND %f AND longitude BETWEEN %f AND %f", location.coordinate.latitude - latDist, location.coordinate.latitude + latDist, location.coordinate.longitude - lonDist, location.coordinate.longitude + lonDist]; 

Now you can calculate the exact distance and sort the results ...