Add a Geographic Coordinate to the Locations Table

Insert a Spatial Data Point Using ST_GeomFromText

Write a MySQL query to insert a new record into the Locations table with a geographic point using ST_GeomFromText.

Solution:

-- Insert a new location into the Locations table, specifying the name and geographic coordinates.
INSERT INTO Locations (name, location)

-- Use the ST_GeomFromText function to create a POINT value representing the geographic coordinates (-73.965355, 40.782865).
VALUES ('Central Park', ST_GeomFromText('POINT(-73.965355 40.782865)'));

Explanation:

• Purpose of the Query:
• The goal is to insert spatial data using a well-known text (WKT) representation.
• This demonstrates how to use ST_GeomFromText to convert text into a spatial point.
• Key Components:
• ST_GeomFromText('POINT(-73.965355 40.782865)') : Converts the WKT string into a POINT value.
• INSERT INTO Locations : Specifies the table and columns to receive the data.
• Real-World Application:
• Ideal for recording specific geographic locations such as parks or landmarks.

Notes:

• Verify that the WKT format matches the expected syntax.

For more Practice: Solve these Related Problems:

• Write a MySQL query to insert a record into "GeoLocations" with a spatial point using ST_GeomFromText for the "coordinates" column.
• Write a MySQL query to insert a new record into "Locations" with a POINT column defined by ST_GeomFromText, ensuring the coordinate order is longitude then latitude.
• Write a MySQL query to insert a record into "Parks" with a spatial column using ST_GeomFromText to represent the park's central point.
• Write a MySQL query to insert multiple rows into a table "Landmarks" where each row uses ST_GeomFromText to convert WKT strings into POINT values.

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

Previous MySQL Exercise: Create a Table with a Spatial Column.
Next MySQL Exercise: Calculate Distance between Two Points.