Skip navigation
2020

This week there was a question on the Tableau Community forums about working with Google Plus codes in Tableau.  That made me wonder how difficult it would be to convert between lat/lon pairs and Google Plus codes using Tableau Prep.  It turns out to be pretty easy using the same general steps as I've used in other Prep geocoding / data conversion examples - you just need a data source with Google Plus codes or lat/lon pairs in Tableau Prep, to start up TabPy Server, and then run a simple Python script.  In this post I'll give a rundown on how to make it work.

Quick links to the important parts to making this all work:

 

 

And...if you are interested in other data conversion options, here are some more bits to read:

 

First - what are Google Plus codes?  Google Plus codes are just another method for encoding specific locations on the earth's surface.  Instead of using a latitude and longitude pair, or a place name (e.g., 'Seattle'), a Google Plus code uses a set of letters and numbers to identify the location.  For instance, the location of the Tableau headquarters can be identified in a number of ways - address, latitude and longitude, or with the Google Plus code of 84VVJMX6+2Q

 

 

 

Now, how do we work with them in Tableau Prep?  It's easy to convert between latitude and longitude to Google Plus codes using the Google Plus Codes API

 

The API provides an easy URL-based method for requesting conversion to or from Google Plus Codes.  You do not need a Google API key to do basic conversions!  You can just send in a basic request using your email address as an ID.  For instance:

https://plus.codes/api?address=84VVJMX6%2B2Q&email=fakeemail@gmail.com

 

returns the following with all sorts of interesting location data.

{

  "plus_code": {

  "global_code": "84VVJMX6+2Q",

  "geometry": {

  "bounds": {

  "northeast": {

  "lat": 47.647625000000005,

  "lng": -122.33800000000001

  },

  "southwest": {

  "lat": 47.64750000000001,

  "lng": -122.338125

  }

  },

  "location": {

  "lat": 47.64756250000001,

  "lng": -122.3380625

  }

  },

  "locality": {}

  },

  "status": "OK"

}

 

To use this in Tableau Prep, all you need is a data table, TabPy Server, and a small Python script.  Let's get rolling and build a Prep workflow and try it out!

 

We'll start with lat/lon to Google Plus code.  Add a dataset with some lat/lon values in Tableau Prep.  I'm going to use a test dataset that I created with both lat/lon and Google Plus codes so that I could use the same dataset for both examples.  I've put the file up on Github if you want to use it for your own testing.

 

 

To make our lives easy with wrestling the Python script, I'm going to add a new plus_code field to the table with a 'Clean' step (that way I don't just overwrite the existing plus_code field

 

I'm going to fill in a dummy value so that I know if new results were calculated as a result of running the python script:

Now it's time to check out the Python script and see how it's going to do its magic...

 

Grab the script from Github

 

Save it somewhere where you can find it again.

 

Take a look through to see if you need to edit any of the field names to match up with your dataset or Tableau Prep workflow... it's a simple script, I'll walk through it and highlight the parts that you might edit:

These are libraries that you need to make the script work.  If you don't already have them as part of your Python installation, get them.

Defines the function that you'll call from Tableau Prep.  You just tell Prep which function name to go to and magic happens

The Google Plus Codes API requires an email address.  Edit this line and put your email address in

 

 

This part walks through your input file (what Tableau Prep has sent over to the script) and grabs the values from your latitude and longitude fields.  In my sample data they are named 'lat' and 'lng' - if you are using other field names, change these in the script!

 

Once the values have been grabbed from the data, they are sent off to the Google Plus URL in line 37

 

Next the script sends the URL off and gets some data back

 

2020-01-17_12-07-11.jpg

 

If the result is good (status = 'OK') then the data is dropped into a field named 'plus_code2' (that was the name I used in the cleaning step in Prep when I made a dummy field for the results to go into...if you used a different name, edit this to reflect the name you chose).

 

And then it's done and you can run your script in Tableau Prep and check out the result.

 

How do you run the script in Prep???

 

First, Start TabPy Server! I forget this part all the time and wonder why things aren't working... Remember to start TabPy Server.  I use a windows machine, so I just open a command prompt, navigate to the location where I dropped TabPy-server and start it up.  I'm on a windows machine and see something like this:

In Tableau Prep, add a Script step

 

Point it to the script and the right function name:

 

Let the script run. 

 

Look at the results.  My result table has duplicates for the Plus Codes because my original table already had the values in it...but you can see the new values made it successfully into the plus_codes2 field!  Yay!

 

 

If you want to go back to lat and lon from the plus code, you can just use the other function in the Python file that I linked on Github.  That will go from a field of Google Plus codes and return TWO fields with the latitude and longitude.

 

Again, just make sure that the input column names and output column names match ones in your table in Tableau Prep, and then point your script to the right function

 

And that should be all there is to it!  Hopefully this works for you as well.

This blog post will walk you through creating hexagonal grids for use in Tableau to aggregate points into a nice, pretty, regular grid of hexagons.  I gave some examples of this in my TC talk with Kent Marten on “All Over the Map! Level Up Your Spatial Vizzes

 

Spatial aggregation can be useful for identifying general spatial patterns in point datasets.  Spatial aggregation allows you to count up the number of points that fall into discrete geographic regions and then map the counts of points in non-overlapping polygon regions.  Or, to put it more simply – we can make maps like this using a spatial intersection join in Tableau:

To make this type of map, all you need to do is join your original dataset with points to a spatial file with polygons

and then drop ‘number of records’ onto the polygons in Tableau:

But, in order to make a map like this you need to start with a spatial file that has the polygons you want to use on your map.  I often use a set of hexagons or squares for the polygons and I make them using QGIS – a free, open-source GIS program.

 

Here is how you can make a hexagonal grid (or a square grid) for use with the spatial intersection functionality in Tableau using QGIS.

 

Warning

If you are working with a relatively large geographic area, you will have map projection issues.  Please consider the distortion due to projection and think about whether a hex or square grid is appropriate for your analysis.  And then think about the fact that the data will be projected to web Mercator for display in Tableau. 

 

Want to know more?  Please check out ‘Shapes on a Plane: Evaluating the impact of projection distortion on spatial binning

 

Step 1: Install QGIS (download it here: http://qgis.org)

 

Step 2: Add the MMQGIS package (Read more about it here: About MMQGIS)

 

 

 

Step 3: Add your point dataset (or other dataset that might give you a good sense of how large you want for the extent of your hexagonal grid)

 

     Here is a help file on adding layers in QGIS: http://docs.qgis.org/2.8/en/docs/training_manual/introduction/preparation.html

    

     Here is a tutorial on adding csv files with lat/lon: https://www.qgistutorials.com/en/docs/importing_spreadsheets_csv.html

 

Step 4: Create a new grid layer using MMQGIS

 

Then you just need to define the polygons – here are the main steps:

 

1 – Select your geometry type.  I’m using Hexagons (Rectangles and Diamonds would also give you polygons)

 

2 – Define the X or Y spacing.  You only need to define one of these, the other will update automatically for hexagons. This is how ‘wide’ or ‘tall’ the hexagon will be in the project units or the layer units.  This is a very important element to note!  You need to know your dataset’s units in order to pick a size for your polygons that will make sense. 

 

I’ll give you two sets of instructions here – if the units in your file are reasonable for defining a polygon side (e.g., feet or meters), and if they aren’t (e.g., decimal degrees) and you need to change them.  The default units will be based on the units used in the coordinate system for your spatial file (e.g., if it’s in WGS84 they will be decimal degrees)

 

First option - Assuming the units given for spacing are reasonable:

 

For hexagons, define either the X or Y spacing.  The other will fill in automatically based on the number you type in.

 

If you want to define based on the side length of a hexagon, you just need to do a bit of math – for instance, if you have map units in meters and you want a 1000 meter hexagon side, then you can define the X-spacing as 1500.  The Y-spacing will fill in appropriately

 

Gratuitous graphic if you want to see why 1500 works for the x-spacing to define a hexagon with a 1000 unit side.

For rectangles, define both the X and Y spacing to whatever size you want the rectangle to be.  If you want it to be a square, use the same value for both X and Y.

 

If you are using Project Units, you may need to change to ‘Custom Extent’ to get the MMQGIS grid maker to fully recognize your projected coordinate system and not whatever the units are for the layer.  Just a minor glitch…

 

 

 

Second option - If you need to change the units:

Change the units to a projected coordinate system appropriate for your dataset (e.g., if you’re working with the US, the Albers Equal Area coordinate system for the conterminous US is a decent choice).  Something equal area is a good choice for generating grids for spatial aggregation.

 

To change the coordinate system go to Project -> Properties

Then pick your coordinate system:

Then go back and create the grid using the steps above (under the first option where we assume that the units are reasonable to begin with).  When you go to use these in the MMQGIS dialog for creating hexagon (or rectangle) grids, when you change the unit to ‘project units’ it might not update immediately.  Sometimes I have to change the Extent to ‘custom area’ before the unit change takes place. Fiddle with the extent and unit settings until it gets it right 

 

 

3 – Set the extent to define how much geographic area the polygons will cover.

 

The default is going to be the window extent, but you can change it to just be the extent of the datasets that you have in the view.  This just defines how large the geographic area is that the hexagons (or rectangles) will cover.

 

4 – Name the file and save it somewhere that you’ll be able to find it later.

 

Be smart and don’t do what I normally do, which is save the file somewhere cryptic and with a cryptic name (you know, the ‘hex_v6_final_reallyFinal.shp’ type naming scheme).

 

Click Apply, close the grid making dialog window.

 

Add a unique ID to each hexagon

To make it easier to work with the shapefile in Tableau, it helps to have an ID field that you can use with the polygons.  This is easy to add:

 

Right click on the name of your hex shapefile in the list of layers and open the attribute table

 

 

Open the field calculator:

 

Add a new field called ID (or whatever name you want to use) and use the calculation '$id'

 

If you want to clean up your attribute table, feel free to delete the left, bottom, right, and top attributes (I generally do this) - just click on the Delete Field button, select the fields you want to get rid of, and click okay.  Note that this will put your file in 'edit' mode, so you'll also need to save those edits - just click on the pencil icon in the attribute table to stop editing and save the edits

 

 

 

Now you should go enjoy your new grid in Tableau.  Just add the shapefile that you saved with a useful name and in a memorable location in step 4 above

 

Now you have some hexagons to use in a spatial join in Tableau!  Enjoy spatial aggregation of your points into cute (yet edgy) little hexagons.

(if you need it, here is the help file on how to join spatial files in Tableau)