📋

Description

This error occurs when a MySQL spatial function that is exclusively defined for geographic (latitude/longitude) coordinate systems is invoked with an argument belonging to a non-geographic Spatial Reference System Identifier (SRID). It indicates a fundamental incompatibility between the function's expected data type and the coordinate system of the provided spatial data.

💬

Error Message

Function %s is only defined for geographic spatial reference systems, but one of its arguments is in SRID %u, which is not geographic.

🔍

Known Causes

3 known causes

⚠️

Geographic Function on Projected Data

You are attempting to use a spatial function designed for latitude and longitude coordinates (geographic) with spatial data that is defined in a planar, projected coordinate system.

⚠️

Incorrect SRID Definition for Data

The spatial data column or literal was created or interpreted with an SRID that incorrectly identifies it as non-geographic, despite being used with a function requiring geographic input.

⚠️

Implicit SRID Conflict

When an SRID is not explicitly provided, MySQL might infer one or use a default that conflicts with the geographic requirements of the function being called.

🛠️

Solutions

3 solutions available

1. Ensure All Spatial Data Uses the Same SRID medium

Convert spatial data to a common geographic SRID before using spatial functions.

1

Identify the SRID of your spatial columns and the SRID expected by the geographic function.

SELECT SRID FROM INFORMATION_SCHEMA.SPATIAL_REF_SYS WHERE SRS_NAME = 'your_geographic_srs_name';
SELECT SRID FROM INFORMATION_SCHEMA.SPATIAL_REF_SYS WHERE SRS_NAME = 'your_non_geographic_srs_name';

2

If the SRIDs do not match, use the `ST_Transform` function to convert your spatial data to the correct geographic SRID. Replace `your_table`, `your_spatial_column`, `your_current_srid`, and `your_target_geographic_srid` with your actual values.

UPDATE your_table SET your_spatial_column = ST_Transform(your_spatial_column, your_target_geographic_srid) WHERE SRID(your_spatial_column) = your_current_srid;

3

Re-run the query that produced the error. It should now work if the SRIDs are consistent.

SELECT your_geographic_function(column1, column2) FROM your_table WHERE ...;

2. Use Geographic-Specific Spatial Functions easy

Leverage functions designed for geographic SRIDs when working with geographic data.

1

Review the MySQL documentation for spatial functions. Identify functions that are explicitly designed for geographic SRIDs (e.g., `ST_Distance_Sphere` instead of `ST_Distance`).

SELECT ST_Distance_Sphere(point1, point2) FROM your_table;

2

Replace any non-geographic spatial functions in your query with their geographic equivalents.

Original query (potential error):
SELECT ST_Distance(point1, point2) FROM your_table WHERE SRID(point1) = 4326 AND SRID(point2) = 4326; -- Assuming 4326 is geographic

Corrected query:
SELECT ST_Distance_Sphere(point1, point2) FROM your_table WHERE SRID(point1) = 4326 AND SRID(point2) = 4326;

3. Define Spatial Columns with Geographic SRIDs medium

Create new spatial columns with the correct geographic SRID from the outset.

1

When creating new tables or altering existing ones, specify the desired geographic SRID for your spatial columns. For example, SRID 4326 is commonly used for WGS 84.

CREATE TABLE your_new_table (
  id INT PRIMARY KEY,
  geo_location GEOMETRY NOT NULL SRID 4326
);

2

If altering an existing table, add a new column with the correct SRID and then migrate your data.

ALTER TABLE your_existing_table ADD COLUMN new_geo_location GEOMETRY NOT NULL SRID 4326;

UPDATE your_existing_table SET new_geo_location = ST_Transform(your_spatial_column, 4326);

-- Optionally, drop the old column and rename the new one

3

Ensure all future data insertions or updates use spatial data that conforms to the defined SRID.

INSERT INTO your_new_table (id, geo_location) VALUES (1, ST_GeomFromText('POINT(10 20)', 4326));

🔗

Related Errors

5 related errors

MySQL Error 3726: Geographic Function SRID Mismatch