How Can I Find the Nearest Latitude and Longitude in a Database Using SQL?

Use SQL query to locate the nearest latitude and longitude

When retrieving data from a database, you often need to find the closest latitude and longitude record to a given point. This is especially useful in scenarios like finding nearby businesses, attractions, or other points of interest.

Query Points

To perform this task, you need a SQL query that can calculate the distance between two points based on their latitude and longitude. This involves using Haversine's formula which takes into account the curvature of the Earth. Additionally, you need to specify the maximum allowed distance for retrieving records.

SQL query structure

The following SQL query satisfies these requirements:

<code class="language-sql">SELECT latitude, longitude, SQRT(
    POW(69.1 * (latitude - [startlat]), 2) +
    POW(69.1 * ([startlng] - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM TableName HAVING distance < [max_distance] ORDER BY distance;</code>

• [startlat] and [startlng] represent the latitude and longitude of the starting point.
• [max_distance] Specifies the maximum distance at which records should be retrieved.

Description:

This query uses the Haversine formula to calculate the distance between the latitude and longitude of each record and the given starting point. The "distance" column stores the resulting distance. Records whose distance is less than the specified maximum distance are then selected and sorted by their distance from the starting point.

By using this query, you can efficiently find the closest latitude and longitude record to a given point, taking into account distance constraints.

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