Geek Answers Handbook

Quick search for nearest values ​​in SQL

I have long / lat coordinates with height in a SQLite database.

Some sample data:

latitude longitude altitude ------------------------------ 48.003333 11.0 652 48.036667 11.000833 651 48.280833 11.000833 533

Now I want some SQL query that will give me the maximum height for the given latitude / longitude coordinates, e.g.

Provided that long / long will be 48.023445 / 11.000733 , so the nearest height is 651 with wide / long 48.036667 / 11.000833 . Tried to search many forums, but nothing useful. Examples of a single variable or very slow queries are given everywhere. I need this query very quickly, and we saw some solution based on UNIONS. I need this fast because I will make about 1.5 million queries on this table. I use transactions and indexing.

+4
source share
3 answers

I would go with something similar to the following query:

 select * from pos order by ((lat-48.00333)*(lat-48.00333)+(long-11.0)*(long-11.0)+(alt-652)*(alt-652));

SQLite does not support SQRT, but since SQRT is monotonous, you can skip it in the distance formula.

+1
source

For your goal (a point close to finding coordinates), you can also minimize the use of the formula for distances on the plane.

 (lat-latitude)*(lat-latitude) + (long-longitude)*(long-longitude)
+4
source

The formula for the distance between two points on the sphere, using the coordinates of longitude and latitude, is far from simple, and is described here. As indicated on the page, if the points are close enough, you can get away with simple planar trigonometry and just use the Euclidean distance between the points:

+3
source

More articles:


All Articles