6 Replies Latest reply on Mar 16, 2013 6:32 PM by radiosaigon

    GPS Coordinate conversions

      Greetings

       

      I am developing a database of GPS Coordinates for use in Aviation. During the course of my activities, I have gathered a considerable number of waypoints which, naturally enough, are most easily manipulated in the digital coordinate format of DD.dddddd. All of my waypoints are recorded in that format. However, in some circumstances, it is necessary that the coordinates be reported in DD MM.mmm -and therein lies the rub. I have successfully got my coordinates reporting N/S E/W within a calculated field, using (If ( LATITUDE>0 ; "N" ; "S" )) etc., but I'm having real trouble getting my head around how to do the rest! The mathermatics are easy... the whole degrees stay as whole degrees, everything after the decimal is multiplied by 60 to provide the MM.mmmm -it's just the getting there that I am having trouble with. How do I extract just the whole degrees from the coordinates, without rounding? How best do I extract only the information after the decimal so I can do the multiplication to provide the result I'm after?

       

      Of course, it would be tremendous if there was a way to convert coordinates bi-directionally! For the meantime though, I would appreciate any assistance offered to crack the immediate problem.

       

      Best regards

      RS

        • 1. Re: GPS Coordinate conversions
          jbante

          The Div and Mod functions come in handy for this sort of thing. How does this work for you:

           

          Let ( [

                    ~original = 123.456 ;

                    ~degrees = Div ( ~original ; 1 ) ;

                    ~minutes = Mod ( ~original ; 1 ) * 60 ;

                    ~decimal = ~degrees + ~minutes / 60

          ] ;

                    "Original:          " & ~original

                    & ¶ & "Degrees-Minutes:          " & ~degrees & "° " & ~minutes & "'"

                    & ¶ & "Decimal:          " & ~decimal

          )

          1 of 1 people found this helpful
          • 2. Re: GPS Coordinate conversions

            Many thanks jbante -I was definitely going in completely the wrong direction! The only (probably minor) bug I can see with the calculation is that it will not calculate negative coordinates accurately. I tend to spend a bit of time both sides of the Equator, so that's a bit of a game-changer ;-) How would I best calculate the negative Cardinals (S & W) within that structure?

            • 3. Re: GPS Coordinate conversions
              jbante

              Good catch! I'd say that's pretty important. The quick-and-dirty off-the-top-of-my-head solution is to detect negative values at the start and convert the math to positive. It feels like there ought to be a more straightforward way, but I need to get back to work.

               

              Let ( [

                        ~original = -123.456 ;

                        ~negative = ~original < 0 ;          // True (1) if value is negative

                        ~positive = Abs ( ~original ) ;

                        ~degrees = Div ( ~positive ; 1 ) ;

                        ~minutes = Mod ( ~positive ; 1 ) * 60 ;

                        ~decimal = ~degrees + ~minutes / 60 ;

                        ~decimal = If ( ~negative ; 0 - ~decimal ; /* Else */ ~decimal )

              ] ;

                        "Original:          " & ~original

                        & ¶ & "Degrees-Minutes:          "

                        & If ( ~negative ; "-" )

                        & ~degrees & "° " & ~minutes & "'"

                        & ¶ & "Decimal:          " & ~decimal

              )

              • 4. Re: GPS Coordinate conversions

                Again jbante, many thanks! Your "quick & dirty" looks to be doing a bang-up job, on 1st glance! I'll continue testing for a while across a range of coordinates, but it sure looks like the job!

                 

                Many thanks, have a good day at work ;-)

                 

                Regards

                RS

                • 5. Re: GPS Coordinate conversions

                  I've had a wee play with the formula jbante, thought you might perhaps be interested in the "Final" shape of it;

                   

                   

                  REFINED VERSION -"FINAL" SHAPE, INCLUDING CARDINALS!


                  LATITUDE:


                  Let ( [

                            ~original = LATITUDE ;

                            ~negative = ~original < 0 ;          // True (1) if value is negative

                            ~positive = Abs ( ~original ) ;

                            ~degrees = Div ( ~positive ; 1 ) ;

                            ~minutes = Mod ( ~positive ; 1 ) * 60 ;

                            ~decimal = ~degrees + ~minutes / 60 ;

                            ~decimal = If ( ~negative ; 0 - ~decimal ; /* Else */ ~decimal )

                  ] ;

                             If ( ~negative ; "S" ; "N")

                            & ~degrees & "° " & ~minutes & "'"

                          

                  )

                   

                  LONGITUDE:

                   

                  Let ( [

                            ~original = LONGITUDE ;

                            ~negative = ~original < 0 ;          // True (1) if value is negative

                            ~positive = Abs ( ~original ) ;

                            ~degrees = Div ( ~positive ; 1 ) ;

                            ~minutes = Mod ( ~positive ; 1 ) * 60 ;

                            ~decimal = ~degrees + ~minutes / 60 ;

                            ~decimal = If ( ~negative ; 0 - ~decimal ; /* Else */ ~decimal )

                  ] ;

                             If ( ~negative ; "W" ; "E" )

                   

                            & ~degrees & "° " & ~minutes & "'"

                          

                  )

                   

                  I've reduced it to displaying only the information I'm absolutely interested in at this point (the rest will come later!) and have it reporting the Cardinal directions now, in the correct sense!

                   

                  Again, thanks man. I wouldn't have gotten there without your assist!

                   

                  Regards

                   

                  RS

                  • 6. Re: GPS Coordinate conversions

                    Further to the above:

                     

                    The calculation has been updated again, with much assistance from deninger and now in addition to the above adds leading zeroes everywhere necessary with correct N,S,E,W Cardinals and apears in a very familiar format. I have to date tested it with ~400 waypoints, with complete success and accuracy. Any further updates will be posted here. So, the latest formulae are:

                     

                    LATITUDE:

                     

                    Let ( [

                              ~original = -LATITUDE ;

                              ~negative = ~original < 0 ;          // True (1) if value is negative

                              ~positive = Abs ( ~original ) ;

                              ~degrees = Div ( ~positive ; 1 ) ;

                              ~minutes = Mod ( ~positive ; 1 ) * 60 ;

                              ~roundedMinutes = Round ( ~minutes ; 5 ) ;

                              ~intMinutes = Int (~roundedMinutes);

                              ~decimalMinutePart = ( ~roundedMinutes - ~intMinutes) ;

                              ~decimalMinutePartAsText = Middle ( ~decimalMinutePart ; 2 ; 2 );

                              ~paddedDecimalMinutes = Left (~decimalMinutePartAsText & "00" ; 2);

                              ~paddedIntMinutes = Right ("00" & ~intMinutes ; 2);

                              ~resultMinutes = ~paddedIntMinutes & "." & ~paddedDecimalMinutes ;

                              ~decimal = ~degrees + ~minutes / 60 ;

                              ~decimal = If ( ~negative ; 0 - ~decimal ; /* Else */ ~decimal )

                     

                    ] ;

                     

                     

                               If ( ~negative ; "S" ; "N")

                              & Right ( "00" & ~degrees ; 2 ) & "° "

                              & ~resultMinutes & "'"

                    )

                     

                    and LONGITUDE:

                     

                    Let ( [

                              ~original = LONGITUDE ;

                              ~negative = ~original < 0 ;          // True (1) if value is negative

                              ~positive = Abs ( ~original ) ;

                              ~degrees = Div ( ~positive ; 1 ) ;

                              ~minutes = Mod ( ~positive ; 1 ) * 60 ;

                              ~roundedMinutes = Round ( ~minutes ; 5 ) ;

                              ~intMinutes = Int (~roundedMinutes);

                              ~decimalMinutePart = ( ~roundedMinutes - ~intMinutes) ;

                              ~decimalMinutePartAsText = Middle ( ~decimalMinutePart ; 2 ; 2 );

                              ~paddedDecimalMinutes = Left (~decimalMinutePartAsText & "00" ; 2);

                              ~paddedIntMinutes = Right ("00" & ~intMinutes ; 2);

                              ~resultMinutes = ~paddedIntMinutes & "." & ~paddedDecimalMinutes ;

                              ~decimal = ~degrees + ~minutes / 60 ;

                              ~decimal = If ( ~negative ; 0 - ~decimal ; /* Else */ ~decimal )

                     

                    ] ;

                     

                     

                               If ( ~negative ; "W" ; "E")

                              & Right ( "000" & ~degrees ; 3 ) & "° "

                              & ~resultMinutes & "'"

                    )

                     

                    Again, I would like to extend my grateful thanks to both jbante and deninger for their advice and hard work, making this possible.

                     

                    Regards

                    RS

                     

                    Message was edited by: radiosaigon -minor bug-fix.