SQL: Determine if one coordinate is in radius of another

Question

Lets say i have a Table of rows that contain coordinates. what would be the best way to pull only the rows of coordinates which are in the radius of a another coordinate?

To simplify my question, i'm giving the following example:

Table like:
Columns: Latitude, Longitude.
Row1:    23.44444  24.55555
Row2:    32.44444  28.22222
Row3:    35.11111  32.12345

in an sql statement, how do i get the rows of coordinates that are in the radius of Row3 for example.

Thanks in advance.

Answer 1

This post shows how to do this in SQL Server.

And here is how to do it in MySQL:

SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + 
         COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon - lon) * 
         PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance 
FROM members 
HAVING distance<='10' ORDER BY distance ASC

Answer 2

What do you mean by in the radius of? Do you want to pass in a distance, say 5 miles, and find all rows within 5 miles of row x?

Check this out if so http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx

Answer 3

looks like the distance formula is:

D = 60* 1.1515 * acos (sin(pi*y/180) * sin(pi*Y/180) +
                       cos(pi*y/180) * cos(pi*Y/180) * cos((z-Z) *pi / 180) 
                 )  * 180 / pi)

where Y and Z - are points of each row you whant to test, and y and z - are points of example row.

so you could make such a thing:

1. select each row in table and get its lon and lat.
2. apply query from this, changing $lon and $lat to data form point 1.

I don't know how to make this in access. But this way is as strait as slow.