BigQuery ST_SIMPLIFY returns GEOMETRYCOLLECTION instead of POLYGON

user_1683626 0
Viewed 577

I found one issue in BigQuery function ST_SIMPLIFY.

I'm querying big geometries and some stats for them. In case I need to visualize them e.g. in Kepler it is not possible because Kepler do not consume output from BigQuery ST_SIMPLIFY. I analyzed results from ST_SIMPLIFY and found this:

As an input that I used is in all cases POLYGON parsed from OSM. enter image description here

When I call ST_SIMPLIFY I get results of mixed geometry types like POLYGON and GEOMETRYCOLLECTION containing MULTILINESTRINGs, LINESTRINGs, POLYGONs.

enter image description here

Maybe it is not so strange but when I tried to visualize these geometries, they do not make sense. Especially LINESTRINGs inside GEOMETRYCOLLECTIONs like in this geojson

When I tried use these geometries in simplify function in Shapely I got valid results containing only POLYGONs

Why BigQuery ST_SIMPLIFY returns GEOMETRYCOLLECTIONS with geom mixed types instead of simple one POLYGON or MULTIPOLYGON?

To reproduce this issue you can init BQ table from these data

google-bigquery
2 Answers

When simplifying, BigQuery can reduce dimensions of the shape if the lower dimension shape represents the original shape with requested precision.

E.g.

with data as (
select st_geogfromtext(
'polygon((1 1, 2 1, 2 2, 1.5 2, 1.5 3, 1.499 2, 1 2, 1 1))') g
)
select g, st_simplify(g, 10000) s from data

Here the "spike" at the top of the shape is converted into a line, and we get GEOMETRYCOLLECTION(LINESTRING(1.5 2, 1.5 3), POLYGON((1 1, 2 1, 2 2, 1.5 2, 1 2, 1 1))).

Use

with data as (
select st_geogfromtext(
'polygon((1 1, 2 1, 2 2, 1.5 2, 1.5 3, 1.499 2, 1 2, 1 1))') g
)
select st_union(st_dump(st_simplify(g, 10000),2)) s from data

to extract polygon only if you are OK with ignoring such spikes.

user_198800

I wrote BigQuery function that solved my issue. Basically this function ignore all geometries except POLYGON and MULTIPOLYGON. Tested results are possible to visualize in Kepler and other tools

CREATE OR REPLACE FUNCTION
  my_project.repair_simplified_geom(geom ANY TYPE) AS ((
    SELECT
      ST_UNION(ARRAY_AGG(geom))
    FROM
      UNNEST(ST_DUMP(geom)) AS geom
    WHERE
      STARTS_WITH(ST_ASTEXT(geom), 'POLYGON')
      OR STARTS_WITH(ST_ASTEXT(geom), 'MULTIPOLYGON')))

Usage

select my_project.repair_simplified_geom(ST_SIMPLIFY(geometry,100)) as geom from
`admin_levels_svk.admin_levels_3_svk`
user_16836260
Related