maps with UTM coordinates instead of Latitude and Longitude

[aszpic]

Hello everyone,

Is there a way to use the map tool with the coordinates in UTM format instead of lat and long? The only thing I am aiming for is to show in the map a list of places and values that will be represented with the size of the circles.

Thanks in advance!

Ary

[KoenDelaere]

Thanks for the tip on how to use Power Query to use a function like this.

The conversion formulas themselves however seem to be inaccurate when comparing to online converters. I get better results with the formulas mentioned here:

https://stackoverflow.com/questions/2689836/converting-utm-wsg84-coordinates-to-latitude-and-longitu...

best regards

Koen

[Fortunato_INB]

Hello @aszpic,

I solved this issue by creating a function that transforms geographic coordinates (lat / long) into plane coordinates (UTM) in Power Query using the M language.

Step 1 (Create the role):
- Copy the code below.
- Go to Power Query in the "Home" tab and click "Enter Data" and click "OK".
- Click on the table that was just created, and click on "Advanced Editor".
- Paste the code and click "Done".

Step 2 (Invoke function):
- Select the table you want to transform the coordinates.
- In the "add column" tab, click "Invoke Custom Function".
- Select the function you created and inform the columns that are the coordinates UTM (north and east), Hemisphere (N for north and S for South), Central Meridian and click "OK".

Code (M language):

let
/*Fórmula para converter coordenadas UTM para Geográficas
Desenvolvido por: José Carlos Fortunato - fortunatojosecarlos@gmail.com*/
    UtmLatLong = (UTMeste as number,UTMnorte as number,Hemisferio as text,MeridianoCentral as number) =>
        let
        long0 = MeridianoCentral,
        Hem = Hemisferio,
        TextHem = Text.Upper(Hem),
        a = 6378137.0000,
        b = 6356752.3141,
        x = 500000 - UTMeste,
        y = UTMnorte,

        y1 = if TextHem = "S" or TextHem = "SOUTH" or TextHem = "SUL" then
        10000000 - y
        else y,

        M = y1 / 0.9996,
        e = Number.Sqrt(1 - Number.Power(b,2) / Number.Power(a,2)),
        mu = M / (a * (1 - Number.Power(e,2) / 4 - 3 * Number.Power(e,4) / 64 - 5 * Number.Power(e,6) / 256)),
        e1 = (1 - Number.Power((1 - Number.Power(e,2)),(1 / 2))) / (1 + Number.Power((1 - Number.Power(e,2)),(1 / 2))),

        j1 = (3 * e1 / 2 - 27 * Number.Power(e1,3) / 32),
        j2 = (21 * Number.Power(e1,2) / 16 - 55 * Number.Power(e1,4) / 32),
        j3 = (151 * Number.Power(e1,3) / 96),
        j4 = (1097 * Number.Power(e1,4) / 512),

        fp = mu + j1 * Number.Sin(2 * mu) + j2 * Number.Sin(4 * mu) + j3 * Number.Sin(6 * mu) + j4 * Number.Sin(8 * mu),

        e2 = Number.Power(e,2) / (1 - Number.Power(e,2)),
        c1 = e2 * Number.Power((Number.Cos(fp)),2),
        t1 = Number.Power(((Number.Sin(fp)) / (Number.Cos(fp))),2),
        r1 = a * (1 - Number.Power(e,2)) / Number.Power((1 - Number.Power(e,2) * Number.Power((Number.Sin(fp)),2)),(3 / 2)),
        n1 = a / Number.Power((1 - Number.Power(e,2) * Number.Power((Number.Sin(fp)),2)),(1 / 2)),
        d = x / (n1 * 0.9996),

        q1 = n1 * ((Number.Sin(fp)) / (Number.Cos(fp))) / r1,
        q2 = Number.Power(d,2) / 2,
        q3 = (5 + 3 * t1 + 10 * c1 - 4 * Number.Power(c1,2) - 9 * e2) * Number.Power(d,4) / 24,
        q4 = (61 + 90 * t1 + 298 * c1 + 45 * Number.Power(t1,2) - 3 * Number.Power(c1,2) - 252 * e2) * Number.Power(d,6) / 720,

        latrad = fp - q1 * (q2 - q3 + q4),

        lat = if TextHem = "S" or TextHem = "SOUTH" or TextHem = "SUL" then
            -1 * (latrad * (180 / Number.PI))
        else
            (latrad * (180 / Number.PI)),


        q5 = d,
        q6 = (1 + 2 * t1 + c1) * Number.Power(d,3) / 6,
        q7 = (5 - 2 * c1 + 28 * t1 - 3 * Number.Power(c1,2) + 8 * e2 + 24 * Number.Power(t1,2)) * Number.Power(d,5) / 120,

        longrad1 = (q5 - q6 + q7) / (Number.Cos(fp)),
        longrad2 = longrad1 * (180 / Number.PI),

        longi = if long0 < 0 then
            long0 - longrad2
        else
            long0 + longrad2

        in
        Table.FromRecords({[latitude = lat,longitude = longi]})
        
        //UtmLat = Table.AddColumn(UtmLatLong, "latitude", each List.First(UtmLatLong([este],[norte], "SUL", -45),1),type number),
        //UtmLong = Table.AddColumn(UtmLat, "longitude", each List.Last(UtmLatLong([este],[norte], "SUL", -45),1),type number)

in
UtmLatLong

Hope this helps!

[Fortunato_Jr]

Here is the link to the video explaining the steps

:

https://youtu.be/q8QYQpnSVWo?si=nyCMhfaFh4VI-hL3

[tfiglioli]

Good Morning Fortunato, 

Your Code definitely put me in the right direction. I am in NYC, do any of the numbers or language in the code have to be changed in order to get accurate lat long ?

[Anonymous]

Hi Fortunato,

I used a web API from National Grid which gave me the right lat long values from easting and northing values.

[Anonymous]

https://webapps.bgs.ac.uk/data/webservices/convertForm.cfm

[Flamas]

The values for the longitude are given in ranges 15 or 16, and for the UTM coordinates that I handle should be in -71, what is the reason for this error considering that I am in the S hemisphere and meridian 18?

[Fortunato_INB]

Hello @Flamas 

In the field that asks for Central Meridian, type in degrees (-75). You probably entered 18 which would be the UTM zone.

Note: check your UTM zone and central meridian.

Any other questions, you can talk

[Anonymous]

My UTM Zone is 32 so in the central Meridian Text box i have typed the corresponding value based on the url

https://gisgeography.com/central-meridian/

I am getting lat long values which are wrong. For e.g northin ,easting values below, hemisphere NORTH, MeredianCentral=9

169129

553569

I need to get lat=51.400409 long=0.206263 but I am getting wrong values. Not sure if it is becuase of meredian central

Screenshot attached

[Fortunato_INB]

Hi @Anonymous @

For these coordinates (lat = 51.400409 long = 0.206263) the Central Meridian is 3, because the "UTM zone" is 31, not 32.

I hope I was helpfull.

[Anonymous]

Hi Fortunato_INB,

I provide the following data to the invoked function e.g (eastin,northing,hemisphere,centralmeridian) values (190410,541450,NORTH,3)

I am getting the lat long (1.72,2.62) which is in the middle of the ocean not the U.K. If I use the website https://www.doogal.co.uk/LatLong.php get the correct lat long values.

Does the U.K need some kind of translation in the northing and easting values?

[Fortunato_INB]

@Anonymous The problem is in the unit of measurement. It should be in meters and not imperial. (North = 354700, East = 6066442).

[Anonymous]

Thanks Fortunato_INB for the info..  once last question what is the formula to convert imperial to metric.

I tried feet to meters but that was wrong. Converting what (from and to) units of measurements?

[mihaiem]

@Anonymous @ were you able to find a solution to convert OS UK Easting and Northings into Long Lat for use in powerbi? I've been strugling with this for quite a while but cannot find anything that I can successfully implement

[ocremetz]

Hi @mihaiem , There is a user error on your extension. it's @ocremedios instead of @ocremetz.

[mihaiem]

Oh, sorry, my bad. I've edited my comment ^_^

[v-shex-msft]

Hi @aszpic,

I don't think current map visual support utm format, you can try to write a formula to convert the Latitude and Longitude to utm format, then drag this column to tooltips field.

In addition, you can also submit this requirement to ideas.

Regards,

Xiaoxin sheng