📋

Description

This error occurs when defining a Geographic Spatial Reference System (SRS) in MySQL with incorrect axis names or order. MySQL requires specific axis names for geographic coordinate systems: one axis must be NORTH or SOUTH (for latitude) and the other must be EAST or WEST (for longitude).

💬

Error Message

The spatial reference system definition for SRID %u specifies invalid geographic axes '%s' and '%s'. One axis must be NORTH or SOUTH and the other must be EAST or WEST.

🔍

Known Causes

3 known causes

⚠️

Incorrect Axis Naming

The axis names specified in the `CREATE SPATIAL REFERENCE SYSTEM` statement for a geographic SRS do not conform to the required 'NORTH'/'SOUTH' and 'EAST'/'WEST' convention.

⚠️

Invalid Axis Order

Even if correct axis names are used, their order in the SRS definition might not match the expected latitude-first (NORTH/SOUTH) and longitude-second (EAST/WEST) or vice-versa, as per standard conventions.

⚠️

Non-Standard SRS Definition

Attempting to define a custom geographic SRS using non-standard or unsupported axis definitions that deviate from MySQL's expected WKT (Well-Known Text) or SQL/MM spatial standards.

🛠️

Solutions

3 solutions available

1. Correct Invalid SRID Definition medium

Modify the problematic SRID definition in the `spatial_ref_sys` table to use valid axis names.

1

Identify the SRID and the invalid axis names from the error message. The error message will typically show `%u` as the SRID and `%s` and `%s` as the invalid axis names.

2

Connect to your MySQL server using a client like `mysql` or MySQL Workbench.

mysql -u your_user -p your_database

3

Update the `spatial_ref_sys` table to correct the axis names. Replace `your_srid`, `invalid_axis1`, `invalid_axis2`, `correct_axis1`, and `correct_axis2` with the actual values from your error and the correct axis names (e.g., 'NORTH'/'SOUTH', 'EAST'/'WEST').

UPDATE spatial_ref_sys SET axis_name1 = 'CORRECT_AXIS1', axis_name2 = 'CORRECT_AXIS2' WHERE srid = YOUR_SRID AND axis_name1 = 'INVALID_AXIS1' AND axis_name2 = 'INVALID_AXIS2';

4

Verify the change by querying the `spatial_ref_sys` table for the updated SRID.

SELECT srid, axis_name1, axis_name2 FROM spatial_ref_sys WHERE srid = YOUR_SRID;

2. Re-import or Recreate Spatial Reference Data advanced

If the SRID definition is part of a larger spatial data import or is corrupted, re-importing or recreating the `spatial_ref_sys` table is a robust solution.

1

Locate the source of your spatial reference system definitions. This might be a SQL dump file or a script used for initial setup.

2

Backup your current `spatial_ref_sys` table before making any changes.

CREATE TABLE spatial_ref_sys_backup LIKE spatial_ref_sys;
INSERT INTO spatial_ref_sys_backup SELECT * FROM spatial_ref_sys;

3

Delete the problematic SRID entry from the `spatial_ref_sys` table. Replace `YOUR_SRID` with the SRID from the error message.

DELETE FROM spatial_ref_sys WHERE srid = YOUR_SRID;

4

Re-insert the correct SRID definition. You'll need to know the correct parameters for the SRID, including valid axis names. Refer to official SRID definitions or documentation for the correct values. For example, for SRID 4326 (WGS 84), the axes are typically 'EAST' and 'NORTH'.

INSERT INTO spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text, axis_name1, axis_name2) VALUES (4326, 'EPSG', 4326, 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["degree",0.0174532925199433]]', '+proj=longlat +datum=WGS84 +no_defs', 'EAST', 'NORTH');

5

Alternatively, if you have a full SQL dump of `spatial_ref_sys` that contains correct definitions, you can drop and recreate the table (after backing it up).

DROP TABLE spatial_ref_sys;
-- Then re-run the script that creates and populates spatial_ref_sys

3. Use a Valid Predefined SRID easy

If the SRID causing the error is custom or incorrectly defined, switch to a standard, well-defined SRID that uses correct axis naming conventions.

1

Identify the spatial data that is using the invalid SRID. This might be in your table definitions or within your application code that inserts spatial data.

2

Consult the `spatial_ref_sys` table in your MySQL instance to find a suitable, predefined SRID that matches your geographic coordinate system requirements. Common SRIDs like 4326 (WGS 84) are widely used and have correct axis definitions ('EAST', 'NORTH').

SELECT srid, srtext, axis_name1, axis_name2 FROM spatial_ref_sys WHERE auth_name = 'EPSG' AND srtext LIKE '%WGS 84%';

3

Update your table schema to use the chosen valid SRID. Replace `your_table`, `your_geometry_column`, `your_invalid_srid`, and `your_valid_srid`.

ALTER TABLE your_table MODIFY COLUMN your_geometry_column GEOMETRY SRID YOUR_VALID_SRID;

4

If the invalid SRID is used in application logic, update your application code to use the correct SRID when creating or querying spatial data.

🔗

Related Errors

5 related errors

MySQL Error 3736: Invalid Geographic Axes