CODEWITHSUNDEEP
androidsqlite
tyczj
tyczj

Reputation: 73753

Get all items around a certain point from SQLite Database

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?

Upvotes: 3

Views: 2473

Answers (3)

Ted Hopp
Ted Hopp

Reputation: 234807

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".

Upvotes: 2

Doug Currie
Doug Currie

Reputation: 41180

You can also use the SQLite R*Tree extension.

An R-Tree is a special index that is designed for doing range queries. R-Trees are most commonly used in geospatial systems where each entry is a rectangle with minimum and maximum X and Y coordinates.

The source code to the SQLite R*Tree module is included as part of the SQLite3 amalgamation but is disabled by default. To enable the R*Tree module, simply compile with the SQLITE_ENABLE_RTREE C-preprocessor macro defined.

Upvotes: 2

Sam
Sam

Reputation: 86948

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);

Upvotes: 2

Related Questions