Remove Distant Locations using Proximity Conditions

Delete Records Based on Spatial Criteria

Write a MySQL query to delete records from the Locations table where the distance from a given point exceeds 5000 units.

Solution:

-- Delete locations from the Locations table that are more than 5000 units away from a specified reference point.
DELETE FROM Locations

    -- Use the ST_Distance function to calculate the distance between the 'location' column 
    -- and the reference point (created using ST_GeomFromText). Only rows where the distance 
    -- exceeds 5000 units will be deleted.
    WHERE ST_Distance(location, ST_GeomFromText('POINT(-73.985130 40.758896)')) > 5000;  

Explanation:

• Purpose of the Query:
• The goal is to remove records based on a spatial condition.
• This demonstrates filtering and deletion using spatial functions.
• Key Components:
• ST_Distance(location, ST_GeomFromText('POINT(...)')) > 5000 : Evaluates the distance condition for deletion.
• Real-World Application:
• Useful for maintaining a dataset with only nearby or relevant geographic entries.

Notes:

• Always review the spatial condition to avoid unintended data loss.

For more Practice: Solve these Related Problems:

• Write a MySQL query to delete records from "GeoLocations" where the distance from a given point exceeds 10000 units using ST_Distance.
• Write a MySQL query to remove entries from "Parks" if their spatial data lies outside a specified bounding box defined by spatial functions.
• Write a MySQL query to delete records from "Regions" where the calculated centroid is farther than a given distance from a reference point.
• Write a MySQL query to delete rows from "Shops" based on a spatial condition involving ST_Distance with a dynamically set threshold.

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

Previous MySQL Exercise: Update a Spatial Data Point.
Next MySQL Exercise: Convert Spatial Data to Well-Known Text (WKT).