如何使用MySQL触发器将导入的XY坐标转换为经纬度？

Alright, let's walk through how to solve this problem—getting your TXT map coordinates into the zone table, with automatic conversion from XY to latitude/longitude via a trigger. Here's a step-by-step breakdown tailored to your needs:

First things first: converting XY coordinates to lat/lng depends entirely on what coordinate system your X/Y values use. Your example values (X=240005.400, Y=5176335.820) look like UTM (Universal Transverse Mercator) coordinates—specifically Zone 31N, given the easting/northing ranges. If your data uses a different CRS (like Web Mercator or a local projected system), you’ll need to adjust the conversion logic later.

MySQL doesn’t have built-in coordinate conversion functions, so we’ll first make a stored function to handle the math, then hook it up to a BEFORE INSERT trigger that fills in latitude and longitude automatically.

First, Create the UTM-to-WGS84 Conversion Function #

This function converts UTM coordinates to WGS84 (the standard GPS lat/lng system). Replace the zone and hemisphere values if your data uses a different UTM zone:

DELIMITER //

CREATE FUNCTION UTMtoLatLng(easting DECIMAL(10,3), northing DECIMAL(10,3), zone INT, northernHemisphere BOOLEAN)
RETURNS POINT
DETERMINISTIC
BEGIN
    -- WGS84 datum constants
    DECLARE a DECIMAL(20,10) DEFAULT 6378137.0;
    DECLARE eSquared DECIMAL(20,10) DEFAULT 0.00669437999014;
    DECLARE ePrimeSquared DECIMAL(20,10);
    DECLARE n, A, M, mu, phi1Rad, phi1 DECIMAL(20,10);
    DECLARE N1, T1, C1, R1, D, lngRad, lng, latRad, lat DECIMAL(20,10);
    
    SET ePrimeSquared = eSquared / (1 - eSquared);
    SET n = a / SQRT(1 - eSquared * SIN(RADIANS(0)) * SIN(RADIANS(0)));
    SET A = (easting - 500000) / (n * POWER(1 - eSquared, 0.5));
    SET M = northing / (n * (1 - eSquared/4 - 3*POWER(eSquared,2)/64 - 5*POWER(eSquared,3)/256));
    
    -- Calculate intermediate values
    SET mu = M + (3*eSquared/8 - 27*POWER(eSquared,2)/1024) * SIN(2*M) + 
             (21*eSquared/256 - 55*POWER(eSquared,2)/1024) * SIN(4*M) + 
             (151*POWER(eSquared,2)/6144) * SIN(6*M);
    SET phi1Rad = mu + (3*ePrimeSquared/2 - 27*POWER(ePrimeSquared,2)/32) * SIN(2*mu) + 
                  (21*ePrimeSquared/16 - 55*POWER(ePrimeSquared,2)/32) * SIN(4*mu) + 
                  (151*POWER(ePrimeSquared,2)/96) * SIN(6*mu);
    SET phi1 = DEGREES(phi1Rad);
    
    SET N1 = a / SQRT(1 - eSquared * SIN(phi1Rad) * SIN(phi1Rad));
    SET T1 = TAN(phi1Rad) * TAN(phi1Rad);
    SET C1 = ePrimeSquared * COS(phi1Rad) * COS(phi1Rad);
    SET R1 = a * (1 - eSquared) / POWER(1 - eSquared * SIN(phi1Rad) * SIN(phi1Rad), 1.5);
    SET D = A / (N1 * COS(phi1Rad));
    
    -- Compute latitude and longitude
    SET latRad = phi1Rad - (N1 * TAN(phi1Rad) / R1) * 
                 (D*D/2 - (5 + 3*T1 + 10*C1 - 4*C1*C1 - 9*ePrimeSquared) * POWER(D,4)/24 + 
                 (61 + 90*T1 + 298*C1 + 45*T1*T1 - 252*ePrimeSquared - 3*C1*C1) * POWER(D,6)/720);
    SET lat = DEGREES(latRad);
    
    SET lngRad = RADIANS((zone - 1)*6 - 180 + 3) + 
                 (D - (1 + 2*T1 + C1) * POWER(D,3)/6 + 
                 (5 - 2*C1 + 28*T1 - 3*C1*C1 + 8*ePrimeSquared + 24*T1*T1) * POWER(D,5)/120) / COS(phi1Rad);
    SET lng = DEGREES(lngRad);
    
    -- Adjust for southern hemisphere if needed
    IF NOT northernHemisphere THEN
        SET lat = -lat;
    END IF;
    
    RETURN POINT(lng, lat);
END //

DELIMITER ;

Then, Create the BEFORE INSERT Trigger #

This trigger runs every time you insert a new row, uses the function to convert X/Y to lat/lng, and populates the empty fields:

DELIMITER //

CREATE TRIGGER zone_before_insert
BEFORE INSERT ON zone
FOR EACH ROW
BEGIN
    DECLARE latlng POINT;
    -- Update these values to match your UTM zone and hemisphere
    SET latlng = UTMtoLatLng(NEW.X, NEW.Y, 31, TRUE);
    SET NEW.latitude = ST_Y(latlng);
    SET NEW.longitude = ST_X(latlng);
END //

DELIMITER ;

Assuming your TXT file has one record per line, with fields separated by commas (e.g., 1,240005.400,5176335.820,15.954), use MySQL’s LOAD DATA INFILE command to import:

LOAD DATA INFILE '/full/path/to/your/map_coords.txt'
INTO TABLE zone
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(ID, X, Y, depth);

• If you’re on Windows, use '\r\n' as the line terminator instead of '\n'.
• If the file is on your local machine (not the server), add LOCAL to the command: LOAD DATA LOCAL INFILE ...

• Test First: Always test with a single row before importing all data to confirm the conversion works:

  INSERT INTO zone (ID, X, Y, depth) VALUES (1, 240005.400, 5176335.820, 15.954);
  SELECT * FROM zone WHERE ID = 1;
  

• CRS Adjustment: If your XY coordinates aren’t UTM, replace the UTMtoLatLng function with the correct conversion formula for your specific coordinate system.
• Precision: The function uses WGS84, which is the global standard for GPS. If you need a different datum, adjust the constants at the top of the function.

内容的提问来源于stack exchange，提问作者Baeyens Raphaël