Portfolio Project – Maximum Speeds in Amsterdam, The Netherlands

As of December 8th, 2023, many roads in the city of Amsterdam have had their maximum speed reduced from 50 km/h to 30km/h. In this portfolio project, I have ingested a data set with Amsterdam's maximum speeds into Google BigQuery, exported it to Looker Studio and visualized it on a map.

Note: images can be made larger when clicked on.

Note: the process for this portfolio project has been made into an article.

This data set has been provided by the City of Amsterdam, which they have made publicly available through an open license which allows for the use and reuse of the data set for any lawful purpose, commercial and non-commercial. It is a GeoJSON file, which includes data on the maximum speeds, as well as geographic data like streetnames and longitude-latitude coordinates. For Looker Studio to correctly make use of said coordinates, they need to be in longitude-latitude format, rather than the other way around. If the order is reversed, i.e. latitude-longitude, the coordinates in it will be mapped to the wrong geographical location.

Seeing as I have been living in Amsterdam for quite a while now, I thought it interesting to visualize the maximum speeds of the roads in Amsterdam. The City of Amsterdam has created a similar map, but their map was mainly focused on the transition from 30 km/h to 50 km/h, and only included the main roads. 

Seeing as the City of Amsterdam had provided an already cleaned data set, all I had to do was ingest it into Looker Studio. The catch is that Looker Studio won't recognize longitude-latitude combinations as geospatial data unless it's coming from Google BigQuery. Even if you were to recreate the data in Google Sheets in the exact same format (and trust me, I've tried), and feed it into Looker Studio, it still wouldn't be recognized as geospatial data. This meant that I had to load the data into Google BigQuery first. This was done through Cloud Shell, which can be accessed through the icon in the top right corner after you have logged into BigQuery. The method I have used to ingest and convert the GeoJSON was this one, by Marc Soares.

Before ingesting the data, I had created a new project in Google BigQuery, which was given the name kmu-425119 by BigQuery. Ingesting the data was done through Cloud Shell, which can be accessed through the icon in the top right corner after you have logged into BigQuery. The method I have used to ingest and convert the GeoJSON was this one, by Marc Soares.

This will open up Cloud Shell in the same browser.

I had downloaded the JSON file and made it available through a direct link, so BigQuery could fetch the data more easily.

Then I went on to install the geojson2ndjson package, which is used to convert GeoJSON files to Newline Delimited JSON (NDJSON), which is preferred for BigQuery.

After it, I did the actual conversion.

Then it was time to create a dataset to upload the actual data into, which was called '30km'.

A pop-up will appear to authorize Cloud Shell to create the dataset for you.

Then it's time to upload the dataset, and make up a table name in the process.

After refreshing the page, the new dataset and table should be there.

Notice the LineString geometry type, which contains the longitude and latitude coordinates.

Importing the data into Looker Studio was done the regular way, which is through the built-in BigQuery connector. After having imported the data into Looker Studio, the important part is that Looker Studio recognizes the longitude-latitude coordinates as geospatial data.

The obvious choice to visualize this data is through a line map.

The dimension and metric chosen for the Color dimension and Thickness is the column 'Max. speed (km/h)'. This is simply the  'MAX_SNEL_WENS' column renamed, so that the tooltip will show the former rather than the latter. In this case, using the same column for both the Color dimension and Thickness was a tradeoff. The upside is that improves the visualization tremendously, as thinner lines for lower speed roads make the map much more readable. The downside is that the tooltip will show the same info twice. Contrary to a tool like Tableau, Looker Studio currently doesn't have the option to customize the tooltip.

For Location, I have created a calculated field called 'straatnaam_stadsdeel', which contains a concatenation of the column 'STT_NAAM', which contains the streetnames, and the calculated field 'stadsdeel_cleaned'.

The calculated field 'stadsdeel_cleaned' is a cleaned up version of the column 'STADSDEEL', which contains municipality districts.

The last adjustment which has been made, is to filter out the NULL values, and to filter out the speed limit of '12', which is an erroneous value in the context of speed limits.