3

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?

tyczj
  • 71,600
  • 54
  • 194
  • 296
  • What's the table structure? Is the "certain location" a given value or the value of one of the rows in the table? – Ted Hopp Apr 19 '13 at 17:25
  • @TedHopp the certain location is some point on the map ie. just a latitude longitude. basically user presses on the map to get the lat/lng – tyczj Apr 19 '13 at 17:29

3 Answers3

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);
Sam
  • 86,580
  • 20
  • 181
  • 179
  • Square region wont work though because I have a setting that the user sets what they want the radius (in meters) to be and that is what I check against. Unless I can add that distance to the selected position somehow – tyczj Apr 19 '13 at 17:33
2

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

Community
  • 1
  • 1
Ted Hopp
  • 232,168
  • 48
  • 399
  • 521
  • can u please elaborate this? – Jas Nov 03 '15 at 05:59
  • @Jas - What part of this do you want elaborated? – Ted Hopp Nov 03 '15 at 06:26
  • I have posted my problem as a question.Could you please check this http://stackoverflow.com/questions/33492505/how-to-retrieve-a-set-of-locations-within-particular-range-from-users-location ? – Jas Nov 03 '15 at 06:27
2

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.

Doug Currie
  • 40,708
  • 1
  • 95
  • 119