Question

Let s 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 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?

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

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:

select each row in table and get its lon and lat.
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.

Answer 3

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

友情链接