CODEWITHSUNDEEP
mysqlsqlperformancesqlite
MartinS
MartinS

Reputation: 751

Fast search of nearest values in SQL

I have got long/lat coordinates with altitude in SQLite database.

Some example 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 which will give me closest altitude to given latitude/longitude coordinates so for example

Given lat/long will be 48.023445/11.000733 so the closest is altitude 651 with lat/long 48.036667/11.000833. Tried to search a lot of forums but nothing usefull. Everywhere are examples on one variable or very slow queries. I need this query really fast and saw some solution based on UNIONS. I need it fast because i will make about 1,5 milions of queries on this table. I am using transactions and indexing.

Upvotes: 4

Views: 738

Answers (3)

Daniel Voina
Daniel Voina

Reputation: 3215

I would go with something similar 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 as SQRT is monotonous you can skip it in the distance formula.

Upvotes: 1

kmkaplan
kmkaplan

Reputation: 18960

For your purpose (point close to searched for coordinates) you might as well minimize using the formula for distances on a plane.

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

Upvotes: 4

SWeko
SWeko

Reputation: 30902

The formula for distance between two points on a sphere, using longitude and latitude coordinates is far from simple, and is described here. As specified in the page, if the points are reasonably close, you could get away with simple planar trigonometry, and just use the Euclid distance between the points:

Upvotes: 3

Related Questions