4

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.

fthiella
  • 48,073
  • 15
  • 90
  • 106
MartinS
  • 751
  • 3
  • 12
  • 27
  • What database are you using? Postgresql? – piotrekkr Dec 31 '12 at 08:35
  • 3
    Maby this will help http://stackoverflow.com/questions/1006654/fastest-distance-lookup-given-latitude-longitude or maby using postgresql + PostGIS (http://postgis.org/) – piotrekkr Dec 31 '12 at 08:42
  • @MartinŠevic Are your queries are all for contiguous point in a rectangular area? In which case you should add more context to your question. – kmkaplan Dec 31 '12 at 09:35
  • OK, i have a picture of specified height and width. Each pixel has it's own gps coordinates and all i want to do is assign altitude to everz pixel. – MartinS Dec 31 '12 at 11:12

3 Answers3

4

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)
kmkaplan
  • 18,655
  • 4
  • 51
  • 65
3

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:

SWeko
  • 30,434
  • 10
  • 71
  • 106
1

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.

Daniel Voina
  • 3,185
  • 1
  • 27
  • 32
  • But i don´t want to count distance i just want to asign altitude to each pixel on my map. Every pixel has it's own coordinates so i want only to search closest row where long=xx.xxxxx and lat=yy.yyyyyy – MartinS Dec 31 '12 at 09:22
  • 1
    As far as I understand you ae trying to do something similar to: foreach y foreach x find closest altitude to point(x,y) Is this right? In this case the scan is O(n^2) hence this kind of query is quite inefficient. – Daniel Voina Dec 31 '12 at 09:30
  • 1
    In this case I would implement some caching at application level. I would fetch in memory a window of 500*500 points and the search the altitudes for your point in this data. Hence you will fetch larger result sets in memory and reduce the queries on DB.In case of ache miss you will have to fetch another windowbut the penalty would be lower. lookat the 2nd answer of this question stackoverflow.com/questions/1006654/… (url via pitrekkr)for some implementation ideas that do not imply spatial indexes – Daniel Voina Dec 31 '12 at 12:05
  • I know it's a little bit late but you helped me. I think you should know it. I made a simple cache where all points readed for current picture from database where inserted one by one to csv file. Also i made an algo on app level which counted exact row for each point i have inserted (there was an order in database between each record). So first load for file 1800*900 pixels last about one minute. Any other load on this map last about 2-3 seconds because of cache. It works great because user change this file rarely. – MartinS Sep 01 '15 at 09:21