Google Maps Platform is excited to announce a new feature that simplifies exporting tabular data from Earth Engine into BigQuery. Earth Engine and BigQuery share the goal of making large-scale data processing accessible and usable by a wider range of people and applications; Earth Engine tends to focus on image (raster) processing, whereas BigQuery is optimised for processing large tabular datasets. This new connection is their first major step towards a deeper interoperability between the two platforms.
For years, users have moved Earth Engine data into BigQuery, but, until now, that required careful attention to encodings, intermediate storage, and data types. Today, Google Maps Platform can offer more juice for less squeeze, with a single-line invocation to transfer Earth Engine data into BigQuery. This new `Export.table.toBigQuery()` function makes a number of new flows simpler, including:
combining Earth Engine data with BigQuery data sources to get a more complete picture of a particular problem
using BigQuery's powerful analysis tools to extract insights from Earth Engine data
sharing Earth Engine data with SQL-friendly users in a way that's accessible for them
This guide walks through the process of exporting data from Earth Engine to BigQuery, building a real-world example of using Google's geospatial tools to identify flooded roads.
Example: Flooded road detection
Extreme weather events have a devastating impact around the world. Flooding, heat waves, and drought have substantial human and financial costs, causing mortality and devastation of homes and property. The following example shows how to use satellite data mosaics from Earth Engine and open road datasets from BigQuery, processing the data in both environments to determine which road segments are affected by a flooding event in the UK.
Prerequisites
Create a new Cloud project and enable the BigQuery and Earth Engine APIs.
Configure access to Earth Engine.
Create a dataset in your BigQuery workspace. This example uses one called "your_dataset".
Use Earth Engine Code Editor, or you can follow the same steps in the Colab environment with roughly equivalent Python.
Following this example requires a billing account to be enabled on the project, and it will generate charges for Earth Engine computation, BigQuery storage, BigQuery computation, and network traffic.
Identify flooded areas in Earth Engine
The Earth Engine Data Catalog contains the Copernicus Sentinel Synthetic Aperture Radar collection. This public dataset is composed of radar images that measure how surfaces scatter light waves back to a satellite's sensor. Standing bodies of water act like mirrors for radio signals, reflecting the satellite's radar light away rather than scattering it back to the imaging sensor. Most natural surfaces don't have this property, which means that one can differentiate standing bodies of water from their surroundings by looking for "dark" patches in the images (that is, areas with low backscatter values). Let’s prepare the input data by selecting an area of interest and filtering images with vertical-vertical ("VV") polarisation, sending vertically polarised light, and measuring the vertically polarised light that's returned.
1. Load the Code Editor and define the area of interest and the filtered collection:
lang-py
// The area of interest ("AOI") polygon, can also be imported or hand-drawn.
var aoi = ee.Geometry.Polygon(
[[[-2.92, 54.10],
[-2.92, 53.99],
[-2.67, 53.99],
[-2.67, 54.10]]], null, false);
// Load the Sentinel-1 collection (log scaling, VV co-polar).
var collection = ee.ImageCollection('COPERNICUS/S1_GRD')
.filterBounds(aoi)
.filter(ee.Filter.listContains('transmitterReceiverPolarisation', 'VV'))
.select('VV');
2. Identify the areas which changed most significantly using a pair of images from before and after the heavy rains. The difference between the two images indicates the regions that were flooded.
// Smooth the data to remove noise.
var SMOOTHING_RADIUS_METERS = 100;
// Filter by date (before and after)
var before = collection.filterDate('2017-11-01', '2017-11-17')
.mosaic()
.focalMedian(SMOOTHING_RADIUS_METERS, 'circle', 'meters');
var after = collection.filterDate('2017-11-18', '2017-11-23')
.mosaic()
.focalMedian(SMOOTHING_RADIUS_METERS, 'circle', 'meters');
// Threshold smoothed radar intensities to identify areas with standing water.
var DIFF_THRESHOLD_DB = -3;
var diffSmoothed = after.subtract(before);
var diffThresholded = diffSmoothed.lt(DIFF_THRESHOLD_DB);
3. Next, use the Global Surface Water dataset to remove persistent surface water (like lakes, rivers, etc.) from the result:
// Remove global surface water (oceans, lakes, etc.).
var jrcData0 = ee.Image('JRC/GSW1_0/Metadata')
.select('total_obs')
.lte(0);
var waterMask = ee.Image('JRC/GSW1_0/GlobalSurfaceWater')
.select('occurrence')
.unmask(0)
.max(jrcData0)
.lt(50); // mask for persistent water (more than 50% of the time)
var floodedPixels = diffThresholded.updateMask(waterMask);
4. We want the flooded areas in BigQuery, so let’s convert flooded pixel data to vector format.
// Convert the patches of pixels to polygons (vectors).
var vectors = floodedPixels.reduceToVectors({
geometry: aoi,
scale: 10,
geometryType: 'polygon',
eightConnected: false // only connect if pixels share an edge
});
// Eliminate large features in the dataset.
var MAX_AREA = 500 * 1000; // m^2
vectors = vectors.map(function (f) {
return f.set('area', f.geometry().area(10));
}).filter(ee.Filter.lt("area", MAX_AREA));
5. Display the flooded areas on the map:
Map.centerObject(aoi, 13); // display our AOI at zoom level 13
Map.addLayer(before, {min: -30, max: 0}, 'Before rains');
Map.addLayer(after, {min: -30, max: 0}, 'After rains');
Map.addLayer(diffSmoothed, {min: -10, max: 10}, 'Difference', false);
Map.addLayer(vectors, {color: 'blue'}, 'Flooded areas');
6. This is the big moment, where the new Earth Engine to BigQuery connector simplifies the export to a single `Export.table.toBigQuery()` call!
Export.table.toBigQuery({
collection: vectors,
description:'ee2bq_export_polygons',
// Update with your existing project and dataset names, and a table name.
table: 'your_project.your_dataset.your_table'
});
7. Click the "Run" button in the Code Editor to execute the script. Doing so will add a task to the "Unsubmitted tasks" section of the Tasks tab (see below). To start the task, click the "Run" button on the task itself (see below).
8. Wait for the task to complete and navigate to the exported table by clicking the “Open in BigQuery” button.
Identify flooded road segments in BigQuery
At this point, the flooded polygons are now in BigQuery, where they can be used in combination with the many publicly available datasets that are hosted there. The public "planet_ways" dataset from OpenStreetMap has information about road segments, which we'll combine with the data we exported from Earth Engine.
Select the polygons from their dataset as `flood_poly`
Join the result with the intersecting road polygons from OpenStreetMap data.
Once you have updated the following query with your dataset and table name, run it in the BigQuery UI.
SELECT
*
FROM (
-- query 1 - find all the flooding areas
SELECT
geo AS flood_poly
FROM
`your_dataset.your_table`) t1
JOIN (
-- query 2 - find all the highways in Open Street Map - https://wiki.openstreetmap.org/wiki/BigQuery_dataset#Query_2:_hospitals_with_no_phone_tag
SELECT
id,
version,
changeset,
osm_timestamp,
geometry as road_geometry
FROM
`bigquery-public-data.geo_openstreetmap.planet_ways` planet_ways,
planet_ways.all_tags AS all_tags
WHERE
-- this tag catches all types of roads https://wiki.openstreetmap.org/wiki/Map_features
all_tags.key = 'highway' )
ON
ST_INTERSECTS(flood_poly, road_geometry)
Visualise the results in GeoViz
Check the result of the query by creating a visualisation in GeoViz. Click on “Explore Data” and then select “Explore with GeoViz”.
In GeoViz, click “Run” to load data into the system, then select a field to display. To display the flooded road segments, choose "road_geometry" as the geometry column:
What comes next?
To continue your adventure, Google Maps Platform recommend:
Make it your own: adjust the area of interest and the time of the before/after layers to customise this example to your needs.
Using BI tools: Perform a similar visualisation using filled polygon and linestring maps in Looker Studio.
Applying ML techniques: Run classification in Earth Engine or clustering in BigQuery.
Joining with additional datasets: Add a time-series of weather data to each polygon before exporting.
We believe that geospatial analyses are key to addressing global challenges, and this feature is a step towards making that easier.
Want to learn more about the BigQuery export? Get in touch with us today.