IssueTracker

Hello BQ devs!

Thank you for such a wonderful tool!

What you would like to accomplish:

I'd like to do ST_INTERSECT on more than two geographies at a time, just like it is possible to do union with ST_UNION/ST_UNION_AGG.

How this might work:

SELECT ST_INTERSECT(ARRAY_AGG(polygon)) AS intersection  FROM (...)

or

SELECT ST_INTERSECT_AGG(polygon) AS intersection  FROM (...)

If applicable, reasons why alternative solutions are not sufficient:

My current workaround is a javascript UDF which is passed an array of WKT strings and computes the intersection using TURF library. However, in some edge cases turf.intersect throws an error. If I wrap failing call to try/catch I'm getting Resources exceeded during query execution: UDF out of memory., which is probably related to some V8 memory leak, like here. In my case polygons have small number of nodes (around 20) and max number of polygons is about few hundreds, so explicit UDF limits are unlikely the issue here. Switching to other library does not guarantee that there will be no similar issues. So purely-BQ way to do this would be much better.

Other information (workarounds you have tried, documentation consulted, etc):

There seem to be no way to implement reduce semantics in a single query, that is one can not repeatedly apply a function (ST_INTERSECT here) to elements of a sequence and an accumulator, so ST_INTERSECT function is not generalizable by means of the language itself. Solutions based on LOOP will incur additional costs (as the table will be scanned multiple times) and are unnecessarily complicated for such a trivial task. I imagine one can generate a string with nested calls to ST_INTERSECT and run it using EXECUTE IMMEDIATE, but that probably has its limitations too.

Cheers,
Sergey