Extra geography simplification in BigQuery

  • it smoothes the edges of each shape using Douglas-Peucker algorithm, creating simpler edges with error up to the given tolerance
  • it drops any resulting shapes smaller than tolerance in diameter
  • First, we need to split geometry into parts, we’ll use ST_Dump for that.
  • Diameter is the max distance between two points of a shape, so we can use ST_MaxDistance(g, g) function, passing same object as first and second argument.
  • A tiny optimization: since all points (0-dimension objects) have zero diameter, we can simply drop all of them, without even calling this function.
  • Finally, we need to merge all the remaining parts together, using ST_Union_Agg.
CREATE TEMP FUNCTION ST_DropSmallObjects(
geo GEOGRAPHY,
tolerance FLOAT64) AS
((
WITH parts AS (
-- split into parts
SELECT ST_Dump(geo) g
),
filtered AS (
-- select large enough parts
SELECT g2 FROM parts p, UNNEST(p.g) g2
WHERE ST_Dimension(g2) > 0
AND ST_MaxDistance(g2, g2) > tolerance
)
-- merge them together
SELECT ST_Union_Agg(g2)
FROM filtered
));

--

--

--

Hi, I'm TL of BigQuery Geospatial project. Posting small recipes and various notes for BQ Geospatial users.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Easily call a method after a time delay in Godot

10+ Best Angular JS Tutorials For Beginners — Learn Angular Online

React Js | Why React Shines

React 18 Features

Programmatically sign in with Facebook into a web site

Getting Started with Redux Saga

The mystery of the missing ADFS OAuth JWT claims

Why React is Cooler than Others || TT

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Michael Entin

Michael Entin

Hi, I'm TL of BigQuery Geospatial project. Posting small recipes and various notes for BQ Geospatial users.

More from Medium

Overcoming large-scale geospatial analysis using Google BigQuery

Importing Big Data in BigQuery with Data Transfer Service and Terraform

Load Data into GCP BigQuery Table using pandas DataFrame

Spatial Autocorrelation and Moran's I in SQL and BigQuery