topic Re: Northings and Eastings to Latitude and Longitude in Quick Measures Gallery

Northings and Eastings to Latitude and Longitude

Greg_Deckler — Fri, 17 Sep 2021 13:32:27 GMT

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

Re: Northings and Eastings to Latitude and Longitude

whisp1986 — Tue, 07 Mar 2023 11:27:18 GMT

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

Re: Northings and Eastings to Latitude and Longitude

Tigersqueeze — Mon, 11 Sep 2023 09:54:44 GMT

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!

Re: Northings and Eastings to Latitude and Longitude

MColey — Sat, 30 Mar 2024 11:55:04 GMT

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