📋

Description

This error occurs when a longitude value within a geometry data type supplied to a MySQL function falls outside the permissible geographic range. MySQL enforces specific boundaries for longitude, typically between -180 and 180 degrees, to maintain data consistency and enable accurate spatial operations. It typically arises during data insertion or updates involving spatial columns.

💬

Error Message

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

🔍

Known Causes

4 known causes

⚠️

Manual Data Entry Error

Longitude values were manually entered incorrectly, exceeding the standard -180 to 180 degree range.

⚠️

Incorrect Data Transformation

Spatial data imported or transformed from another source contains longitude values that are malformed or not mapped to the correct range.

⚠️

Application Logic Flaw

The application generating or processing geometry data constructs longitude values that are outside the valid geographic bounds.

⚠️

Coordinate System Inconsistency

The data's inherent coordinate system might produce longitude values that are valid for its projection but not for MySQL's expected range (e.g., 0 to 360 instead of -180 to 180).

🛠️

Solutions

3 solutions available

1. Validate and Correct Input Geometry easy

Ensure all input geometries adhere to the valid longitude range before insertion or update.

1

Identify the specific geometry data causing the error. This might involve reviewing recent inserts or updates that triggered the error.

2

For each geometry, extract the longitude value. The error message will explicitly state the problematic longitude.

SELECT ST_X(your_geometry_column) FROM your_table WHERE your_id = problematic_id;

3

Check if the extracted longitude is within the valid range. The error message specifies the acceptable range, typically (-180, 180].

4

If the longitude is out of range, correct it to a value within (-180, 180]. This might involve simple adjustments or re-geocoding the location.

UPDATE your_table SET your_geometry_column = ST_GeomFromText('POINT(corrected_longitude latitude)') WHERE your_id = problematic_id;

5

Re-run the operation that caused the error to confirm the issue is resolved.

2. Implement Pre-Validation in Application Logic medium

Add checks in your application code to validate longitude before sending data to MySQL.

1

In your application's data insertion or update logic, before calling the MySQL `INSERT` or `UPDATE` statement, retrieve the longitude value from the geometry data.

2

Implement a conditional check to see if the longitude is within the acceptable range (-180, 180].

if (longitude <= -180 || longitude > 180) { // Handle error: log, reject, or attempt correction }

3

If the longitude is out of range, decide on an error handling strategy: reject the data, log the error for later review, or attempt to 'wrap' the longitude around (e.g., if 181, treat as -179).

4

Only proceed with sending the data to MySQL if the longitude is valid.

3. Cleanse Existing Data with a Script advanced

Create a script to scan and correct invalid longitude values in your existing database.

1

Write a SQL script that iterates through your table containing spatial data.

DELIMITER //
CREATE PROCEDURE CleanseInvalidLongitudes() 
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE current_id INT;
    DECLARE current_geom GEOMETRY;
    DECLARE current_lon DOUBLE;
    DECLARE cur CURSOR FOR SELECT your_id, your_geometry_column FROM your_table;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO current_id, current_geom;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET current_lon = ST_X(current_geom);

        IF current_lon <= -180 OR current_lon > 180 THEN
            -- Correct the longitude by wrapping it around
            SET current_lon = IF(current_lon > 180, current_lon - 360, current_lon + 360);
            -- You might want to log this correction or handle it differently
            UPDATE your_table SET your_geometry_column = ST_GeomFromText(CONCAT('POINT(', current_lon, ' ', ST_Y(current_geom), ')')) WHERE your_id = current_id;
        END IF;
    END LOOP;

    CLOSE cur;
END //
DELIMITER ;

2

Execute the stored procedure to cleanse your data.

CALL CleanseInvalidLongitudes();

3

Consider dropping the stored procedure after successful execution if it's not needed for ongoing maintenance.

DROP PROCEDURE IF EXISTS CleanseInvalidLongitudes;

🔗

Related Errors

5 related errors

MySQL Error 3731: Invalid Geometry Longitude Range