关于BigQuery ST_SIMPLIFY返回GEOMETRYCOLLECTION而非POLYGON的咨询

I've run into this exact issue before, and it boils down to key differences between BigQuery's spatial function logic, Shapely's behavior, and the inherent complexity of OSM polygon data. Here's a breakdown:

Core Reasons #

• Different handling of edge/invalid cases
  BigQuery's ST_SIMPLIFY uses the Douglas-Peucker algorithm, but it prioritizes preserving all components of the original geometry—even if simplification turns parts of the polygon into lines, points, or invalid shapes. For example:

  • A tiny island (inner ring) in your OSM polygon might get simplified down to a line or point instead of a small polygon.
  • A polygon's boundary might lose enough vertices that it's no longer a closed shape, becoming a linestring instead.
    Instead of automatically filtering or fixing these invalid components, BigQuery wraps them into a GEOMETRYCOLLECTION to retain all raw simplification results.

  Shapely's simplify function, by contrast, is optimized for returning valid, OGC-compliant geometries. It automatically handles edge cases like this: it'll repair near-closed shapes, filter out non-polygon components, or merge valid parts back into a single polygon/multipolygon without explicit input from you.

• OSM data complexity exacerbates the issue
  OSM polygons often have dense vertices, nested inner rings (holes/islands), and minor imperfections like near-self-intersections or duplicate vertices. When you apply aggressive simplification, these complex structures are more likely to break down into non-polygon geometries—and BigQuery doesn't clean these up for you by default.

• Design priorities differ
  BigQuery's spatial tools are built to be flexible and preserve raw data, giving you full control over post-processing. Shapely, as a GIS-focused library, leans into usability by handling implicit cleanup to ensure you get a usable, valid geometry out of the box.

Solutions to Fix the Mixed-Type Output #

Here are a few approaches to get valid polygon/multipolygon results from BigQuery's simplification:

1. Filter and aggregate valid polygons from the collection
   Extract only the polygon components from the GEOMETRYCOLLECTION and merge them into a single MULTIPOLYGON:

   SELECT
     ST_UNION_AGG(valid_polygons) AS simplified_multipolygon
   FROM (
     SELECT
       -- Extract each geometry from the collection
       ST_GEOMETRYN(ST_SIMPLIFY(original_geom, 10), n) AS valid_polygons
     FROM
       your_osm_table,
       -- Generate index numbers to iterate through the collection
       UNNEST(GENERATE_ARRAY(1, ST_NGEOMETRIES(ST_SIMPLIFY(original_geom, 10)))) AS n
     WHERE
       -- Only keep polygon-type geometries
       ST_GEOMETRYTYPE(ST_GEOMETRYN(ST_SIMPLIFY(original_geom, 10), n)) = 'POLYGON'
   )
   

2. Preprocess OSM data before simplification
   Clean up invalid or complex parts of your original polygon first to reduce the chance of mixed-type outputs:

   SELECT
     ST_SIMPLIFY(
       -- Fix invalid geometries and remove tiny holes that cause issues
       ST_REMOVEHOLES(ST_MAKEVALID(original_geom), 100)
     , 10) AS simplified_valid_geom
   FROM your_osm_table
   

   Adjust the ST_REMOVEHOLES threshold (100 in this example) to match your data's scale—it removes holes smaller than the specified area.

3. Tweak the simplification threshold
   If you're using a very high tolerance value, try reducing it. A smaller threshold will preserve more vertices, making it less likely that polygons break down into lines or points.

内容的提问来源于stack exchange，提问作者y0j0