How to Find the Nearest Latitude and Longitude within a Given Distance Using SQL?

Query to find the nearest latitude and longitude using SQL

This article will explore how to use SQL to retrieve the nearest longitude and latitude within a certain range of a given reference coordinate from the database.

Problem description:

You know the latitude and longitude value and want to extract the record in the database that best matches the latitude and longitude. But there is a distance threshold: if the calculated distance exceeds this threshold, no data should be retrieved.

Table structure:

列名	数据类型
id	整数
纬度	十进制
经度	十进制
地名	字符串
城市	字符串
国家	字符串
州	字符串
邮编	整数
海拔	整数

Query statement:

To accomplish this task, we use the following SQL query:

SELECT 纬度, 经度, SQRT(
    POW(69.1 * (纬度 - [startlat]), 2) +
    POW(69.1 * ([startlng] - 经度) * COS(纬度 / 57.3), 2)) AS distance
FROM 表名称 HAVING distance < 25
ORDER BY distance ASC;

• [startlat]: Starting latitude for distance measurement.
• [startlng]: The starting longitude for distance measurement.
• 25: Maximum allowed distance threshold in kilometers.

Instructions:

This query uses the great circle distance formula to calculate the distance between the reference coordinates ([startlat], [startlng]) and each record in the table. If the calculated distance is less than the specified threshold (25 kilometers in this example), the record will be included in the results. The query also sorts the results in ascending order based on distance, ensuring that the closest records are returned first.

The above is the detailed content of How to Find the Nearest Latitude and Longitude within a Given Distance Using SQL?. For more information, please follow other related articles on the PHP Chinese website!