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

Find the nearest latitude and longitude using SQL query

When working with geospatial data, you often need to retrieve records based on their distance from a given point. This can be achieved by using a SQL query that calculates the distance between two points and retrieves records that fall within a specified radius.

Suppose a database table contains the latitude and longitude coordinates of various locations. For a given latitude and longitude, we want to retrieve the record with the closest latitude and longitude. However, if the distance between a given point and the retrieved record exceeds the specified threshold, the record should not be retrieved.

To do this we can use the following SQL query:

<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 < 25 ORDER BY distance</code>

In this query, [startlat] and [startlng] represent the latitude and longitude of a given point. The distance column uses the Haversine formula to calculate the distance between a given point and each record in the table. The HAVING clause filters out records whose distance exceeds 25 (specified threshold). Finally, the ORDER BY clause sorts the results by distance, returning records with the closest latitude and longitude first.

This query allows for efficient retrieval of nearby locations, ensuring that returned results fall within the desired distance range.

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