UK - Hull - Easting and Northing to Lat Long Conversion within 2 Metres

[p208134]

Hi,

I have utilised code from this thread:

Solved: Convert Eastings and Northings to Lat Long in Powe... - Microsoft Fabric Community

To create dax code that you can implement that will convert easting and northing into lat long within 1.6 metres (at max distance). I understand converting first is the smart idea but if you want to push easting and northing data into PowerBI and than refresh to let it do the conversion for you. This worked for me.

I usually click on my datasheet and click new column then enter the Lat part of data than new column and the long side - editing the Var Northing/Easting as appropriate.

PBILat2 =
VAR northing = Sheet4[job_northing]
VAR easting = Sheet4[job_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 lat = (northing-n0-m)/(a*f0) + lat0
VAR ma = (1 + n + (5/4)*n2 + (5/4)*n3) * (lat - lat0)
VAR mb = (3*n + 3*n*n + (21/8)*n3) * SIN(lat-lat0) * COS(lat+lat0)
VAR mc = ((15/8)*n2 + (15/8)*n3) * SIN(2*(lat-lat0)) * COS(2*(lat+lat0))
VAR md = (35 / 24) * n3 * SIN(3*(lat-lat0)) * COS(3*(lat+lat0))
VAR m1 = b * f0 * (ma - mb + mc - md) // meridional arc
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 lat1 = lat - vii*de2 + viii*de4 - ix*de6
VAR lon1 = lon0 + x*de - xi*de3 + xii*de5 - xiia*de7
return (lat1 * radToDeg) + 0.002975899

/////////////////////

PBILon2 =
VAR northing = Sheet4[job_northing]
VAR easting = Sheet4[job_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 lat = (northing-n0-m)/(a*f0) + lat0
VAR ma = (1 + n + (5/4)*n2 + (5/4)*n3) * (lat - lat0)
VAR mb = (3*n + 3*n*n + (21/8)*n3) * SIN(lat-lat0) * COS(lat+lat0)
VAR mc = ((15/8)*n2 + (15/8)*n3) * SIN(2*(lat-lat0)) * COS(2*(lat+lat0))
VAR md = (35 / 24) * n3 * SIN(3*(lat-lat0)) * COS(3*(lat+lat0))
VAR m1 = b * f0 * (ma - mb + mc - md) // meridional arc
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 lat1 = lat - vii*de2 + viii*de4 - ix*de6
VAR lon1 = lon0 + x*de - xi*de3 + xii*de5 - xiia*de7
return (lon1 * radToDeg) - 0.001579311

[Les_Lew]

Excellent, works perfectly, thank you so much, been searching for a solution like this for UK data for ages.

[samc_26]

Genius thank you! I just tried a load of other complicated ways for UK coordinates and none of them worked except this one!