5 Replies Latest reply on Jan 29, 2017 8:17 PM by jbrown

    Convert Longitude/Latitude formats


      I have a series of coordinates in degree/decimal minute format (30,19.32141120N, 86,8.21221140W) that I am trying to convert into Decimal format. I have put together the following to convert the latitude


      Left ( Latitude ; 2 )+(Middle ( Latitude ; 4 ; 8 ))/60


      and it appears to work without a problem.


      My problem arises with the longitude. In the example above, there are only two digits (86) to left of the comma; however, if you go far enough west, that becomes three digits.  So my formula:


      Left ( Longitude;  2 )+(Middle ( Longitude ; 4 ;8 ))/60


      only works on the two-digit format.


      I don't know enough about programming to do an if, this, then, that-type thing. My inelegant solution is to simply perform the calculation again, starting with the fifth character.


      Is it possible to do what I want? Could someone point me in the right direction?


      Again, many thanks. I always appreciate the help given.

        • 1. Re: Convert Longitude/Latitude formats


          Have you explored the Position function?

          With this, you can get the position of the comma and take the digits before that place.


          if: 86,  . . .


          using this in your calc above, you could do


          Left (Longitude ; Position ( Longitude ; "," ; 1 ; 1) - 1) . . .


          EDIT: I forgot 2 parameters in the position function.

          I'm doing the -1 because the function returns the position of the text. So it would return 3. You want the first two digits.


          For a 3 digit number the comma would be in the 4th place, so it would do Left ( Longitude ; 3)


          I'm curious about the calculation. I googled lat/long convert to decimal and saw one just a bit different: Convert Latitude/Longitude to Decimal


          If you have FIleMaker Pro Advanced, check out this custom function you can add to your solution. Have you used custom functions before? Here's a good basic info about those: Using custom functions (FileMaker Pro Advanced)

          • 2. Re: Convert Longitude/Latitude formats

            There are many ways as always, so I write pedantic one.


            Evaluate ( Substitute ( Latitude ; [ "," ; "+" ] ; [ "N" ; "/ 60" ] ; [ "S" ; "/ 60" ] ) )


            This make




            then evaluate.


            Hmm, after I describe it, seems not so pedantic...


            OTOH, why do you use 8 in Middle function? There are more digits.

            • 3. Re: Convert Longitude/Latitude formats

              Thanks for the quick response!


              The format I am trying to convert from is degrees, decimal minutes. The conversion you saw is from degrees, minutes and seconds. I just prefer working with the decimal degrees; the other formats just seem too "fussy."


              I think I've got something that will work.


              Left (Longitude ; Position ( Longitude ; "," ; 1 ; 1) - 1) + (Middle (Longitude ; Position ( Longitude ; "," ; 1 ; 1)  +1; +8))/60


              I've got to look at it more closely, but I think it will work. Never would have figured that out on my own!


              Many, many thanks. I really appreciate your effort.

              • 4. Re: Convert Longitude/Latitude formats

                I was just experimenting with different values, trying to get something to work. I just started with 8 and was pleasantly surprised that it looked promising. I shall have to revise that number before I am finished.


                I shall try your solution as well. It is certainly much shorter than mine!


                Thank you very much.

                • 5. Re: Convert Longitude/Latitude formats


                  I'm glad this might work for you.


                  You might want to consider the Let format as well. These calcs can get a bit long and hard to read. The Let statement lets us define variables at the beginning and then use them throughout. The advantage of this is that FileMaker only does the calculating function (such as Position or Get () or Left()) once and uses the value throughout. And you can make the variables shorter than the actual field name.


                  So this would work as well:


                  Let ([


                  _Lon = YourTable::Longitude;

                  _Pos = Position (_Lon ; ",";1;1);

                  _Deg = Left (_Lon ; _Pos - 1);

                  _Min = Middle (_Lon ; _Pos + 1) ; 1; 8)




                  (_Deg + _Min) / 60