# How to get the intersection of an array of polygons in BigQuery

I have a table with POLYGON geometries classified in groups of 2,3,4... features/rows. They can or cannot intersect between each other. This subsample of three features could be a good example: The idea is to get a single row with the intersection geometry (highlighted in red). Bear in mind that the result could be a single polygon (as in the image) or a multipolygon, in case more than two combinations of geometries are intersecting. That's what I need to be recursive, because I do not really know how many geometries could be olverpping.

I would like to apply Big Query ST_Intersection function to an array of geometries. I am trying to replicate this PostGIS function in BigQuery without success. I am not really familiar with UDF scripting in Big Query. This is the piece of code I need to fix:

``````create or replace function ST_IntersectionArray(geoms array <geography>) returns geometry as (
LOOP
SET count = 0;
SET tmpGeom = geoms[OFFSET(0)];
IF count <= geoms.length THEN
SET count = count + 1;
SET tmpGeom = st_intersection(tmpGeom, geoms[OFFSET(count)]);
ELSE
SELECT tmpGeom
BREAK;
END IF;
END LOOP;
)
``````

At the moment, the UI is complaining about the `SET` keyword wrongly positioned.

• Could you give small example of input data, and desired shape of output? Do you have rows with arrays of polygons, or just rows of polygons? – Michael Mar 16 at 22:34

Note that the PostGIS function you have here finds a common intersection of all polygons, not pair-wise intersections. So for the input of the three polygons in the sample image, result would be EMPTY rather than red rectangle - since the third polygon does not intersect anything.

If this is what you really want - here is a ready recipe for such a function, see explanation at https://mentin.medium.com/invert-polygons-for-fun-and-new-functionality-41b29a353cf4:

``````create temp function ST_Invert(g GEOGRAPHY) AS  (
ST_Difference(ST_GeogFromText('fullglobe'), g)
);

create temp function ST_Intersection_Agg(poly GEOGRAPHY) AS  (
ST_Invert(ST_Union_Agg(ST_Invert(poly)))
);
``````

On the other hand, if you want pair-wise intersections, use a self-JOIN to find all intersections, something along the line:

``````select t1.id as id1, t2.id as id2, st_intersection(t1.geom, t2.geom) as intersect
from table t1, table t2
where st_intersects(t1.geom, t2.geom) and t1.id < t2.id;
``````

and if you want a union of all pair-wise intersections, just add `st_union_agg`:

``````select st_union_agg(intersect) as intersec
from (
select st_intersection(t1.geom, t2.geom) as intersect
from table t1, table t2
where st_intersects(t1.geom, t2.geom) and t1.id < t2.id;
)
``````

P.S. In BigQuery, functions cannot have variables, only procedures can. But this iteration-style programming is very slow, what you should be using is set-based SQL queries like above.