Convert Gauss-Kruger (GK) coordinates to Latitude/Longitude

    This article is about converting the GK (Gauss-Krüger) coordinates into WGS 84 (Latitude/Longitude)

     

    Background:

    In cartography, the term Gauss–Krüger Geographic coordinate system is named after Carl Friedrich Gauss (1777-1855) and Johann Heinrich Louis Krüger. It is a particular set of transverse Mercator projections (map projections) used in narrow zones in Europe and South America, at least in Germany, Turkey, Austria, Slovenia, Croatia, Macedonia, Finland and Argentina. This Gauss–Krüger system is similar to the universal transverse Mercator system (UTM), but the central meridians of the Gauss–Krüger zones are only 3° apart, as opposed to 6° in UTM. Depending on the zone of the Gauss-Kruger coordinates, different EPSG codes are applicable (The country of Germany is within the EPSG-Codes 31466 to 31469). Nevermind about the EPSG code, the below conversion works on any Gauss-Kruger coordinates.

     

    The typical Gauss-Krüger coordinates is a pair of "Rechtswert" (East) and "Hochwert" (North) coordinates, both numbers are 7 digits long, may have up to 3 decimals (since the unit is meter, decimals don't make much sense as they are about decimeters, centimeters, even millimeter).

     

    Links:

     

    Solution:

    Enough about the background, to convert given set of Gauss-Krüger coordinates from a given "Rechtswert" (East) and "Hochwert" (North) in QlikView add the following script fragments. Adjust the values of XCOORD_Field and Y_COORD Field with the effective column names in which the GK values are found in the later LOAD statement.

     


     

    // constants

    LET XCOORD_Field = 'XKOORD';

    LET YCOORD_Field = 'YKOORD';

     

    // Formulas
    SET @rho = 57.29577951; //=180/PI

    SET @e2 = 0.006719219;

    SET @b1 = "($(YCOORD_Field)/10000855.7646)";

    SET @b2 = "Pow($(@b1),2)";

    SET @bf = "325632.08677*$(@b1)*((((((0.00000562025*$(@b2)-0.0000436398)*$(@b2)+0.00022976983)*$(@b2)-0.00113566119)

        *$(@b2)+0.00424914906)*$(@b2)-0.00831729565)*$(@b2)+1) / 3600/ $(@rho)";

    SET @fa = "(($(XCOORD_Field)-(Floor($(XCOORD_Field)/1000000)*1000000)-500000)/(6398786.849/Sqrt(1+(Pow(COS($(@bf)),2)*$(@e2)))))";

    SET @LAT_Formula = "($(@bf)-(Pow($(@fa),2)*(Sin($(@bf))/Cos($(@bf)))*(1+(Pow(COS($(@bf)),2) * $(@e2)))/2)

        +(Pow($(@fa),4)*(Sin($(@bf))/Cos($(@bf)))*(5+(3*Pow(Sin($(@bf))/Cos($(@bf)),2))+(6*(Pow(COS($(@bf)),2)

        * $(@e2)))-(6*(Pow(COS($(@bf)),2) * $(@e2))*Pow(Sin($(@bf))/Cos($(@bf)),2)))/24)) * $(@rho)";

    SET @LON_Formula = "(($(@fa)-(Pow($(@fa),3)*(1+(2*Pow(Sin($(@bf))/Cos($(@bf)),2))+(Pow(COS($(@bf)),2)

        * $(@e2)))/6)+(Pow($(@fa),5)*(1+(28*Pow(Sin($(@bf))/Cos($(@bf)),2))+(24*Pow(Sin($(@bf))/Cos($(@bf)),4)))/120))

        * $(@rho)/COS($(@bf))) + (Floor($(XCOORD_Field)/1000000)*3)";

     


    Now if you import a file or table with Gauss-Krüger coordinates in fields XKOORD / YKOORD this is your script (dark-blue part). If you plan to use QlikView's built-in Mapping Extension "Quick Map" or "QlikView Mapping Extension", the coordinates-pair needs to go into one field, which I am calling LON_LAT. They need to have US number format. use the dark-red part of the script as well.

     


    SET US_Format = "'','.',' '";

     

    LOAD

        *

        ,Num(LON,$(US_Format)) & ',' & Num(LAT,$(US_Format)) AS LON_LAT   

    ;

    LOAD

         ID

         ,XKOORD,

         ,YKOORD

         ,$(@LAT_Formula) AS LAT

         ,$(@LON_Formula) AS LON

    FROM

        [myExcel.xlsx]

        (ooxml, embedded labels);


    Enjoy,

    Christof