Geocoding with Tableau Prep Builder and Here API

I have a list of customer addresses that I want to plot on a map in Tableau, but…

Country,City,Street
Sweden,Stockholm,Kungsgatan 1
Denmark,Copenhagen,Nyhavn 5
Finland,Helsinki,Runeberginkatu 2
Norway,Oslo,Stranden 15

As you already might know Tableau has built-in geocoding down to the zip/postcode level but it can not resolve a street address on a map. To do this you can use the Python integration to call an external service to resolve a street address to a coordinate.

Tableau Prep Builder introduced the integration with Python and R back in 2019.3 and in this article, I want to demonstrate how to leverage that integration to solve my problem.

To execute Python code in Tableau you have to connect to an instance of TabPy. You can install TabPy by running the PIP command:

pip install tabpy

Many of my colleagues and customers like to use the popular Google geocoding API (my colleague Alex Eskinasy wrote a great article on how to use it with Prep Builder) but in this article, I will be using the Here API (sign up here).

Let’s create the Flow

1. Start by opening Prep Builder

If you do not have Prep Builder go ahead and download it from here

2. Connecting to your data source

Click Connect to Data (or the + sign Right of Connections)

3. Select your datasource type (I’m have a .csv file) so I will be using Text file. Download my sample file from here

4. Browse to your file or connect to your database.

5. Drag the “adresser” table into the canvas (it will be added automatically if you only have one table)

6. Add a Clean step to preview your data.

The preview in Step 1 will look something like this:

Preview

The Python Script without Prep Builder

I wrote the following python script that connects to the Here API. It translates the combination of Country, Street, Street address and returns a set of coordinates (Longitude, Latitude) for the location.

I start by importing the following packages

import herepy
import pandas as pd
import json
from tabpy_client import Client

Connect to the GeocoderApi and define my API key

geocoderApi = herepy.GeocoderApi(< API KEY >)

Define Country, City and Street

Country = 'Sweden'
City = 'Stockholm'
Street = 'Kungsgatan 1'

Combine Country, City and Street Adress to a string and submit it to the geocoderAPI

adress = Street + ',' + City + ',' + Country
getCoordinates = geocoderApi.free_form(adress)
json_data = json.loads(str(getCoordinates))

Lets print json_data to see what we are getting back from the API

print(json_data)
{'items': [{'access': [{'lat': 59.33617, 'lng': 18.07177}], 'address': {'city': 'Stockholm', 'countryCode': 'SWE', 'countryName': 'Sweden', 'county': 'Stockholm', 'district': 'Norrmalm', 'houseNumber': '1', 'label': 'Kungsgatan 1, SE-111 43 Stockholm, Sweden', 'postalCode': '111 43', 'state': 'Stockholm County', 'stateCode': 'AB', 'street': 'Kungsgatan'}, 'houseNumberType': 'PA', 'id': 'here:af:streetsection:pEENiKYF90rhPW3XBWTzQC:CgcIBCDXr7JnEAEaATE', 'mapView': {'east': 18.07355, 'north': 59.33697, 'south': 59.33517, 'west': 18.07003}, 'position': {'lat': 59.33607, 'lng': 18.07179}, 'resultType': 'houseNumber', 'scoring': {'fieldScore': {'city': 1.0, 'country': 1.0, 'houseNumber': 1.0, 'streets': [1.0]}, 'queryScore': 1.0}, 'title': 'Kungsgatan 1, SE-111 43 Stockholm, Sweden'}]}

The information that I’m looking for is under {‘items’: [{‘access’: and to get that I will I can navigate to it using the example below :

print(json_data['items'][0]['access'])
{'lat': 59.33617, 'lng': 18.07177}

Great we have the coordinates, lets translate this into a Python code that we can use in Prep Builder

The python script in the context of Prep Builder

Tableau Prep send data to the Script module as a pandas dataframe, so we will have to import the dataframe and loop over it to get the columns that we want to send to the Here API for translation row by row .

In the script I start by import my

7. Copy the code, add your API key and seve it to a local file (my example file is called Call_HereMaps_API_Prep.py)

Running the code in TabPy

TabPy is running locally on my Mac (you can install it on Windows/Linux on dedicated hardware) and I need to have the pandas and the herepy package installed on my machine or the machine where TabPy is running to be able to execute the python code successfully.

To install the Pandas and herepy packages run the following command:

pip install herepy pandas

Now that we have the script in place we can add a Script module to our Prep Builder flow.

8. Add the Script module

9. Select the Script module in the flow, under Settings select Tableau Python and configure the connection to your TabPy server (in my case it is 127.0.0.1 port 9004)

10. Browse to the Python script that we created earlier

11. Type in the name of the Python Function that you created in the Python script. You can find the name of the function in row 5 of my script.

12. Add a new Clean Step to your Flow

9. Select the Clean step (Step2)

Once the Flow has completed Tableau Prep Builder will add two additional fields (Longitude and Latitude) to the original dataset and the output will look something like this.

Congratulations, you have successfully translated a list of addresses to coordinates and you can now map all your customer's home addresses on a Tableau map.

One last step

Lets add an Output step and write the new dataset back to a new csv file or to a Tableau Exract, Excel file, Snowflake, BigQuery, Redshift…