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?
>Solution :
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)