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
));

--

--

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

151 Followers

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