Determine the Closest Geographic Point to a Reference Location

Retrieve the Nearest Location

Write a MySQL query to find the nearest location to a given point by ordering results using ST_Distance.

Solution:

-- Retrieve the nearest location to a specified reference point by calculating distances and sorting the results.
SELECT 

    -- Select the 'name' column to display the name of the location.
    name, 

    -- Use the ST_Distance function to calculate the distance between the 'location' column 
    -- and the reference point (created using ST_GeomFromText). The result is aliased as 'distance'.
    ST_Distance(location, ST_GeomFromText('POINT(-73.985130 40.758896)')) AS distance

-- Retrieve data from the Locations table.
FROM Locations

    -- Sort the results in ascending order of the calculated distance, so the nearest location appears first.
    ORDER BY distance ASC

    -- Limit the output to only the first row, which corresponds to the nearest location.
    LIMIT 1;

Explanation:

• Purpose of the Query:
• The goal is to determine the closest location from a reference point.
• This demonstrates ordering spatial data by proximity.
• Key Components:
• ORDER BY distance ASC : Sorts results from nearest to farthest.
• LIMIT 1 : Returns only the top (nearest) record.
• Real-World Application:
• Essential for location-based services and nearest-neighbor searches.

Notes:

• Ensure the reference point is correctly defined for accurate results.

For more Practice: Solve these Related Problems:

• Write a MySQL query to find the nearest record in "GeoLocations" to a given reference point by ordering results with ST_Distance and limiting the output.
• Write a MySQL query to retrieve the closest store from the "Stores" table to a dynamic coordinate using spatial distance calculations.
• Write a MySQL query to select the record from "Parks" that is nearest to a specified point, ordering the results by calculated distance.
• Write a MySQL query to find the nearest neighbor in the "Regions" table relative to a provided coordinate, ensuring the result includes the computed distance.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Convert Spatial Data to Well-Known Text (WKT).
Next MySQL Exercise: Create a Table with Multiple Spatial Data Types.