Using Coordinate Data in Degrees (DMS) Format in Tableau

Have you ever received a spatial data set that you wanted to visualize in Tableau, only to find out the coordinates looked like this: 50°07'01.9"N 8°40'20.8"E If so, or if you’re just generally interested in geographic data and Tableau, this post is for you.

There’s three ways to use geographic data in Tableau:

  1. Import a spatial file (.shp, .tab, .kml, or .geojson – this was introduced in version 10.3)
  2. Use data that Tableau can geocode for you on the fly – by default Tableau knows the geographic roles of country, state, county, city, zip code, aiport code, European NUTS, and a few US-specific units; but you can always introduce your own custom geographic role
  3. Load a data set with X/Y or Longitude/Latitude information – please make sure they’re referring to WGS84 (EPSG 4326); if you don’t know what that is, just try out loading them and see if they fit

That last option is exactly what we’re going to do here. The only problem is that Tableau can’t deal with a latitude of 50°07'01.9"N but rather expects 50.117202. They both refer to the same place on earth (see if you can find out, which example location I’m using here…), but while the latter is the decimal way of writing this coordinate, the former is in the degrees format – often also referred to as DMS which stands for degrees, minutes, seconds. The coordinate can hence also be read as 50 degrees 7 minutes 1.9 seconds north. This is one of those occurrences where seconds are used to describe something different than time: here it’s fractions of an angle. (Another such case being the unit “parsec”, which is used for measuring distances, not time – in doubt ask Han Solo…) Here’s another bit of useless information for your next trivia quiz: this way of subdividing numbers by time units is also called sexagesimal system, “sexagesimal” referring to the number 60: one hour/degree consists of 60 minutes, which in turn consists of 60 seconds. Actually, knowing about this fact will help us understand how to convert DMS coordinates to decimal coordinates which can then be used in Tableau.

The formula to get from DMS to decimal is really easy and straight-forward (for details refer to Wikipedia):

D_{dec} = D + \frac{M}{60} + \frac{S}{3600}

But how can we implement that in Tableau? The most complex part is actually not performing the math but how to parse D, M, and S out of the DMS string. Of course we resort to our trusty friends the regular expressions here!

The regex to extract D, M, and S out of a DMS string like the one I used above looks like this:

(\d*)°(\d*)"([\d\.]*)'([NS])

OK, so what’s going on there? Firstly, we use the brackets to mark the bits we want to extract: the actual numbers (D, M, and S), and also the indicator for the northern and southern hemisphere in the end.

When putting this together with the above mentioned formula into Tableau, the following two monsters evolved (but they’re cute monsters, not scary at all):

Longitude:

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)

Latitude:

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)

Why are they so complex, you ask? Well, firstly I wanted to accommodate for the fact that sometimes people introduce whitespace into the DMS coordinates (e.g. making it 50° 07' 01.9" N) – these formulae account for that. Secondly, using characters such as " and ' in string operations is always a mine field, so I went ahead and replaced them with a and b inside of the formulae. Lastly we’re using the hemisphere indicator to assign the decimal coordinates to the correct positive (north of the equator; east of the prime meridian) or negative (south of the equator; west of the prime meridian) number range.

And there you go! Using DMS coordinates in Tableau! Is it pretty? No. Is it affecting your performance? Probably. I’d strongly suggest to do this DMS-to-decimal conversion somewhere along your pre-Tableau ETL process or at least materialize the results into a Tableau extract. Let me know in the comments if that was helpful or in case you know of a better way to do it!

Also, here’s a quick video that shows the steps necessary to use the DMS coordinates in Tableau. Note how it is necessary to explicitly tell Tableau not to interpret the " as text delimiter in the first few seconds of the clip:

6 Comments

  1. Hi, Thank you for an excellent article, this is almost exactly what I am looking for, but unfortunately the data I have is encoded like NN:NN:NN E and N. (ex. 61:22:00 N)
    I have tried to understand Regexp, but now I fear my brain might be broken…
    Could you tip me on how I can swap the expression to replace the : in my format?

    Reply

    1. Since you’re not dealing with ' and " in your original Latitude and Longitude strings the regex actually become a bit less contrived:

      Longitude:
      FLOAT(REGEXP_EXTRACT_NTH(REGEXP_REPLACE([Longitude], " ", ""), "(\d*):(\d*):([\d\.]*)([EW])", 1)) +
      FLOAT(REGEXP_EXTRACT_NTH(REGEXP_REPLACE([Longitude], " ", ""), "(\d*):(\d*):([\d\.]*)([EW])", 2)) / 60 +
      FLOAT(REGEXP_EXTRACT_NTH(REGEXP_REPLACE([Longitude], " ", ""), "(\d*):(\d*):([\d\.]*)([EW])", 3)) / 3600 *
      IIF(REGEXP_EXTRACT_NTH(REGEXP_REPLACE([Longitude], " ", ""), "(\d*):(\d*):([\d\.]*)([EW])", 4) == "E", 1, -1)

      Latitude:
      FLOAT(REGEXP_EXTRACT_NTH(REGEXP_REPLACE([Latitude], " ", ""), "(\d*):(\d*):([\d\.]*)([NS])", 1)) +
      FLOAT(REGEXP_EXTRACT_NTH(REGEXP_REPLACE([Latitude], " ", ""), "(\d*):(\d*):([\d\.]*)([NS])", 2)) / 60 +
      FLOAT(REGEXP_EXTRACT_NTH(REGEXP_REPLACE([Latitude], " ", ""), "(\d*):(\d*):([\d\.]*)([NS])", 3)) / 3600 *
      IIF(REGEXP_EXTRACT_NTH(REGEXP_REPLACE([Latitude], " ", ""), "(\d*):(\d*):([\d\.]*)([NS])", 4) == "N", 1, -1)

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*