Visualize Finland’s postal code areas on a map in Google Looker Studio. The instructions requires a Google Cloud project and a BigQuery database.
The easiest way to get started is to download the Example file of Finnish postal code areas.
Uploading postal code data as a file to Looker Studio was not successful
Browser-based Looker Studio can be accessed from lookerstudio.google.com . Looker Studio is free and login happens with a Google account.
I tried uploading the postal code data directly as a file to the reporting tool.
There was an error loading the
CSV file, probably due to too many characters per line. The
JSON file 3rd part connector also did not work. Namely, you cannot choose a geographical polygon as the data type for the postal code area boundaries. Only a coordinate consisting of a longitude and latitude pair or, for example, the name of a country would come into question.
Advanced map visualization in Looker Studio requires a Google Cloud account
Visualizing postal code data in Looker Studio requires BigQuery or a similar database in Google Cloud.
You need to log in to the Google Cloud console and create a project .
Storing and processing data with BigQuery incurs costs. With postal code data, however, we are talking about pennies.
Loading postal code data into BigQuery
BigQuery is a database service for processing large amounts of data in the Google Cloud. It is browser-based for the user and surprisingly easy even for a first-timer.
It is still a rather technical and
SQL will become familiar.
The goal of the following steps is to load an example CSV file into BigQuery.
Start by creating a
Dataset. It contains one or more database tables.
Create a table in BigQuery’s Dataset into which the postal code data is loaded.
Select the file whose data will be loaded into the BigQuery table.
Selecting postal code data in BigQuery for Looker Studio visualization
Create a table and run the following SQL query. Change the name of the project, Dataset and table to the correct ones:
SELECT *, ST_GEOGFROMTEXT(multi_polygon) AS geo FROM `project_id.dataset_name.table_name`
ST_GEOGFROMTEXT function converts the postal code
MultiPolygon definition into a geographic data type understood by BigQuery and Looker Studio.
Explore in Looker Studio to create a map visualization from the queried data.
Map visualization of postal code areas in Looker Studio
Remove any graphs that Looker Studio may have automatically generated. Select
Filled Map from the top menu. Click on an empty map to open setting panel.
geo column created in the SQL query to
Geospatial field. You can use the postal code as a location identifier and the number of educated people as a measure of the color metric.
When visualizing all postal code areas in Finland, you should slightly adjust the settings in the
STYLE tab. Under the heading
Filled area layer and the subheading
Max number of polygon vertices, you should choose one million. The map points needed to define postal code polygons is hundreds of thousands.