How to Retrieve Results Within a Marker Radius from a Database: A SQL Approach to Accurate Distance Calculation

How to Retrieve Results within Marker Radiuses from a Database: Accurate Distance Calculation

To accurately determine the results that fall within specified marker radii from a database, it's crucial to consider the equation of a circle:

(x-x1)^2 + (y - y1)^2 = r^2

where (x,y) represents a point within the circle, (x1, y1) is the circle's center, and r is its radius.

In the context of this question, the center of the circle is the user's marker location (latitude and longitude), while the potential matches or results are represented by points within the circle. The radius is set to 1 kilometer.

To address the issue of different units (degrees for latitude and longitude vs. kilometers for radius), the equation is modified as follows:

((x-x1)*111.12)^2 + ((y-y1)*111.12)^2 = 4 (where 2 is the radius in kilometers and 111.12 represents the conversion factor from degrees to kilometers)

Based on this equation, the SQL statement to retrieve results that intersect with marker circles can be written as:

SELECT A.user_id, A.radius_id, A.latitude, A.logitude
FROM UserA AS A,
     (SELECT user_id, latitude, longitude
       FROM UserB
       WHERE user_id = 8) AS B
WHERE (POW((A.latitude-B.latitude)*111.12, 2) + POW((A.longitude - B.longitude)*111.12, 2)) <= 4

The above is the detailed content of How to Retrieve Results Within a Marker Radius from a Database: A SQL Approach to Accurate Distance Calculation. For more information, please follow other related articles on the PHP Chinese website!