As most of you know, longitude and latitude values can be specified in various ways. They can be given in degrees, minutes, and seconds (DMS) like 9°25'49.5677" N. Or they can be provided as a decimal degree like 9.430435.
MapInfo Pro works with decimal degrees so you will have to convert DMS values into decimal degrees to use these for creating points, as an example.
You can convert your DMS values to decimal degrees using this formula: decimal degree = D + (M + (S / 60) / 60).
Oh, and don't forget to handle the quadrant too. That's the last character in the string: N. That character can be one of two values for longitude values and one of two values for latitude values too. W or E and N or S. If the longitude value specifies W for West, the decimal degrees must be negative, and similar if the latitude value specifies S for South, the decimal degrees must be negative.
The Decimal / DMS Converter tool allows you to convert single values or a column of values between DMS and decimal degrees.
Last week our support team got a call about a different form of longitude/latitude values: Degrees and decimal minutes. They would look like: 53°31.9832' N. Here is a look at some of the values in the columns Lat and Long.
The formula from above still holds true, but we can simplify it to this: decimal degree = D + (M / 60). As the seconds already have been merged with the minutes to form a decimal minute, we can remove these from the formula.
To make it easier for me and you to understand, I have split the process into 3 steps:
- Extract the degree from the string
- Extract and add the decimal minute to the degree
- Handle the quadrant
As the first value in the string is the degree, we can convert the string to a number and end up with the numeric value of the degree. You can use the Val() function to get the numeric value from a string.
Update Observations
Set Latitude = Val(Lat)
, Longitude = Val(Long)
I use the SQL window to execute the Update statements and that will look like this:
The results have been written to the Longitude and Latitude columns as you can see below.
The next step is the tricky one as we need to extract the decimal minute value from within the string. We can see a degree symbol in front of the value and a minute symbol after the value. We can find the position of these two strings, and extract the value in between.
We will use the InStr() function to find the position of the strings and the Mid$() function to extract the value and just like we did with the degree we will use the Val() function to convert the value to a numeric value that we will add to the existing value in the Latitude and Longitude columns.
Once extracted, we must divide it by 60 to get a decimal-degree value.
Here is the expression.
Update Observations
Set Latitude = Latitude + (Val(Mid$(Lat, InStr(1, Lat, "°") + 1, InStr(1, Lat, "'") - 1)) / 60)
, Longitude = Longitude + (Val(Mid$(Long, InStr(1, Long, "°") + 1, InStr(1, Long, "'") - 1)) / 60)
Here you can see how it looks in the SQL window combined with the first expression
Here are the results after running the first two Update statements on the data.
In the final step, we need to ensure that the value will be negative if the last character is S for the latitude values or W for the longitude values.
We will use the Right$() function to extract the first character from the end of the string. We will use the inline If function IIf() to test the value of that character.
This is the final expression:
Update Observations
Set Latitude = Latitude * IIf(Right$(Lat, 1) = "S", -1, 1)
, Longitude = Longitude * IIf(Right$(Long, 1) = "W", -1, 1)
Below you can see it in combination with the first two expressions in the SQL window.
Here are the final decimal degree values ready for further use in MapInfo Pro.
You can also do it all in a single statement:
Update Observations
Set Latitude = (Val(Lat) + (Val(Mid$(Lat, InStr(1, Lat, "°") + 1, InStr(1, Lat, "'") - 1)) / 60)) * IIf(Right$(Lat, 1) = "S", -1, 1)
, Longitude = (Val(Long) + (Val(Mid$(Long, InStr(1, Long, "°") + 1, InStr(1, Long, "'") - 1)) / 60)) * IIf(Right$(Long, 1) = "W", -1, 1)
In the SQL window, it would look like this:
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------