Tableau Public is an easy-to-use and convenient tool for visualizing your ideas! Try creating cool visualizations using Prepper Open Data Bank (PODB) provided by truestar.
Today, I would like to share some tips for working with spatial data in Tableau Public × PODB.
Having trouble importing spatial data into Tableau Public?
PODB data is stored on Snowflake, so you can access it directly from Tableau or Alteryx! However, we cannot use live connection to external data from Tableau Public. Therefore, you need to download the data from Snowflake and then upload it to Tableau Public.
Snowflake has a feature that allows you to download data as text data such as CSV or TSV, so you don’t have to worry too much about it. However, when downloading spatial data or large capacity data, you need to make some small adjustments.
Two challenges to overcome
There are two challenges you may face.
[1st Challenge] When trying to download large-capacity data such as spatial data, it may not be possible to save the file due to capacity limitations.
[2nd Challenge] Even after overcoming the 1st challenge, you may not be able to read the data that is originally spatial and has been converted to a text file.
PODB provides many datasets that utilize spatial data, such as Japnese Prefecutres’ Basic Information, so it’s worth trying to overcome these challenges.
Two methods to overcome
[Method 1] Narrowing down the data into the limit when downloading
- Narrow down the data to the download capacity limit when applying SQL on Snowflake.
- Download the data in CSV format.
- Convert the downloaded CSV file to an Excel file.
- Upload the Excel file to Tableau Public.
- Change the column type from string to spatial.
However, because of the capacity of the data that can be downloaded from Snowflake and the capacity of the CSV, there may be cases where you cannot download all the data you want.
In that case, please try [Method 2] below.
[Method 2] Making use of ODBC driver function from Excel
Did you know that you can directly retrieve data from PODB using Excel?
Our representative, Mr. Fuji (not Mt. Fuji), has written a blog post about this method. Please take a look and start using this useful method now! Although it is written in Japanese, the images are informative and easy to understand even for non-Japanese speakers.
With this method, you can download almost all PODB datasets (*).
After that, just upload it to Tableau Public as in Method 1.
Please note that it may take time for queries or loading in Tableau Public.
※For some data, you need to apply SQL as shown in the figure below because it exceeds the limit of rows that can be saved in Excel. It has a larger download capacity than applying SQL directly to Snowflake.
Keep it Simple!
One user asked me how to use spatial data from Snowflake in Tableau Public. I struggled and struggled to solve it… but when I finally found the solution, I felt like "Oh, it was such a simple thing."
Initially, I overcomplicated things by thinking, "Is there a way to write a clever SQL?" or "Can we download it as a GeoJSON file?". I even asked AI in a not-so-smart way, and the answer I received was complicated.
I thought, ‘I’m sorry to tell the user to give up…’ But after I cleared my mind, I came up with this solution.
One of truestar’s 5 values, "Speed Up," has an action guideline called "Keep it Simple", and this incident made me realize the importance of this once again.
Why not try playing with Tableau Public × PODB with spatial data?
Please share your wonderful Viz on Tableau Public as well!
There are also usage examples on the PODB website, so please take a look.
See you next time!
