Select coordinates which fall within a radius of a central point?

Question

I have a database of coordinates in the schema:

ID:Latitude:Longitude:name:desc

I've set up my google maps application to show the markers effectively on the screen. However I need to add another feature whereby the user can view all pointers that fall within the radius from a central point.

How would I write up a sql statement of the kind:

Select all pointers that fall within a 10 mile radius of X & Y

Lyonnesse

Answer 1

The SQL below should work:

SELECT * FROM Table1 a 
WHERE (
          acos(sin(a.Latitude * 0.0175) * sin(YOUR_LATITUDE_X * 0.0175) 
               + cos(a.Latitude * 0.0175) * cos(YOUR_LATITUDE_X * 0.0175) *    
                 cos((YOUR_LONGITUDE_Y * 0.0175) - (a.Longitude * 0.0175))
              ) * 3959 <= YOUR_RADIUS_INMILES
      )

This is based on the spherical law of cosines, for more detailed information on the topic, check out this article - http://www.movable-type.co.uk/scripts/latlong.html

Navigable

Latitude is Y, Longitude is X. Which means which in this instance? – Sorel

This worked great! For radius in Km instead of Miles, replace 3959 with 6371 and enter your radius in km. – City

HA!, nice dude :D – Vacillate

Answer 2

You probably need to do this in two steps. Select the points that lie within a 20 mile square with it's centre at X,Y. Assuming you calculate the top,left and bottom,right coordinates of the square first you can get all the points inside the square from the database with:

select * from coordinates where longitude < right and longitude > left and 
latitude < top and latitude > bottom;

The second step is to see whether the set of points is inside the 10 mile radius circle. At this point I would be tempted to use Google maps to calculate the distance between the points and the centre of your square using the google.maps.geometry.spherical.computeDistanceBetween(from:LatLng, to:LatLng, radius?:number)function. Check the answer is less than 10 miles. This function uses the radius of the earth as a default.

Dmso

This would work (unless you're far too in the north or the south, near the poles - where your square would look more like a trapezium (trapezoid) or even a triangle). – Predominance

Answer 3

This SQL gives more accurate answer:

SELECT *
 FROM Table1 a
 WHERE 1 = 1
 AND 2 * 3961 * asin(sqrt( power((sin(radians((X - cast(a.latitude as decimal(10,8))) / 2))) , 2) + cast(cos(radians(cast(a.latitude as decimal(18,8)))) * cos(radians(X)) * power((sin(radians((Y - cast(a.long as decimal(18,8))) / 2))) , 2) as decimal(18,10) ))) <= Radius_In_Miles

X = Latitude of Centroid Y = Longitude of Centroid

I did it in Redshift, so I had to use cast to prevent numeric value overflow error.

Reference: http://daynebatten.com/2015/09/latitude-longitude-distance-sql/

Reyna