DMS Coordinates to Decimal Coordinates for Maps in Tableau
When dealing with geographic data in Tableau, you might encounter coordinates expressed in the DMS format i.e. for degrees, minutes, and seconds, like 50°07'01.9"N 8°40'20.8"E. However, Tableau expects decimal coordinates (e.g., 50.117202 to plot spatial coordinates for Latitude and Longitude respectively)
Let’s see how to convert DMS coordinates to decimal coordinates in Tableau. 🌐
- Latitude Conversion:
The DMS latitude coordinate 50°07'01.9"N can be converted to decimal degrees using this formula:
Decimal Latitude=Degrees+(60Minutes)+(3600Seconds). If the latitude direction is south, multiply the result by -1.
Here is the calculation in Tableau
FLOAT(REGEXP_EXTRACT_NTH(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE([Y], " ", ""), "'", "a"), '"', "b"), "(\d*)°(\d*)a([\d\.]*)b([NS])", 1)) +
FLOAT(REGEXP_EXTRACT_NTH(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE([Y], " ", ""), "'", "a"), '"', "b"), "(\d*)°(\d*)a([\d\.]*)b([NS])", 2)) / 60 +
FLOAT(REGEXP_EXTRACT_NTH(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE([Y], " ", ""), "'", "a"), '"', "b"), "(\d*)°(\d*)a([\d\.]*)b([NS])", 3)) / 3600 *
IIF(REGEXP_EXTRACT_NTH(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE([Y], " ", ""), "'", "a"), '"', "b"), "(\d*)°(\d*)a([\d\.]*)b([NS])", 4) == "N", 1, -1)2. Longitude Conversion:
For the DMS longitude coordinate 8°40'20.8"E, use a similar formula:
Decimal Longitude=Degrees+(60Minutes)+(3600Seconds). Again, adjust the sign based on the longitude direction (east or west).
Here is the calculation in Tableau
FLOAT(REGEXP_EXTRACT_NTH(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE([X], " ", ""), "'", "a"), '"', "b"), "(\d*)°(\d*)a([\d\.]*)b([EW])", 1)) +
FLOAT(REGEXP_EXTRACT_NTH(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE([X], " ", ""), "'", "a"), '"', "b"), "(\d*)°(\d*)a([\d\.]*)b([EW])", 2)) / 60 +
FLOAT(REGEXP_EXTRACT_NTH(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE([X], " ", ""), "'", "a"), '"', "b"), "(\d*)°(\d*)a([\d\.]*)b([EW])", 3)) / 3600 *
IIF(REGEXP_EXTRACT_NTH(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE([X], " ", ""), "'", "a"), '"', "b"), "(\d*)°(\d*)a([\d\.]*)b([EW])", 4) == "E", 1, -1)
After creating the calculation, set the Geographic Roles for the calculations as Latitude and Longitude respectively.
Here is a step by step tutorial of doing the same in Tableau.
Source: https://konstantingreger.net/using-coordinate-data-in-degrees-dms-format-in-tableau/
Thank you for reading. Connect with me on Twitter and LinkedIn, and check out my Tableau Public profile for more visualizations.
Vignesh Suresh
