Determine the Proximity between Two Spatial Points

Calculate Distance between Two Points

Write a MySQL query to calculate the distance between a stored location and a given point using ST_Distance.

Solution:

-- Calculate the distance from each location in the Locations table to a reference point.
SELECT name,

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

-- Retrieve data from the Locations table.
FROM Locations;

Explanation:

• Purpose of the Query:
• The goal is to compute the distance between two geographic points.
• This demonstrates the use of the ST_Distance function for spatial calculations.
• Key Components:
• ST_Distance(location, ST_GeomFromText('POINT(-73.985130 40.758896)')) : Calculates the distance from the stored point to the reference point.
• AS distance : Provides an alias for the computed value.
• Real-World Application:
• Useful for finding nearby points of interest relative to a user’s location.

Notes:

• The distance unit depends on the spatial reference system in use.

For more Practice: Solve these Related Problems:

• Write a MySQL query to calculate the distance between each record's "coordinates" in "GeoLocations" and a fixed reference point using ST_Distance.
• Write a MySQL query to compute the distance between points in "Stores" and a dynamic point provided via ST_PointFromText, aliasing the result as "dist".
• Write a MySQL query to calculate and display the distance from each "Cities" record to a given coordinate, filtering out results with distances greater than a threshold.
• Write a MySQL query to determine the great-circle distance between spatial points in "Airports" and a specified location using ST_Distance.

Go to:

• Hands-on MySQL Spatial Data Exercises Home. ↩
• MySQL Exercises Home ↩

PREV : Insert a Spatial Data Point Using ST_GeomFromText.
NEXT : Find Locations Within a Radius.

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