Northings and Eastings to Latitude and Longitude

Greg_Deckler · ‎09-17-2021

Never knew so many different mapping systems existed! Well here is a new one to me at least, Northings and Eastings. Came out of this thread. Was able to "repurpose" code from here: go - how convert easting northing coordinates to latitude longitude? - Stack Overflow. So credit to the original authors. Honestly no idea if it is accurate or correct. Seems to return the same position as in the original post. The below is an "improved" version that enforces a "brute force for loop" in DAX. That means repeating the same code multiple times with different variable names.

Latitude Longitude 2 = 
    VAR northing = [Northing]
    VAR easting = [Easting]

    VAR radToDeg = 180 / PI()
    VAR degToRad = PI() / 180

    VAR a    = 6377563.396
    VAR b    = 6356256.909  // Airy 1830 major & minor semi-axes
    VAR f0   = 0.9996012717 // NatGrid scale factor on central meridian
    VAR lat0 = 49 * degToRad
    VAR lon0 = -2 * degToRad // NatGrid true origin
    VAR n0   = -100000.0
    VAR e0   = 400000.0        // northing & easting of true origin, metres
    VAR e2   = 1 - (b*b)/(a*a) // eccentricity squared
    VAR n    = (a - b) / (a + b)
    VAR n2   = n * n
    VAR n3   = n * n * n

    VAR m = 0
    VAR lat1 = (northing-n0-m)/(a*f0) + lat0
    VAR ma1 = (1 + n + (5/4)*n2 + (5/4)*n3) * (lat1 - lat0)
    VAR mb1 = (3*n + 3*n*n + (21/8)*n3) * SIN(lat1-lat0) * COS(lat1+lat0)
    VAR mc1 = ((15/8)*n2 + (15/8)*n3) * SIN(2*(lat1-lat0)) * COS(2*(lat1+lat0))
    VAR md1 = (35 / 24) * n3 * SIN(3*(lat1-lat0)) * COS(3*(lat1+lat0))
    VAR m1 = b * f0 * (ma1 - mb1 + mc1 - md1) // meridional arc

    VAR lat2 = (northing-n0-m1)/(a*f0) + lat1
    VAR ma2 = (1 + n + (5/4)*n2 + (5/4)*n3) * (lat2 - lat0)
    VAR mb2 = (3*n + 3*n*n + (21/8)*n3) * SIN(lat2-lat0) * COS(lat2+lat0)
    VAR mc2 = ((15/8)*n2 + (15/8)*n3) * SIN(2*(lat2-lat0)) * COS(2*(lat2+lat0))
    VAR md2 = (35 / 24) * n3 * SIN(3*(lat2-lat0)) * COS(3*(lat2+lat0))
    VAR m2 = b * f0 * (ma2 - mb2 + mc2 - md2) // meridional arc

    VAR lat3 = (northing-n0-m2)/(a*f0) + lat2
    VAR ma3 = (1 + n + (5/4)*n2 + (5/4)*n3) * (lat3 - lat0)
    VAR mb3 = (3*n + 3*n*n + (21/8)*n3) * SIN(lat3-lat0) * COS(lat3+lat0)
    VAR mc3 = ((15/8)*n2 + (15/8)*n3) * SIN(2*(lat3-lat0)) * COS(2*(lat3+lat0))
    VAR md3 = (35 / 24) * n3 * SIN(3*(lat3-lat0)) * COS(3*(lat3+lat0))
    VAR m3 = b * f0 * (ma3 - mb3 + mc3 - md3) // meridional arc

    VAR lat4 = (northing-n0-m3)/(a*f0) + lat3
    VAR ma4 = (1 + n + (5/4)*n2 + (5/4)*n3) * (lat4 - lat0)
    VAR mb4 = (3*n + 3*n*n + (21/8)*n3) * SIN(lat4-lat0) * COS(lat4+lat0)
    VAR mc4 = ((15/8)*n2 + (15/8)*n3) * SIN(2*(lat4-lat0)) * COS(2*(lat4+lat0))
    VAR md4 = (35 / 24) * n3 * SIN(3*(lat4-lat0)) * COS(3*(lat4+lat0))
    VAR m4 = b * f0 * (ma4 - mb4 + mc4 - md4) // meridional arc

    VAR lat = (northing-n0-m4)/(a*f0) + lat4

    VAR cosLat = COS(lat)
    VAR sinLat = SIN(lat)
    VAR nu = a * f0 / SQRT(1-e2*sinLat*sinLat)                 // transverse radius of curvature
    VAR rho = a * f0 * (1 - e2) / POWER(1-e2*sinLat*sinLat, 1.5) // meridional radius of curvature
    VAR eta2 = nu/rho - 1
    VAR tanLat = TAN(lat)
    VAR tan2lat = tanLat * tanLat
    VAR tan4lat = tan2lat * tan2lat
    VAR tan6lat = tan4lat * tan2lat
    VAR secLat = 1 / cosLat
    VAR nu3 = nu * nu * nu
    VAR nu5 = nu3 * nu * nu
    VAR nu7 = nu5 * nu * nu
    VAR vii = tanLat / (2 * rho * nu)
    VAR viii = tanLat / (24 * rho * nu3) * (5 + 3*tan2lat + eta2 - 9*tan2lat*eta2)
    VAR ix = tanLat / (720 * rho * nu5) * (61 + 90*tan2lat + 45*tan4lat)
    VAR x = secLat / nu
    VAR xi = secLat / (6 * nu3) * (nu/rho + 2*tan2lat)
    VAR xii = secLat / (120 * nu5) * (5 + 28*tan2lat + 24*tan4lat)
    VAR xiia = secLat / (5040 * nu7) * (61 + 662*tan2lat + 1320*tan4lat + 720*tan6lat)
    VAR de = easting - e0
    VAR de2 = de * de
    VAR de3 = de2 * de
    VAR de4 = de2 * de2
    VAR de5 = de3 * de2
    VAR de6 = de4 * de2
    VAR de7 = de5 * de2
    VAR final_lat = lat - vii*de2 + viii*de4 - ix*de6
    VAR final_lon = lon0 + x*de - xi*de3 + xii*de5 - xiia*de7
return final_lat * radToDeg & "," & final_lon * radToDeg

eyJrIjoiNDIxMTgwOGQtOTI3NC00YWRmLWIyZDUtYjVhZGE1OTRmZWZlIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

MColey · ‎03-30-2024

Both of these are great, they give the same output so very close but not quite there!

Tigersqueeze · ‎09-11-2023

Oh my goodnight what an amazing piece of script!! Used this in a table I've got for locations in the UK (in a particular area) and apart from a few anomalies (out of thousands) it worked really well. Thank you!

whisp1986 · ‎03-07-2023

Great conversion - in testing it seems to be slightly out with BNG (using the below conversion to PowerQuery) in line with the original SQL.

let LatLongConversion = (E, N, Conv) =>

    let  
        northing = N,
        easting = E,

        radToDeg = 180 / Number.PI,
        degToRad = Number.PI / 180,

        a    = 6377563.396,
        b    = 6356256.909,  // Airy 1830 major & minor semi-axes
        f0   = 0.9996012717, // NatGrid scale factor on central meridian
        lat0 = 49 * degToRad,
        lon0 = -2 * degToRad, // NatGrid true origin
        n0   = -100000.0,
        e0   = 400000.0,       // northing & easting of true origin, metres
        e2   = 1 - (b*b)/(a*a), // eccentricity squared
        n    = (a - b) / (a + b),
        n2   = n * n,
        n3   = n * n * n,

        m = 0,
        lat1 = (northing-n0-m)/(a*f0) + lat0,
        ma1 = (1 + n + (5/4)*n2 + (5/4)*n3) * (lat1 - lat0),
        mb1 = (3*n + 3*n*n + (21/8)*n3) * Number.Sin(lat1-lat0) * Number.Cos(lat1+lat0),
        mc1 = ((15/8)*n2 + (15/8)*n3) * Number.Sin(2*(lat1-lat0)) * Number.Cos(2*(lat1+lat0)),
        md1 = (35 / 24) * n3 * Number.Sin(3*(lat1-lat0)) * Number.Cos(3*(lat1+lat0)),
        m1 = b * f0 * (ma1 - mb1 + mc1 - md1), // meridional arc

        lat2 = (northing-n0-m1)/(a*f0) + lat1,
        ma2 = (1 + n + (5/4)*n2 + (5/4)*n3) * (lat2 - lat0),
        mb2 = (3*n + 3*n*n + (21/8)*n3) * Number.Sin(lat2-lat0) * Number.Cos(lat2+lat0),
        mc2 = ((15/8)*n2 + (15/8)*n3) * Number.Sin(2*(lat2-lat0)) * Number.Cos(2*(lat2+lat0)),
        md2 = (35 / 24) * n3 * Number.Sin(3*(lat2-lat0)) * Number.Cos(3*(lat2+lat0)),
        m2 = b * f0 * (ma2 - mb2 + mc2 - md2), // meridional arc

        lat3 = (northing-n0-m2)/(a*f0) + lat2,
        ma3 = (1 + n + (5/4)*n2 + (5/4)*n3) * (lat3 - lat0),
        mb3 = (3*n + 3*n*n + (21/8)*n3) * Number.Sin(lat3-lat0) * Number.Cos(lat3+lat0),
        mc3 = ((15/8)*n2 + (15/8)*n3) * Number.Sin(2*(lat3-lat0)) * Number.Cos(2*(lat3+lat0)),
        md3 = (35 / 24) * n3 * Number.Sin(3*(lat3-lat0)) * Number.Cos(3*(lat3+lat0)),
        m3 = b * f0 * (ma3 - mb3 + mc3 - md3), // meridional arc

        lat4 = (northing-n0-m3)/(a*f0) + lat3,
        ma4 = (1 + n + (5/4)*n2 + (5/4)*n3) * (lat4 - lat0),
        mb4 = (3*n + 3*n*n + (21/8)*n3) * Number.Sin(lat4-lat0) * Number.Cos(lat4+lat0),
        mc4 = ((15/8)*n2 + (15/8)*n3) * Number.Sin(2*(lat4-lat0)) * Number.Cos(2*(lat4+lat0)),
        md4 = (35 / 24) * n3 * Number.Sin(3*(lat4-lat0)) * Number.Cos(3*(lat4+lat0)),
        m4 = b * f0 * (ma4 - mb4 + mc4 - md4), // meridional arc

        lat = (northing-n0-m4)/(a*f0) + lat4,

        cosLat = Number.Cos(lat),
        sinLat = Number.Sin(lat),
        nu = a * f0 / Number.Sqrt(1-e2*sinLat*sinLat),                 // transverse radius of curvature
        rho = a * f0 * (1 - e2) / Number.Power(1-e2*sinLat*sinLat, 1.5), // meridional radius of curvature
        eta2 = nu/rho - 1,
        tanLat = Number.Tan(lat),
        tan2lat = tanLat * tanLat,
        tan4lat = tan2lat * tan2lat,
        tan6lat = tan4lat * tan2lat,
        secLat = 1 / cosLat,
        nu3 = nu * nu * nu,
        nu5 = nu3 * nu * nu,
        nu7 = nu5 * nu * nu,
        vii = tanLat / (2 * rho * nu),
        viii = tanLat / (24 * rho * nu3) * (5 + 3*tan2lat + eta2 - 9*tan2lat*eta2),
        ix = tanLat / (720 * rho * nu5) * (61 + 90*tan2lat + 45*tan4lat),
        x = secLat / nu,
        xi = secLat / (6 * nu3) * (nu/rho + 2*tan2lat),
        xii = secLat / (120 * nu5) * (5 + 28*tan2lat + 24*tan4lat),
        xiia = secLat / (5040 * nu7) * (61 + 662*tan2lat + 1320*tan4lat + 720*tan6lat),
        de = easting - e0,
        de2 = de * de,
        de3 = de2 * de,
        de4 = de2 * de2,
        de5 = de3 * de2,
        de6 = de4 * de2,
        de7 = de5 * de2,
        final_lat = lat - vii*de2 + viii*de4 - ix*de6,
        final_lon = lon0 + x*de - xi*de3 + xii*de5 - xiia*de7,
        //return final_lat * radToDeg & "," & final_lon * radToDeg
        output_lat = final_lat * radToDeg,
        output_lon = final_lon * radToDeg,

        Conversion_Output = if Conv = "lat" then output_lat else if Conv = "long" then output_lon else null

    in Conversion_Output

in LatLongConversion

Join us at the 2025 Microsoft Fabric Community Conference

Northings and Eastings to Latitude and Longitude

Northings and Eastings to Latitude and Longitude