How to Visualize Longitude & Latitude Coordinates in Looker Studio

Visualizing latitude and longitude coordinates in Looker Studio might not be as straightforward as it should be, which is why we will walk you through the process with a simple example. We will use a GeoJSON file provided by the City of Amsterdam, containing, among other things, data on the maximum speeds, as well as geographic data like streetnames and longitude-latitude coordinates. This data has been 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.

Sadly, converting this data into CSV and importing it into Looker Studio doesn't work, because Looker will not recognize the latitude and longitude coordinates as geospatial data which can be used on a map. It turns out we have to ingest it into Google BigQuery first, and then import it into Looker Studio. Even if you were to recreate the data in Google Sheets in exactly the same format (and trust me, I've tried) and feed it into Looker Studio, it still wouldn't be recognized as geospatial data.

With regards to the data, the City of Amsterdam has created a similar map. However, their map is mainly focused on the transition from 30 km/h to 50 km/h and only included the main roads, whereas our map includes all roads and speed limits. If you would like to follow along, you can download the data set here: maps.amsterdam.nl.

Note: images can be made larger when clicked on.

Seeing as the City of Amsterdam has provided an already cleaned data set, we can start with ingesting it into Google BigQuery. This needs to be done in a 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. Loading a JSON into Google BigQuery can be done in several way, but the most effective way is to do it through Cloud Shell. For this, we will use this method, by Marc Soares.

Start by logging into Google BigQuery, creating a new project (or selecting an existing one), and activating the Cloud Shell which can be accessed through the icon in the top right corner.

This will open up Cloud Shell in the same browser on the bottom of your screen. The project we will use is called 'kmu-425119'. On your screen this will be the name of your own project.

We will start off by fetching the dataset. The easiest way to fetch your data set is through a direct link. If possible, put it into your cloud storage (like Dropbox or Google Cloud) and get a direct link to the file. In Cloud Shell, type or paste the following code and press Enter.

curl -O https://yourlink.com/yourfile.json

Explanation:

• curl - This is the command-line tool used for transferring data from or to a server using URLs.

• -O (uppercase O) - This option tells curl to save the downloaded file with the same name as in the URL. E.g., if the URL points to a file named “yourfile.json,” curl -O will also save it as “yourfile.json”.

Then, install the geojson2ndjson package. This is used to convert GeoJSON files to Newline Delimited JSON (NDJSON), which is preferred for BigQuery. You can install the package by pasting the code below and pressing Enter.

npm install -g geojson2ndjson

• -g - The -g will install the library globally, rather than just within a virtual environment or for a specific user. If you don't want to do so, leave it out.

We will now convert our file to a NDJSON file. This can be done with the code below.

geojson2ndjson yourfile.json > yourfile.geojsonl

• yourfile.geojsonl - The new extension will be .geojsonl, which is the same extension but with a non capitalized l(ima) at the end.

We will now create a dataset to upload the our data into. You can do this with the code below. 'your_data_set_name' should obviously be replaced with the name you want to give your dataset, which should be one word without spaces. (Use underscores or Camel case instead of spaces.) We will call ours '30km'.

bq mk your_data_set_name

• bq mk - This is used to create new BigQuery resources. It's short for BigQuery make.

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

Now it's time to upload the dataset, and make up a table name in the process. You can do so with the code below:

bq load --source_format=NEWLINE_DELIMITED_JSON --json_extension=GEOJSON --autodetect your_data_set_name.make_up_a_table_name yourfile.geojsonl

Change the following in this code:

• your_data_set_name - This is the data set name you just created with bq mk your_data_set_name.

• make_up_a_table_name - Make up a name for your new BigQuery table, in which your JSON data will be put.

• yourfile.geojsonl - This the file name of your converted file, which you converted with geojson2ndjson yourfile.json > yourfile.geojsonl.

After refreshing the page, your new data set and table should be there.

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

You can now import the into Looker Studio the regular way, i.e., 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 is 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, we 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.

This will result in the map on the image below. You will also see the interactive map we have created in Looker Studio below it.