Some of my IoT projects report location data with messages that looks like this;
{
"time": "20181007T071731Z",
"kph": 0.01852,
"alt": 140.1,
"satellites": 11,
"hdop": 75,
"pos": [
"47.64",
"-122.36"
],
"age": 379,
"chk_f": 9,
"chk_s": 46175,
"fix": 10735
}
Notice how the co-ordinates are returned as an array of two strings where each string contains a decimal degrees value.
It’s clear what the data means, but for Amazon QuickSight to plot it on a map, it needs to have this coordinate expressed in the correct type – in this case a Geo coordinate.
As I mentioned before, we can use a QuickSight calculated field to solve this problem. This time the formula we need is a little more complex but in essence we are going to split the string into 2 and use one part for a Latitude Geo Coordinate and the other for a Longitude Geo Coordinate.
Here’s the calculated field formula for the Latitude
parseDecimal(split(replace(replace({pos},"[",""),"]",""),",",1))
And here’s the almost identical one for the Longitude (which uses the second array value rather than the first)
parseDecimal(split(replace(replace({pos},"[",""),"]",""),",",2))
These calculated fields will be numbers to start with, we still need to tell QuickSight that we want to treat these as coordinates suitable for mapping, so use the edit data type feature to convert them to the appropriate Geo type like this.
Once you have Latitude and Longitude fields, you’ll be able to visualise them on the map charts built in to QuickSight.