Determine if one coordinate is in radius of another
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?
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
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
andlat
. - 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.