Monday, April 15, 2024
No menu items!
HomeCloud ComputingA connector to bring Earth Engine and BigQuery closer together for geospatial...

A connector to bring Earth Engine and BigQuery closer together for geospatial analytics

We’re 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 optimized for processing large tabular datasets. This new connection is our 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, we 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”) polarization, sending vertically polarized light, and measuring the vertically polarized light that’s returned.

Note: the complete Earth Engine script is available here: https://code.earthengine.google.com/df33632277672cd9bce4295e26a11bbf

1. Load the Code Editor and define the area of interest and the filtered collection:

code_block[StructValue([(u’code’, u’// The area of interest (“AOI”) polygon, can also be imported or hand-drawn.rnvar aoi = ee.Geometry.Polygon(rn [[[-2.92, 54.10],rn [-2.92, 53.99],rn [-2.67, 53.99],rn [-2.67, 54.10]]], null, false);rnrn// Load the Sentinel-1 collection (log scaling, VV co-polar).rnvar collection = ee.ImageCollection(‘COPERNICUS/S1_GRD’)rn .filterBounds(aoi)rn .filter(ee.Filter.listContains(‘transmitterReceiverPolarisation’, ‘VV’))rn .select(‘VV’);’), (u’language’, u’lang-py’), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3ee691f8ed90>)])]

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.

code_block[StructValue([(u’code’, u”// Smooth the data to remove noise.rnvar SMOOTHING_RADIUS_METERS = 100;rn// Filter by date (before and after)rnvar before = collection.filterDate(‘2017-11-01’, ‘2017-11-17’)rn .mosaic()rn .focalMedian(SMOOTHING_RADIUS_METERS, ‘circle’, ‘meters’);rnvar after = collection.filterDate(‘2017-11-18’, ‘2017-11-23’)rn .mosaic()rn .focalMedian(SMOOTHING_RADIUS_METERS, ‘circle’, ‘meters’);rnrn// Threshold smoothed radar intensities to identify areas with standing water.rnvar DIFF_THRESHOLD_DB = -3;rnvar diffSmoothed = after.subtract(before);rnvar diffThresholded = diffSmoothed.lt(DIFF_THRESHOLD_DB);”), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3ee693c62390>)])]

3. Next, use the Global Surface Water dataset to remove persistent surface water (like lakes, rivers, etc.) from the result:

code_block[StructValue([(u’code’, u”// Remove global surface water (oceans, lakes, etc.).rnvar jrcData0 = ee.Image(‘JRC/GSW1_0/Metadata’)rn .select(‘total_obs’)rn .lte(0);rnvar waterMask = ee.Image(‘JRC/GSW1_0/GlobalSurfaceWater’)rn .select(‘occurrence’)rn .unmask(0)rn .max(jrcData0)rn .lt(50); // mask for persistent water (more than 50% of the time)rnrnvar floodedPixels = diffThresholded.updateMask(waterMask);”), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3ee693c62950>)])]

4. We want the flooded areas in BigQuery, so let’s convert flooded pixel data to vector format.

code_block[StructValue([(u’code’, u’// Convert the patches of pixels to polygons (vectors).rnvar vectors = floodedPixels.reduceToVectors({rn geometry: aoi,rn scale: 10,rn geometryType: ‘polygon’,rn eightConnected: false // only connect if pixels share an edgern});rn// Eliminate large features in the dataset.rnvar MAX_AREA = 500 * 1000; // m^2rnvectors = vectors.map(function (f) {rn return f.set(‘area’, f.geometry().area(10));rn}).filter(ee.Filter.lt(“area”, MAX_AREA));’), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3ee693c62450>)])]

5. Display the flooded areas on the map:

code_block[StructValue([(u’code’, u”Map.centerObject(aoi, 13); // display our AOI at zoom level 13rnMap.addLayer(before, {min: -30, max: 0}, ‘Before rains’);rnMap.addLayer(after, {min: -30, max: 0}, ‘After rains’);rnMap.addLayer(diffSmoothed, {min: -10, max: 10}, ‘Difference’, false);rnMap.addLayer(vectors, {color: ‘blue’}, ‘Flooded areas’);”), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3ee693c62e50>)])]

6. This is the big moment, where the new Earth Engine to BigQuery connector simplifies the export to a single `Export.table.toBigQuery()` call!

code_block[StructValue([(u’code’, u”Export.table.toBigQuery({rn collection: vectors,rn description:’ee2bq_export_polygons’,rn // Update with your existing project and dataset names, and a table name.rn table: ‘your_project.your_dataset.your_table’rn});”), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3ee693c62ed0>)])]

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 our 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.

code_block[StructValue([(u’code’, u”SELECTrnrn *rnrnFROM (rnrn — query 1 – find all the flooding areasrnrn SELECTrnrn geo AS flood_polyrnrn FROMrnrn `your_dataset.your_table`) t1rnrnJOIN (rnrn — query 2 – find all the highways in Open Street Map – https://wiki.openstreetmap.org/wiki/BigQuery_dataset#Query_2:_hospitals_with_no_phone_tagrnrn SELECTrnrn id,rnrn version,rnrn changeset,rnrn osm_timestamp,rnrn geometry as road_geometryrnrn FROMrnrn `bigquery-public-data.geo_openstreetmap.planet_ways` planet_ways,rnrn planet_ways.all_tags AS all_tagsrnrn WHERErnrn — this tag catches all types of roads https://wiki.openstreetmap.org/wiki/Map_featuresrnrn all_tags.key = ‘highway’ ) rnrnONrnrn ST_INTERSECTS(flood_poly, road_geometry)”), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3ee691f8edd0>)])]

Visualize the results in GeoViz

Check the result of the query by creating a visualization 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, we recommend:

Make it your own: adjust the area of interest and the time of the before/after layers to customize this example to your needs.

Using BI tools: Perform a similar visualization 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.

Learning more about BigQuery export: See the documentation: https://developers.google.com/earth-engine/guides/exporting_to_bigquery

Looking forward, we’re excited to see how people use this feature to make new discoveries and solve important problems. We believe that geospatial analyses are key to addressing global challenges, and this feature is a step towards making that easier.

Watch this space. ⁛

Cloud BlogRead More

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments