Get all items around a certain point from SQLite Database

Question

I have a database full of map Locations (Latitude, Longitude), is there a way I could do a SQL query to return me all points around a certain location?

Right now I basically grab everything in the database and loop through each one and check the distance using

Location.distanceBetween(startPoint.latitude, startPoint.longitude,endPoint.latitude,endPoint.longitude, results);

and keeping items that are within the set distance but there could be a lot of points to loop through.

So is there anyway to do this in an SQL statement?

Answer 1

You can use a WHERE clause expression to filter on angular distance r from an origin (x0, y0). Since SQLite doesn't have a square root function, you'll have to use the squared distance:

SELECT ... FROM ...
    WHERE (Latitude-x0)*(Latitude-x0) + (Longitude-y0)*(Longitude-y0) < r*r;

The only place this won't work well is near the poles or the prime meridian. It's also a planar approximation to the sphere, so it will only work for values of r that are quite small. Finally, it scales latitude and longitude equally, so the selected region looks more and more elliptical the farther away the origin is from the equator.

You will have to convert linear distance (e.g., "within 30 meters") to latitude/longitude differences. This is a rather complex subject because the Earth is not a perfect sphere. However, for rough calculations you can use the approximation that 1 nautical mile = 1852 meters = 1 arc minute of longitude at the equator. Since lines of longitude get closer together as the latitude moves away from the equator, you will need to use some trig to figure out what value of r to use at a given latitude. For more info on this problem see this thread or this one, or search the web for "convert meters to latitude longitude".

Answer 2

I have a database full of map Locations (Latitude, Longitude), is there a way I could do a SQL query to return me all points around a certain location?

You can easily check a square region.

Using very simplified latitude / longitude coordinates say you're at [25.86, 57.03] and you wanted everything in the "neighborhood" (+/- .05) you can use a query like this:

SELECT * FROM Coords WHERE (latitude BETWEEN 25.81 AND 25.91) AND (longitude BETWEEN 56.98 AND 57.08);