Revise the Geographic Coordinate in the Locations Table

Update a Spatial Data Point

Write a MySQL query to update the location of a specific record in the Locations table using ST_GeomFromText.

Solution:

-- Update the geographic coordinates of the location named 'Central Park' in the Locations table.
UPDATE Locations

    -- Set the 'location' column to a new POINT value representing the updated coordinates (-73.985000, 40.758000).
    SET location = ST_GeomFromText('POINT(-73.985000 40.758000)')

    -- Ensure that only the row where the 'name' column matches 'Central Park' is updated.
    WHERE name = 'Central Park';  

Explanation:

• Purpose of the Query:
• The goal is to modify the spatial data for an existing record.
• This demonstrates updating a geometry column using a spatial function.
• Key Components:
• UPDATE Locations : Specifies the table to update.
• SET location = ST_GeomFromText('POINT(...)') : Converts new WKT to a spatial point and updates the record.
• Real-World Application:
• Useful when location data changes due to corrections or updates.

Notes:

• Ensure the new point follows the same spatial reference system.

For more Practice: Solve these Related Problems:

• Write a MySQL query to update the "coordinates" column in "GeoLocations" for a specific record using ST_GeomFromText with new values.
• Write a MySQL query to modify the location of a record in "Parks" by updating its POINT column with a new geographic coordinate.
• Write a MySQL query to update a spatial column in "Stores" using ST_GeomFromText where the record matches a specific condition.
• Write a MySQL query to change the spatial data of a record in "Routes" to a new LINESTRING using ST_GeomFromText.

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

Previous MySQL Exercise: Create a Spatial Index on a Column.
Next MySQL Exercise: Delete Records Based on Spatial Criteria.