Identifying closest Lon and Lat based on iphone position
Hi All
I know this is a big oneand have read many threads but I'm struggling here.
I have looked at the following formulas at:
http://www.movable-type.co.uk/scripts/latlong.html
I need to query the sqlite database and find the closes record to the phone. Anyone successfully managed this? if so that's a good thing. Code example would be nice but I'm happy to carry on working on it if i know SQLlite can do that sort of search.
Many thanks
Mark
4 Answers
-
This article from google describes two methods of querying nearby points:
Point/distance query:
SELECT id, ( 3959 * acos( cos( radians(lat_t) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(lng_t) ) + sin( radians(lat_t) ) * sin( radians( lat ) ) ) ) AS distance FROM Stores HAVING distance < 25 ORDER BY distance
Bounding box query:
SELECT * FROM Stores WHERE lat > (lat_t - d) AND lat < (lat_t + d) AND lng > (lng_t - d) AND lng < (lng_t + d)
-
Hi Carmen
Thank you so much! this is a great start. One question would be can this search be performed on a SQLite database? I just know and have read issue with non Spatial databases. Although looking briefly not sure?
Just about to fly to Istanbul, but would rather get my hands dirty with the above code!
Many thanks again.
-
Hi Carmen
Thank you so much! this is a great start. One question would be can this search be performed on a SQLite database? I just know and have read issue with non Spatial databases. Although looking briefly not sure?
Just about to fly to Istanbul, but would rather get my hands dirty with the above code!
Many thanks again.
-
No, there are no trigonometric functions (sin, cos, atan etc.) in SQlite. You will have to do this in an array.
But you could pre-select entries that are in a certain range (eg. within 50km lat and lon) in SQlite and then alculate the distance for the given results.
Chris