How to use ST_UNION in BigQuery-CodePudding

I have a table in BigQuery, called project.dataset.table. This table, among some other columns, has an ourpolygons column, with datatype GEOGRAPHY.

I am trying to apply ST_UNION to the ourpolygons column, to get a multipolygon, a union of all our polygons. ST_UNION is a Geography function in BigQuery.

Using this answer, I tried:

SELECT ST_UNION(ourpolygons) FROM (SELECT ourpolygons FROM `project.dataset.table`) AS multipolig;

I get the error:

No matching signature for function ST_UNION for argument types: GEOGRAPHY. Supported signatures: ST_UNION(GEOGRAPHY, GEOGRAPHY); ST_UNION(ARRAY<GEOGRAPHY>) at [1:8]

I also tried:

SELECT ST_UNION(ourpolygons) FROM `project.dataset.table` AS multipolig;

Giving me the error:

No matching signature for function ST_UNION for argument types: GEOGRAPHY. Supported signatures: ST_UNION(GEOGRAPHY, GEOGRAPHY); ST_UNION(ARRAY<GEOGRAPHY>) at [1:8]

Eventually, I would put the result into a new table, with one row: the union.

How do I select all polygons from an existing table and create their union?

CodePudding user response：

You should use ST_UNION_AGG instead of ST_UNION

ST_UNION is to make a union horizontally in your table: when you have a column with an array of geography object that you want to transform into a single one, or two columns of geography objects that you want to merge into two. At the end of the operation, your table has the same number of rows.

ST_UNION_AGG is to make a union vertically: you have one column of geography objects that you want to aggregate into a single one (perhaps per group..) At the end of the operation, your rows have been aggregated into only one row (or the number of groups, if you have a GROUP BY)