Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a data set which contains Easting and Northings and I need to create columns for Longitude and Latitude. I got a brilliant example of the DAX (See codes for the columns below) but as you will see from the picture below, the locations of the Latitude and Longitude are out by a couple of hundred metres.
The Easting and Northings fields are whole number format and the Longitude and Latitude are Text
I am new to DAX so please bear with me.
Can anyone help please.
Latitude Longitude =
VAR northing = [Northing]
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 & "," & lon1 * radToDeg
----------------------------------------------------------------------------------------------------------------------------------------
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
----------------------------------------------------------------------------------------------------------------------------------------
Latitude = LEFT([Latitude Longitude 2],SEARCH(",",[Latitude Longitude 2])-1)
----------------------------------------------------------------------------------------------------------------------------------------
Longitude = RIGHT([Latitude Longitude 2],LEN([Latitude Longitude 2]) - SEARCH(",",[Latitude Longitude 2]))
@amitchandak @tamerj1 @danextian @goncalogeraldes @Bryn-MH
@spandy34 Hard to say exactly, as I stated in my post, Northings and Eastings to Latitude and Longitude - Microsoft Power BI Community, I don't know if the formula is accurate outside of the fact that it seemed to return the correct values as in the original article where I got the formula. There are all kinds of possibilities around rounding and other things that might throw things off a bit. I'll try to take a look and see where things may have gotten thrown off although I'm not even sure that it is possible to convert between the two with pinpoint accuracy.
Honestly, I'm more shocked that somebody is actually using that code rather than the fact it is off by a few hundred meters! 🙂
You would be making quite a few local authority officers happy if you could resolve this ... no pressure 😅
Honestly @Greg_Deckler its great and we are using it in our local authority for area information but I need to look at propety or at least the correct road so would be great if we could sort. I know a colleague in a neighbouring local authority also has the same problem so we are excited to see if this can be resolved.
Im only starting out on DAX and my knowledge is limited but your example was easy to follow so a big thank you.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |