How to get 10 nearest locations from the current location

Question

Hi all,

Im doing app in which, uses sqlite db file to get data. table in db having shop name, address,city, latitude, longitude etc columns.

Im able to get cureent location latitude and longitude and also able to calculate the distance between current location and the location of a shop(in db) using longitude and latitude values.

Here my issue is I need to get/display 10 shopnames(from db) which are nearest(distance) to the current location.

Please suggest me or give a reference link to solve this issue

Thank you

Answer 1

There are thousands upon thousands of sites that give you the formulae for computing the distance between two points. Most assume you want to take curvature of the Earth into account; without that, it is simple geometry (Pythagorean theorem). In psuedocode:

sqrt((x1-x2)^2 + (y1-y2)^2)

Construct a SQL query that embodies whichever formula you choose in its ORDER BY clause, taking into account the latitude and longitude of the fixed point you are searching for. Since you do not care about absolute distance, but rather simply the magnitude of the distance, you may be able to do some stuff to save CPU time, such as skipping the square root part of the Pythagorean theorem.

To limit the result set to 10, use LIMIT 10 in your query, and you will get the top 10 hits.

Answer 2

Note that if you have a lot of locations, SQLite is not going to be the ideal platform to do geographic queries. You will have to do a distance calculation for every row on every query, which will be CPU-intensive and slow.

You might be better off to locate a library that does geographic indexing and provide your data in a compatible format. (Try searching for "Java geographic index".) There was a geo extension for Lucene, but I don't know if it ever made it out of alpha.

Answer 3

Another way is to move the "what is nearest " code to an application server somewhere in the net. This way you don't do any cpu intensive stuff on the mobile device, it just requires an active inet connection.