Mapping Local Flood Risks in Looker Studio using Defra Open Data and BigQuery

Introduction

Living in Shrewsbury,UK, I am surrounded by beautiful, embankless river landscapes. However, this beauty comes with a recurring reality: every year, we receive flood alerts. With the accelerating impact of climate change, the risk of localized, heavy rainfall and severe flooding is only increasing.

As residents, it is crucial to analyze these spatial risks accurately to make informed decisions about where we live and how we structure our lifestyles. To achieve this, I decided to build an interactive flood risk map using the UK Environment Agency’s open data (Defra OGC preview).

However, visualizing massive geospatial open data in Looker Studio comes with major performance and rendering pitfalls. In this article, I will share the optimal, step-by-step workflow — from QGIS optimization to BigQuery integration — to successfully render these complex polygons.

Figure 1: The end-to-end data pipeline from Defra Open Data to Looker Studio via QGIS optimization and BigQuery.

Workflow Overview

Before diving into the details, here is the high-level workflow we will follow to achieve lightweight and accurate geospatial rendering:

1. Extract: Download the official flood risk boundaries from Defra Open Data.
2. Optimize: Use QGIS to simplify geometries and clean the dataset.
3. Fix:Repair invalid geometries (such as self-intersections) to prevent schema and loading errors in BigQuery.
4. Store: Export to Apache Parquet and load it directly into Google BigQuery.
5. Visualize: Connect BigQuery to Looker Studio for fast, interactive map rendering.

Step 1: Filtering and Downloading Data on Defra

The raw flood risk dataset covers the entire country and is far too massive to handle. The first optimization trick happens right on the Defra portal before you even click download.

1. Access the Defra OGC preview portal and locate the flood risk dataset.
2. In the right-hand panel under Area of interest, select Selected area (draw a polygon).
3. Use the bounding box tool on the map to crop and select only your target region around Shrewsbury.
4. Under Layers, select Flood_Zones_2_3_Rivers_and_Sea.
5. Set the File format to ESRI Shapefile and click Download file.

Figure 2: Selecting the Shrewsbury area and choosing the “Flood Zones 2 and 3” layer in ESRI Shapefile format on the Defra Data Services Platform.

Step 2: Data Preparation in QGIS

Now we will use QGIS to load, optimize, and repair our dataset before moving it to the cloud. Looker Studio cannot smoothly render polygons with too many dense vertices, so preprocessing is essential.

1. Load the Data into QGIS

1. Unzip the downloaded Defra file.
2. Drag and drop the .shp file directly into QGIS. You will see the localized flood risk zones loaded onto your canvas, perfectly matching the Shrewsbury area we selected.

Figure 3: Importing the raw Defra flood zone shapefile into QGIS, centered on the Shrewsbury river network before optimization.

2. Simplify Geometries

1. Go to the top menu and select Vector ➡️ Geometry Tools ➡️ Simplify…
2. In the Simplify dialog, take a look at the Tolerance field. Since the raw Defra data uses the British National Grid coordinate system, the default unit is set to meters.
3. To perfectly balance data size reduction and map precision, set the Tolerance value to 10 (meters).

Figure 4: Navigating the QGIS top menu to locate the Simplify tool via Vector ➡️ Geometry Tools ➡️ Simplify…

Figure 5: Configuring the Simplify tool in QGIS with a 10-meter tolerance using the Douglas-Peucker algorithm.

4. Click Run. This drastically reduces the vertex count, making the dataset lightweight enough for Looker Studio to process without freezing.

5. A new layer called Simplified will be added to your Layers panel.

3. Fix Broken Geometries

Simplifying shapes or handling raw GIS data can sometimes leave “invalid geometries” (like self-intersecting polygons). If uploaded directly to BigQuery or Looker Studio, these can cause rendering errors. Let’s clean them up in one click.

1. On the right side of QGIS, look at the Processing Toolbox panel. (If you don’t see it, toggle it via the top menu: Processing ➡️ Toolbox).
2. Type fix into the search bar, and double-click Fix geometries under the Vector geometry group.
3. In the dialog, ensure your newly created Simplified layer is selected as the Input layer.
4. Click Run. QGIS will output a perfectly clean layer called Fixed geometries, free of structural errors.

Figure 6: Running the “Fix Geometries” tool on the simplified layer to repair any invalid topological structures or self-intersections.

4. Export as Apache Parquet

Now for the secret sauce. Instead of converting back to a bulky Shapefile, we will export this cleaned data directly into Parquet format. BigQuery loves Parquet because it natively supports geospatial columns, compresses beautifully, and loads instantly.

1. Right-click your new Fixed geometries layer in the Layers panel.
2. Select Export ➡️ Save Features As…
3. In the Format dropdown, select Apache Parquet.
4. Choose your save location and name the file (e.g., shrewsbury_flood_zones.parquet).
5. Leave the default settings as they are and click OK.

Figure 7: Exporting the optimized layer as a (Geo)Parquet file (shrewsbury_flood_final) with the CRS set to EPSG:4326 (WGS 84) for seamless BigQuery integration.

Step 3: Loading Parquet into BigQuery with Auto-Detect

Before arriving at this solution, I initially tried exporting the data as a GeoJSON file, which is a standard format in GIS. However, I quickly ran into frustrating roadblocks when trying to load it into BigQuery — the nested field structures made parsing and schema definition a real headache.

Looking for a better way, I decided to give Apache Parquet a try, and the experience was night and day. It loaded flawlessly on the first attempt.

BigQuery and Parquet are clearly a match made in heaven for geospatial data. Because Parquet embeds its own column structures and metadata, you don’t need to manually configure schemas or struggle with text-to-geometry conversions. BigQuery inherently understands the exact structure of your data from the file itself. For this reason, I highly recommend using Parquet for this pipeline.

1. Initiate Table Creation

1. Open your Google Cloud Console and navigate to BigQuery.
2. Select your project and dataset, then click Create table.

2. Configure Source and Destination Settings

Under the Source section, configure the following:

1. Create table from: Select Upload.
2. Select file: Browse and select your exported .parquet file from QGIS.
3. File format: This will automatically switch to Parquet.

Under the Destination section, specify your target Project and Dataset, then type your desired Table name (e.g., shrewsbury_flood_zones).

3. Let BigQuery Handle the Schema

1. Scroll down to the Schema section. You will notice that you don’t need to configure settings manually. Because the source is a Parquet file, BigQuery inherently knows the exact structure of your data.

Figure 8: Creating a table in Google BigQuery by uploading the Parquet file. Note how the schema is automatically defined by the source file, eliminating manual input.

2. Click Create table at the bottom of the panel.

3. BigQuery will instantly parse the file, automatically mapping the QGIS spatial columns into a native, flawless GEOGRAPHY data type.

Step 4: Visualizing Spatial Data in Looker Studio

With our optimized flood risk data safely stored as a native GEOGRAPHY type in BigQuery, we are ready for the grand finale: building the interactive map. Thanks to our preprocessing in QGIS, Looker Studio will load this massive dataset seamlessly without lagging.

1. Connect to Your BigQuery Table

1. Open Looker Studio and click Blank Report.
2. In the “Add data to report” window, select the BigQuery connector.
3. Navigate through your hierarchy: select your Project, Dataset, and the Table we just created (shrewsbury_flood_zones).
4. Click Add at the bottom right, and confirm by clicking Add to Report.

2. Add the Google Maps Chart

1. Delete any default tables that Looker Studio automatically generates on your canvas to clear your workspace.
2. Click Add a chart in the top toolbar and select Line Map (under the Google Maps section).
3. Drag the edges of the chart to stretch the map so it fills your canvas.

3. Configure the Spatial Dimensions

1. Look at the Setup panel on the right side of the screen.
2. Drag your spatial column (e.g., geometry) into the Location field. (Note: Looker Studio will automatically detect this as a geo-dimension, displaying a small globe icon next to the field name).
3. Drag your flood zone classification column (e.g., flood_zone) into the Color dimension field. Looker Studio will instantly color-code the boundaries over Shrewsbury — rendering Flood Zone 2 (FZ2) in crisp yellow and Flood Zone 3 (FZ3) in vibrant blue.

Figure 9: Visualizing the processed flood risk zones in Looker Studio using the Google Maps chart component connected to BigQuery.

Conclusion: The Power of an Optimized Geospatial Pipeline

By shifting the heavy lifting away from Looker Studio and onto QGIS and BigQuery, I transformed a sluggish, oversized environmental dataset into a lightning-fast, interactive dashboard.

Instead of forcing your browser to parse thousands of complex vertices, you now have a streamlined, production-ready geospatial pipeline. Using QGIS to crop, simplify, and repair geometries ensures data efficiency, while exporting to Apache Parquet guarantees flawless schema detection and native spatial integration within BigQuery.

The result is a responsive rendering experience in Looker Studio via the Line Map visualization. This architectural approach ensures that your stakeholders get the critical flood insights they need, exactly when they need them, without ever waiting for a map to load. Happy mapping!