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

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

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)
or, if you want to use the values in A2 and B2:
=ROUND(A2;8) & ", " & ROUND(B2;8)

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

So, as you see, no advanced formulas are needed at all.

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