w3resource

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.

Go to:


PREV : Convert Spatial Data to Well-Known Text (WKT).
NEXT : Create a Table with Multiple Spatial Data Types.

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

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.