📋

Description

This error occurs when a latitude value provided within a geometry function parameter is outside the valid range of -90 to +90 degrees. It indicates that the geographic coordinate supplied is invalid, preventing the successful execution of the spatial operation.

💬

Error Message

A parameter of function %s contains a geometry with latitude %f, which is out of range. It must be within [%f, %f].

🔍

Known Causes

3 known causes

⚠️

Manual Data Entry Error

A latitude value was manually entered incorrectly, exceeding the valid geographic range of -90 to +90 degrees.

⚠️

Application Logic Flaw

The application generating the spatial data produced an invalid latitude value due to a bug or incorrect calculation within its code.

⚠️

External Data Mismatch

Spatial data imported from an external source or migrated from another system contains latitude values that do not conform to the standard geographic range.

🛠️

Solutions

3 solutions available

1. Validate and Correct Input Geometries easy

Ensure that all latitude values in your spatial data are within the valid range of -90 to +90 degrees.

1

Identify the query or operation that is causing the error. This usually involves inserting or updating data into a table with a spatial data type (e.g., POINT, POLYGON) or using spatial functions.

2

Examine the data being passed to the spatial function or being inserted into the spatial column. Specifically, check the latitude values.

SELECT ST_AsText(your_geometry_column) FROM your_table WHERE ST_Y(your_geometry_column) > 90 OR ST_Y(your_geometry_column) < -90;

3

Correct any latitude values that are outside the range of -90 to +90 degrees. This might involve manual correction, re-geocoding data, or adjusting the source of your spatial data.

UPDATE your_table SET your_geometry_column = ST_GeomFromText('POINT(longitude latitude)') WHERE some_condition_to_identify_bad_data;

4

Re-run the operation that previously failed after correcting the data.

2. Sanitize Geometries During Insertion/Update medium

Implement checks within your application logic or SQL triggers to validate latitude before it's stored.

1

If you control the application code that inserts or updates spatial data, add validation logic before calling MySQL's spatial functions or inserting into spatial columns.

function isValidLatitude(latitude) {
  return latitude >= -90 && latitude <= 90;
}

// In your data processing logic:
if (isValidLatitude(inputLatitude)) {
  // Proceed with MySQL insert/update
} else {
  // Handle the error: log, reject, or attempt correction
}

2

Alternatively, create a SQL trigger on the table with the spatial column to perform validation before an INSERT or UPDATE operation.

DELIMITER $$
CREATE TRIGGER before_insert_or_update_geometry
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
  IF ST_Y(NEW.your_geometry_column) > 90 OR ST_Y(NEW.your_geometry_column) < -90 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Latitude out of range for geometry.';
  END IF;
END$$

3

Ensure that the trigger also handles UPDATE operations by checking `NEW.your_geometry_column`.

DELIMITER $$
CREATE TRIGGER before_insert_or_update_geometry
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
  IF ST_Y(NEW.your_geometry_column) > 90 OR ST_Y(NEW.your_geometry_column) < -90 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Latitude out of range for geometry.';
  END IF;
END$$

CREATE TRIGGER before_update_geometry
BEFORE UPDATE ON your_table
FOR EACH ROW
BEGIN
  IF ST_Y(NEW.your_geometry_column) > 90 OR ST_Y(NEW.your_geometry_column) < -90 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Latitude out of range for geometry.';
  END IF;
END$$

3. Use ST_GeomFromText with Explicit SRID medium

When creating geometries, explicitly define the Spatial Reference Identifier (SRID) to ensure correct interpretation, especially if data originates from different sources.

1

When creating spatial data using functions like `ST_GeomFromText` or `ST_PointFromText`, always specify the SRID. For standard WGS84 (latitude/longitude), SRID 4326 is commonly used.

SET @point_text = 'POINT(-74.0060 40.7128)'; -- New York City
SET @srid = 4326;

INSERT INTO your_table (your_geometry_column)
VALUES (ST_GeomFromText(@point_text, @srid));

2

If your data is already in the table and you suspect incorrect SRID interpretation is contributing to range issues, you might need to re-project or re-create the geometries with the correct SRID.

UPDATE your_table
SET your_geometry_column = ST_Transform(your_geometry_column, 4326)
WHERE ST_Srid(your_geometry_column) != 4326;

3

Note: `ST_Transform` can be computationally expensive. It's better to ensure correct SRIDs upon insertion.

🔗

Related Errors

5 related errors

MySQL Error 3732: Latitude Out of Range