20

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?

Peter O.
  • 32,158
  • 14
  • 82
  • 96
Alon Amir
  • 4,913
  • 9
  • 47
  • 86

3 Answers3

33

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
Juha Syrjälä
  • 33,425
  • 31
  • 131
  • 183
jbochi
  • 28,816
  • 16
  • 73
  • 90
  • Thanks, how can I use it in an Access Database? – Alon Amir Dec 13 '09 at 11:28
  • As far as I know, you cannot create this type of function in an Access Database. You should try the second approach. (Some modifications will be needed. I believe that access does not have an PI() function, for instance) – jbochi Dec 13 '09 at 11:35
  • Is this in miles or kilometers? – Pentium10 May 28 '10 at 16:40
  • 4
    I know this is old, but the 1.1515 tells us this is in miles. – meteorainer May 13 '11 at 20:18
  • 2
    Following up on an old topic here I know, but wanted to add for posterity, if using SQL Server 2008, there is a built in `GEOGRAPHY` spatial data type which can easily calculate distances (e.g. the `STDistance` method) between two coordinates. I find this nicer since it also takes into consideration the curvature/ellipsoidal shape of the earth. (The article linked at the top of this answer seems to assume a flat projection.) Probably not too noticeable if the distances are short enough, but might be across greater distances. See http://stackoverflow.com/a/501224/110871 for a concise example. – Funka Feb 06 '13 at 01:28
1

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.

Community
  • 1
  • 1
Vasiliy Stavenko
  • 1,174
  • 1
  • 12
  • 29
0

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

Paul Creasey
  • 28,321
  • 10
  • 54
  • 90