Display the Spatial Reference Identifier for Geometries

Retrieve the SRID of a Geometry

Write a MySQL query to extract the SRID of the geometry stored in the location column using ST_SRID.

Solution:

-- Retrieve the spatial reference identifier (SRID) of each location in the Locations table.
SELECT 

    -- Select the 'name' column to display the name of the location.
    name, 

    -- Use the ST_SRID function to retrieve the SRID of the 'location' column (a POINT). 
    -- The result is aliased as 'SRID' for clarity.
    ST_SRID(location) AS SRID

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

Explanation:

• Purpose of the Query:
• The goal is to determine the SRID associated with the spatial data.
• This demonstrates the use of the ST_SRID function to verify coordinate systems.
• Key Components:
• ST_SRID(location) : Extracts the SRID from the spatial column.
• AS SRID : Assigns an alias to the output for clarity.
• Real-World Application:
• Critical for ensuring consistency and correctness in spatial data handling.

Notes:

• Confirm that spatial data is stored with the appropriate SRID for your application.

For more Practice: Solve these Related Problems:

• Write a MySQL query to extract and display the SRID of the spatial column in the "Locations" table using ST_SRID.
• Write a MySQL query to select the SRID of geometries stored in the "GeoData" table and alias it as "SpatialRef".
• Write a MySQL query to retrieve the SRID for each record in the "MapAreas" table using ST_SRID and sort the results by SRID.
• Write a MySQL query to display the SRID along with other spatial data from the "CityCenters" table using ST_SRID.

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

Previous MySQL Exercise: Find Locations within a Bounding Box.