Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
spandy34
Post Patron
Post Patron

Converting Easting and Northings to Longitude and Latitude in DAX

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.  

 

spandy34_0-1673434409455.png

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

@Greg_Deckler 

 

3 REPLIES 3
Greg_Deckler
Super User
Super User

@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! 🙂


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.