Menu
Enhancing BigQuery geospatial with Earth Engine raster analytics and map visualization

GCP – Enhancing BigQuery geospatial with Earth Engine raster analytics and map visualization

,

Geospatial analytics can transform rich data into actionable insights that drive sustainable business strategy and decision making. At Google Cloud Next ‘25, we announced the preview of Earth Engine in BigQuery, an extension of BigQuery’s current geospatial offering, focused on enabling data analysts to seamlessly join their existing structured data with geospatial datasets derived from satellite imagery. Today, we’re excited to announce the general availability of Earth Engine in BigQuery and the preview of a new geospatial visualization capability in BigQuery Studio. With this new set of tools, we’re making geospatial analysis more accessible to data professionals everywhere.

Bringing Earth Engine to data analysts

Earth Engine in BigQuery makes it easy for data analysts to leverage core Earth Engine capabilities from within BigQuery. Organizations using BigQuery for data analysis can now join raster (and other data created from satellite data) with vector data in their workflows, opening up new possibilities for use cases such as assessing natural disaster risk over time, supply chain optimization, or infrastructure planning based on weather and climate risk data. 

This initial release introduced two features:

  1. ST_RegionStats() geography function: A new BigQuery geography function enabling data analysts to derive critical statistics (such as wildfire risk, average elevation, or probability of deforestation) from raster (pixel-based) data within defined geographic boundaries.

  2. Earth Engine datasets in BigQuery Sharing: A growing collection of 20 Earth Engine raster datasets available in BigQuery Sharing (formerly Analytics Hub), offering immediate utility for analyzing crucial information such as land cover, weather, and various climate risk indicators.

What’s new in Earth Engine in BigQuery 

With the general availability of Earth Engine in BigQuery, users can now leverage an expanded set of features, from what was previously available in preview:

  • Expanded regional deployment: Earth Engine in BigQuery now supports EU (multi-region) and europe-west1, in addition to US regions, for both data storage and computation, offering greater flexibility for regional needs and regulations.
  • Enhanced metadata visibility: A new Image Details tab in BigQuery Studio provides expanded information on raster datasets, such as band and image properties. This makes geospatial dataset exploration within BigQuery easier than ever before.
  • Improved usage visibility: View slot-time used per job and set quotas for Earth Engine in BigQuery to control your consumption, allowing you to manage your costs and better align with your budgets.

1

New Image Detail tab in BigQuery Studio displays metadata for Aqueduct Flood Hazard dataset

A new way to visualize BigQuery geospatial data

We know visualization is crucial to understanding geospatial data and insights in operational workflows. That’s why we’ve been working on improving visualization for the expanded set of BigQuery geospatial capabilities. Today, we’re excited to introduce map visualization in BigQuery Studio, now available in preview. 

You might have noticed that the “Chart” tab in the query results pane of BigQuery Studio is now called “Visualization.” Previously, this tab provided a graphical exploration of your query results. With the new Visualization tab, you’ll have all the previous functionality and a new capability to seamlessly visualize geospatial queries (containing a GEOGRAPHY data type) directly on a Google Map, allowing for: 

  • Instant map views: See your query results immediately displayed on a map, transforming raw data into intuitive visual insights.

  • Interactive exploration: Inspect results, debug your queries, and iterate quickly by interacting directly with the map, accelerating your analysis workflow.

  • Customized visualization: Visually explore your query results with easy-to-use, customizable styling options, allowing you to highlight key patterns and trends effectively.

Built directly into BigQuery Studio’s query results, map visualization simplifies query building and iteration, making geospatial analysis more intuitive and efficient for everyone.

2

Visualization tab displays a heat map of census tracts in Colorado with the highest wildfire risk using the Wildfire Risk to Community dataset

Example: Analyzing extreme precipitation events

The integration of Earth Engine in BigQuery with map visualization within a single platform creates a powerful and unified geospatial analytics platform. This allows analysts to move from data discovery to complex analysis and visualization within a single platform, significantly reducing the time to insight. For businesses, this offers powerful new capabilities for assessing climate risk directly within their existing data workflows.

Consider a scenario where an insurance provider needs to assess how hydroclimatic risk is changing across its portfolio in Germany. Using Earth Engine in BigQuery, the provider can analyze decades of climate data to identify trends and changes in extreme precipitation events.

The first step is to access the necessary climate data. Through BigQuery Sharing, you can subscribe to Earth Engine datasets directly. For this analysis, we’ll use the ERA5 Land Daily Aggregates dataset (daily grid or “image” weather maps) to track historical precipitation.

3

BigQuery Sharing listings for Earth Engine with the ERA5 Daily Land Aggregates highlighted (left) and the dataset description with the “Subscribe” button (right)

By subscribing to the dataset, we can now query it. We use the ST_RegionStats() function to calculate statistics (like the mean or sum) for an image band over a specified geographic area. In the query below, we calculate the mean daily precipitation for a subset of counties in Germany for each day in our time range and then find the maximum value for each year:

Next, we analyze the output from the first query to identify changes in extreme event frequency. To do this, we calculate return periods. A return period is a statistical estimate of how likely an event of a certain magnitude is to occur. For example, a “100-year event” is not one that happens precisely every 100 years, but rather an event so intense that it has a 1% (1/100) chance of happening in any given year. This query compares two 30-year periods (1980-2010 vs. 1994-2024) to see how the precipitation values for different return periods have changed:

Note: This query can only be run in US regions. The Overture dataset is US-only. In addition, Earth Engine datasets are rolling out to EU regions over the coming weeks.

code_block
<ListValue: [StructValue([(‘code’, “– This UDF implements the Gumbel distribution formula to estimate the event magnitudern– for a given return period based on the sample mean (xbar) and standard deviation (sigma).rnCREATE TEMP FUNCTIONrnCalculateReturnPeriod(period INT64, xbar FLOAT64, sigma FLOAT64)rn RETURNS FLOAT64 AS ( ROUND(-LOG(-LOG(1 – (1 / period))) * sigma * .7797 + xbar – (.45 * sigma), 4) );rnrnrnWITHrn– Step 1: Define the analysis areas.rn– This CTE selects a specific subset of 10 major German cities.rn– ST_SIMPLIFY is used to reduce polygon complexity, improving query performance.rnCounties AS (rnFROM bigquery-public-data.overture_maps.division_arearn|> WHERE country = ‘DE’ AND subtype = ‘county’rn AND names.primary IN (rn ‘München’,rn ‘Köln’,rn ‘Frankfurt am Main’,rn ‘Stuttgart’,rn ‘Düsseldorf’)rn|> SELECTrn id,rn names.primary AS county,rn ST_SIMPLIFY(geometry,500) AS geometryrn),rn– Step 2: Define the time periods for comparison.rn– These two 30-year, overlapping epochs will be used to assess recent changes.rnEpochs AS (rn FROM UNNEST([rn STRUCT(‘e1’ AS epoch, 1980 AS start_year, 2010 AS end_year),rn STRUCT(‘e2’ AS epoch, 1994 AS start_year, 2024 AS end_year)])rn),rn– Step 3: Define the return periods to calculate.rnReturnPeriods AS (rn FROM UNNEST([10,25,50,100,500]) AS years |> SELECT *rn),rn– Step 4: Select the relevant image data from the Earth Engine catalog.rn– Replace YOUR_CLOUD_PROJECT with your relevant Cloud Project ID.rnImages AS (rn FROM YOUR_CLOUD_PROJECT.era5_land_daily_aggregated.climatern |> WHERE year BETWEEN 1980 AND 2024rn |> SELECTrn id AS img_id,rn start_datetime AS img_datern)rn– Step 5: Begin the main data processing pipeline.rn– This creates a processing task for every combination of a day and a county.rnFROM Imagesrn|> CROSS JOIN Countiesrn– Step 6: Perform zonal statistics using Earth Engine.rn– ST_REGIONSTATS calculates the mean precipitation for each county for each day.rn|> SELECTrnimg_id,rnCounties.id AS county_id,rnEXTRACT(YEAR FROM img_date) AS year,rnST_REGIONSTATS(geometry, img_id, ‘total_precipitation_sum’) AS areal_precipitation_statsrn– Step 7: Find the annual maximum precipitation.rn– This aggregates the daily results to find the single wettest day for each county within each year.rn|> AGGREGATErnSUM(areal_precipitation_stats.count) AS pixels_examined,rnMAX(areal_precipitation_stats.mean) AS yearly_max_1day_precip,rnANY_VALUE(areal_precipitation_stats.area) AS pixel_arearnGROUP BY county_id, yearrn– Step 8: Calculate statistical parameters for each epoch.rn– Joins the annual maxima to the epoch definitions and then calculates thern– average and standard deviation required for the Gumbel distribution formula.rn|> JOIN Epochs ON year BETWEEN start_year AND end_yearrn|> AGGREGATErn AVG(yearly_max_1day_precip * 1e3) AS avg_yearly_max_1day_precip,rn STDDEV(yearly_max_1day_precip * 1e3) AS stddev_yearly_max_1day_precip,rn GROUP BY county_id, epochrn– Step 9: Calculate the return period precipitation values.rn– Applies the UDF to the calculated statistics for each return period.rn– This assumes they are the same function.rn|> CROSS JOIN ReturnPeriods rprn|> EXTENDrn CalculateReturnPeriod(rp.years, avg_yearly_max_1day_precip, stddev_yearly_max_1day_precip) AS est_max_1day_preciprn|> DROP avg_yearly_max_1day_precip, stddev_yearly_max_1day_preciprn– Step 10: Pivot the data to create columns for each epoch and return period.rn– The first PIVOT transforms rows for ‘e1’ and ‘e2’ into columns for direct comparison.rn|> PIVOT (ANY_VALUE(est_max_1day_precip) AS est_max_1day_precip FOR epoch IN (‘e1’, ‘e2’))rn– The second PIVOT transforms rows for each return period (10, 25, etc.) into columns.rn|> PIVOT (ANY_VALUE(est_max_1day_precip_e1) AS e1, ANY_VALUE(est_max_1day_precip_e2) AS e2 FOR years IN (10, 25, 50, 100, 500))rn– Step 11: Re-attach county names and geometries for the final output.rn|> LEFT JOIN Counties ON county_id = Counties.idrn– Step 12: Calculate the final difference between the two epochs.rn– This creates the delta values that show the change in precipitation magnitude for each return period.rn|> SELECTrncounty,rnCounties.geometry,rne2_10 – e1_10 AS est_10yr_max_1day_precip_delta,rne2_25 – e1_25 AS est_25yr_max_1day_precip_delta,rne2_50 – e1_50 AS est_50yr_max_1day_precip_delta,rne2_100 – e1_100 AS est_100yr_max_1day_precip_delta,rne2_500 – e1_500 AS est_500yr_max_1day_precip_deltarn|> ORDER BY county”), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3ebcfc2a2ca0>)])]>

To provide quick results as a demonstration, the example query above filters for five populated counties; running the computation for all of Germany would take much longer. When running the analysis for many more geometries (areas of interest), you can break the analysis into two parts:

  1. Calculate the historical time series of maximum daily precipitation for each county in Germany from 1980-2024 and save the resulting table.

  2. Use these results to calculate and compare precipitation return periods for two distinct timeframes.

With the analysis complete, the results can be immediately rendered using the new Visualization feature in BigQuery Studio. This allows the insurance provider to:

  • Pinpoint high-risk zones: Visually identify clusters of counties with increasing extreme precipitation, for proactive risk mitigation and to optimize policy pricing.
  • Communicate insights: Share interactive maps with stakeholders, making complex risk assessments understandable at a glance.
  • Inform strategic decisions: This type of analysis is not limited to insurance. For example, a consumer packaged goods (CPG) company could use these insights to optimize warehouse and distribution center locations, situating them in areas with more stable climate conditions.

4

Running BigQuery analysis for changing extreme precipitation events in Germany and interactively exploring the results with the new Map visualization

This combination of Earth Engine, BigQuery, and integrated visualization helps businesses move beyond reactive measures, enabling data-driven foresight in a rapidly changing world.

The future of geospatial analysis is here

With the general availability of Earth Engine in BigQuery and the preview of map visualization, we’re helping data professionals across industries to unlock richer, more actionable insights from their geospatial data. From understanding climate risk for buildings in flood-prone areas to optimizing enterprise planning and supply chains, these tools are designed to power operational decision making, helping your business thrive in an increasingly data-driven landscape.

We are continuously working to expand the utility and accessibility of this new set of capabilities, including:

  • Growing catalog of datasets: Expect more datasets for both Earth Engine and BigQuery Sharing, allowing you to leverage analysis-ready datasets for individual or combined use with custom datasets. 

  • Intelligent geospatial assistance: We envision a future where advanced AI and code generation capabilities will further streamline geospatial workflows. Stay tuned for more on this later this year!

To begin leveraging these new capabilities, explore our comprehensive documentation for Earth Engine in BigQuery and the new map visualization feature today.

Read More for the details.

Related Posts