2

This is my solution - I needed this as function, to use it in select-statement:

CREATE DEFINER=`root`@`localhost` FUNCTION `deg2utm`(`lat` FLOAT, `lng` FLOAT, `utmzone` INT)
    RETURNS text CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

# Konstruktor
#große Halbachse/äquator.Radius (a), kleine Halbachse/Polarradius (b), Abplattung (iflat), Abweichung (e, e1sq), Skalierungsfaktor (k0)
DECLARE a, b, iflat, e, e1sq, k0 FLOAT;

# r curv 2 (nu), delta Long (p. sin1)
DECLARE latitude, nu, v1, p, sin1 FLOAT;

#Calculate Meridional Arc Length
DECLARE S, A0, B0, C0, D0, E0 FLOAT;

#// Koeffizienten für UTM Koordinaten
DECLARE K1, K2, K3, K4, K5 FLOAT;

#andere Variablen
DECLARE longZone, latZone TEXT;
DECLARE val INT;

#UTM-Koordinaten
DECLARE utmhoch, utmrechts TEXT;

SET a = 6378137;
SET b = 6356752.3142;
SET iflat = 298.257223563;
SET e = SQRT(1 - POW(b / a, 2));
SET e1sq = e * e / (1 - e * e);
SET sin1 = 4.84814E-06;
SET k0 = 0.9996;
SET A0 = 6367449.146;
SET B0 = 16038.42955;
SET C0 = 16.83261333;
SET D0 = 0.021984404;
SET E0 = 0.000312705;

#Input is validated?
    IF lat < -90 OR lat > 90 OR lng < -180 OR lng >=180 THEN RETURN FALSE; END IF;

#Variablen setzen
    SET latitude = RADIANS(lat);
    SET nu = a / POW(1 - POW(e * SIN(latitude), 2), (1 / 2.0));

    IF lng < 0 
    THEN SET v1 = FORMAT(((180 + lng) / 6.0) + 1, 0); ELSE SET v1 = FORMAT((lng / 6) + 31, 0);
    END IF;

    IF utmzone=32 
    THEN SET v1=32; 
    END IF;

    SET p = (lng - ((6 * v1) - 183)) * 36 / 100;
    SET K1 = A0 * latitude - B0 * SIN(2 * latitude) + C0 * SIN(4 * latitude) - D0 * SIN(6 * latitude) + E0 * SIN(8 * latitude) + k0;

    SET K2 = nu * SIN(latitude) * COS(latitude) * POW(sin1, 2) * k0 * 50000000;
    SET K3 = ((POW(sin1, 4) * nu * SIN(latitude) * POW(COS(latitude), 3)) / 24) * (5 - POW(TAN(latitude), 2) + 9 * e1sq * POW(COS(latitude), 2) + 4 * POW(e1sq, 2) * POW(COS(latitude), 4)) * k0 * (10000000000000000);
    SET K4 = nu * COS(latitude) * sin1 * k0 * 10000;
    SET K5 = POW(sin1 * COS(latitude), 3) * (nu / 6) * (1 - POW(TAN(latitude), 2) + e1sq * POW(COS(latitude), 2)) * k0 * 1000000000000;

    SET utmrechts = 500000 + (K4 * p + K5 * POW(p, 3)) ;
    IF utmzone=32 
    THEN SET utmrechts= concat(32,utmrechts); 
    END IF;

    SET utmhoch = K1 + K2 * p * p + K3 * POW(p, 4);
   IF lat < 0 
   THEN SET utmhoch = 10000000 + utmhoch;
    END IF;
    SET utmhoch=FORMAT(utmhoch, 0); 

RETURN utmrechts;
#RETURN utmhoch;
END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.