Re: [libreoffice-users] [Calc] Convert GPS coords from DMS to decimals?

Den sön 22 nov. 2020 kl 11:22 skrev Johnny Rosenberg <gurus.knugum@gmail.com
> :

> Yes, it's very, very easy (when you know how to do it…). Those coordinates
> work exactly like time, so all you need to do is to format your input cells
> properly (if you care about looks) and multiply your input cells with 24
> (hours per day) in your output cells, because when working with time in
> Calc (or Excel or any other spreadsheet application), the result is in
> days, so 0,5 (or 0.5 if you use a period for the decimal symbol) means
> 12:00:00, 0,75 is 18:00:00 and so on.
>
> Follow this for a demo:
>
>    1. Highlight A1 and right click and click ”Format cells…”.
>    2. Click the ”Numbers” tab.
>    3. In the ”Category” field, select Time and in the format Field select
>    the line that looks something like ”13:37:46”.
>    4. Now, in the ”Format code” field, replace the colons (or whatever
>    they are in your case; it's language dependent) with degrees and the other
>    characters inside double quotes, and also make sure your hours symbol is
>    inside [], which means it won't flip over to 0 for greater numbers than 23.
>    In my case (Swedish), it looks like: "N"[TT]"°"MM"'"SS""". If English
>    (USA): "N"[HH]"°"MM"'"SS""". There is a field right under the Language
>    selection that gives you an example of what the result would look like. In
>    my case it reads: N13°37'46".
>    5. Hit ”OK”.
>    6. Repeat steps 1 to 5, but highlight B1 instead of A1 and replace "N"
>    with "E" in the ”Format code” field.
>    7. In A2, type: =A1*24
>    8. In B2, type: =B1*24 (or just auto-fill from A2 or even copy and
>    paste A2 to B2)
>    9. Highlight A2:B2 and increase the number of decimals using the
>    ”.00+” button or do it in the ”Format cells…” dialogue as before by
>    entering something like 0,0000000 in the ”Format code” field (or 0.0000000
>    if your decimal symbol is a period).
>    10. Now, in A1, type:
>    42:59:12.
>    Remember to treat the number as time rather than coordinates. Replace
>    ”:” with whatever is the appropriate time separator for your language.
>    11. In B1, type:
>    0:5:12
>
> I just read your question again and found that you had it the other way
around (east-west first and then north-south and using O instead of E), so
in your case then:
A1 format code: [HH]"°"MM"'"SS""""O"
B1 format code: [HH]"°"MM"'"SS""""N"
But this won't work, since Calc is not able to figure out all those double
quotes correctly, so my workaround is to use the ” double quote instead
(you can copy it from here, if you like, otherwise the UNICODE code is
U+201D. To match that I also use the corresponding ’ single quote, that is
U+2019, so in this case:
A1 format code: [HH]"°"MM"’"SS"”O"
B1 format code: [HH]"°"MM"’"SS"”N"
You can copy the whole thing from above, of course (and then replace the
letters to what's correct in your selected language).


A2=B1*24
B2=A1*24

The rest should be the same, I guess.



> When following my own instructions, here's what my cells look like:
> A1
> N42°59'12"
> B1
> E00°05'12"
> A2
> 42,9866666666667
> B2
> 0,086666666666667
>
> You could of course put the both together to a complete text string, but
> then you can't easily use them for further calculations. For instance, in
> A3, type (for a result with 8 decimals):
> =ROUND(A1*24;8) & ", " & ROUND(B1*24;8)
=ROUND(B1*24;8) & ", " & ROUND(A1*24;8)

> or, if you want to use the values in A2 and B2:
> =ROUND(A2;8) & ", " & ROUND(B2;8)

And you can, of course, also add the degree symbol if you like:
=ROUND(A1*24;8) & "°, " & ROUND(B1*24;8) & "°"


> Result (in my case):
> 42,98666667, 0,08666667

42,98666667°, 0,08666667° after adding the degree symbols.


> So, as you see, no advanced formulas are needed at all.
Still correct. ☺

> I hope there were not too many typos above.
>
>
> Kind regards
>
> Johnny Rosenberg
>
>
> Den sön 22 nov. 2020 kl 06:14 skrev Gilles <codecomplete@free.fr>:
>
> > Hello,
> >
> > I need to convert GPS coordinates from degrees+minutes+seconds (DMS) to
> > decimal, eg. 00°05'12"O 42°59'12"N → 42.98666667,-0.08666667
> >
> > https://postimg.cc/QH0q5qmn <https://postimg.cc/QH0q5qmn>
> >
> > Can Calc do this, or should I look elsewhere?
> >
> > Thank you.
> >
> >
> >
> > --
> > Sent from:
> > http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html
> >
> > --
> > To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
> > Problems?
> > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> > List archive: https://listarchives.libreoffice.org/global/users/
> > Privacy Policy: https://www.documentfoundation.org/privacy

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy