Determine if one coordinate is in radius of another

sql coordinates latitude-longitude
Alon Amir picture Alon Amir · Dec 13, 2009 · Viewed 13.6k times · Source

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

jbochi picture jbochi · Dec 13, 2009

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

Related questions

Find nearest latitude/longitude with an SQL query

I have latitude and longitude and I want to pull the record from the database, which has nearest latitude and longitude by the distance, if that distance gets longer than specified one, then don't retrieve it. Table structure: id latitude …

Read More
SQL query, select nearest places by a given coordinates

I have $latitude = 29.6815400 and $longitude = 64.3647100, now in MySQL I would like to take the 15 nearest places to these coordinates and I'm planning to do this query: SELECT * FROM places WHERE latitude BETWEEN($latitude - 1, $latitude + 1) AND longitude BETWEEN($longitude - 1, $…

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More