Expanding BigQuery geospatial capabilities with Earth Engine raster analytics

At Google Cloud Next 25, a significant advancement in geospatial analytics was announced: Earth Engine in BigQuery. This new capability integrates Earth Engine’s raster analytics directly into BigQuery, making advanced analysis of geospatial datasets—often derived from satellite imagery—more accessible to the SQL community.

Before delving into the specifics of this new feature and its potential applications, it's helpful to understand the two main types of geospatial data and how Earth Engine and BigQuery have historically excelled in handling each:

• Raster data: Representing geographic information as a grid of cells or pixels, raster data stores values for specific attributes such as elevation, temperature, or land cover. Satellite imagery is a prime example. Earth Engine has long been the go-to platform for storing and processing raster data, enabling advanced image analysis and manipulation.

• Vector data: Representing geographic features such as points, lines, or polygons, vector data is ideal for capturing discrete objects, including buildings, roads, and administrative boundaries. BigQuery excels at storing and querying vector data, making it suitable for large-scale geographic analysis.

Both Earth Engine and BigQuery are powerful platforms in their own right. By integrating them, Earth Engine in BigQuery offers the best of both raster and vector analytics in one place. This extension enhances BigQuery's geospatial capabilities, enabling easier access to raster analytics and offering a streamlined solution for solving a range of enterprise problems.

Earth Engine in BigQuery: Key Features

Two main features power Earth Engine in BigQuery:

1. ST_RegionStats() Function: This new geography function in BigQuery allows users to extract statistics from raster data within specified geographic boundaries efficiently.

2. Earth Engine Datasets in BigQuery Sharing: A growing collection of Earth Engine datasets is now available in BigQuery Sharing, providing easy access to analysis-ready data. These datasets can be used to derive statistics for areas of interest, providing valuable insights into attributes such as elevation, emissions, or risk prediction.

   
   

Steps to Perform Raster Analytics

To conduct raster analytics using Earth Engine in BigQuery, users can follow these five steps:

1. Identify a BigQuery Table with vector data, such as administrative boundaries, customer locations, or other geographic areas of interest.

2. Identify a Raster Dataset: Users can access Earth Engine raster datasets in BigQuery Sharing or use raster data stored as Cloud GeoTiffs or Earth Engine image assets.

3. Use ST_RegionStats(): The ST_RegionStats() function takes raster data, vector geometries, and optional bands as inputs and calculates aggregate statistics (e.g., mean, min, max) for the intersecting raster data and vector features.

4. Analyse the Results: The output from ST_RegionStats() can be used to analyse the relationship between raster data and vector features, uncovering valuable insights for an area of interest.

5. Visualise the Results: Tools like BigQuery Geo Viz can be used to create interactive maps, helping users better understand spatial patterns and communicate their findings.

   
   

Empowering Data-Driven Decision-Making

The introduction of Earth Engine in BigQuery opens up new possibilities for data-driven decision-making, especially in geospatial and sustainability-related fields. This new capability enables raster analytics on datasets previously unavailable in BigQuery, helping organisations solve complex, real-world problems.

For instance, datasets can now be used with the ST_RegionStats() function to assess land cover types within specific administrative boundaries or evaluate the suitability of land for development. Sample queries for these datasets can be found on the individual dataset pages in BigQuery Sharing.

Here are a few use cases that Earth Engine in BigQuery makes possible:

1. Climate, Physical Risk, and Disaster Response

Raster data is crucial for understanding weather patterns and natural disaster risks. BigQuery Sharing provides several datasets on flood mapping, wildfire risk, and drought conditions. These insights can be applied to disaster risk management, urban planning, infrastructure development, and more. For example, the Wildfire Risk to Communities dataset can be used to predict wildfire hazards, assess community exposure, and identify vulnerability factors, aiding in the development of resilience strategies.

2. Sustainable Sourcing and Agriculture

Raster data provides insights on land use and land cover over time, which is valuable for sustainable sourcing and agricultural supply chain management. The new datasets in BigQuery, such as the Forest Data Partnership maps, allow businesses to analyse where commodities like cocoa, palm oil, and rubber are grown, assessing whether these areas have remained deforested. By simply querying the data, businesses can evaluate the sustainability of their sourcing practices.

3. Methane Emissions Monitoring

Reducing methane emissions from industries such as oil and gas is crucial to mitigating climate change. The MethaneSAT L4 Area Sources dataset provides detailed information on methane emissions from various sources. Using Earth Engine in BigQuery, users can analyse the location, magnitude, and trends of these emissions, identifying hotspots and informing mitigation efforts.

4. Custom Use Cases

Beyond the provided datasets, users can also bring their raster datasets (e.g., stored as Cloud GeoTiffs or Earth Engine image assets) to support custom use cases, while leveraging BigQuery's scalability and analytical tools.

Get Started with $300 in Free Credit

To explore the potential of Earth Engine in BigQuery, users can access $300 in free credit to try out Google Cloud data analytics and begin implementing these powerful geospatial capabilities.

Start building for free

Advanced Example: Analysing Wildfire Risk with AI-Driven Weather Forecasts

Consider a more advanced example that combines modelled wildfire risk with AI-driven weather forecasting technology. The following SQL query utilises the Wildfire Risk to Communities dataset, which is available in BigQuery Sharing. This dataset is specifically designed to help communities assess and mitigate their exposure to wildfire risk. It includes bands that index both the likelihood and consequences of wildfire across various landscapes.

By utilising geometries from a public dataset of census-designated places, users can apply the ST_RegionStats() function to compute values from the Wildfire Risk to Communities dataset, enabling a comparison of the relative risk exposures among different communities. A sample query illustrating how to identify census tracts in Oregon at the highest wildfire risk can be found on the dataset’s page.

To enhance the analysis, weather data from WeatherNext Graph forecasts can be integrated to assess how imminent fire weather conditions might impact these communities. Note that access to the WeatherNext dataset may need to be requested prior to use.

Here’s how to get started:

1. Navigate to the BigQuery Sharing console.

2. Click on “Search listings”, then filter by “Climate and Environment”.

3. Select the “Wildfire Risk to Communities” dataset, or search for it using the search bar, and click “Subscribe” to add it to your BigQuery project.

   
   

Next, search for “WeatherNext Graph”, subscribe to the dataset, and integrate it into your project to combine wildfire risk with weather forecasts.

Once the subscriptions are in place, users can run a query to combine these datasets across multiple communities using a single query. The task can be broken down into subqueries for clarity using the SQL WITH statement:

1. Select Input Tables: Start by selecting the tables that were subscribed to in the previous step.

2. Compute Weather Forecast: Use the WeatherNext Graph forecast data for a specific date and the places of interest to calculate the weather forecast. The output will include the average and maximum wind speeds for each community.

3. Use ST_RegionStats() for Wildfire Risk: Apply the ST_RegionStats() function to sample the Wildfire Risk to Community raster data for each community. Since the goal is to compute mean values within regions, the scale can be set to 1 kilometre in the function options. This allows for the use of lower-resolution overviews, which helps reduce compute time. If full raster resolution (30 meters) is required, this scale option can be omitted.

   
   

The result is a table containing the mean values of wildfire risk for both bands within each community, along with wind speeds projected over a day. In addition, you can combine the computed values for wildfire risk, wildfire consequence, and average wind speed to create a simple composite index to show relative wildfire exposure for a selected day in Colorado.

Mean values of wildfire risk and wind speeds for each community

You can save this output in Google Sheets to visualize how wildfire risk and consequences are related among communities statewide.

Google sheet visualising wildfire risk (x-axis) and wildfire consequence (y-axis) colored by wind speed

Alternatively, you can visualise relative wildfire risk exposure in BigQuery GeoViz with the single composite index to show relative wildfire exposure for a selected day in Colorado.

GeoViz map showing composite index for wildfire risk, wildfire consequence, and max wind speed

What’s next for Earth Engine in BigQuery?

Earth Engine in BigQuery marks a significant advancement in geospatial analytics, and Google Maps is excited to further expand raster analytics in BigQuery, making sustainability decision-making easier than ever before. 

Get in touch with us today to learn more about this new capability. 

(Source: https://cloud.google.com/blog/products/data-analytics/a-closer-look-at-earth-engine-in-bigquery)