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

Question

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 Entin, Mar 16 '21 at 22:34

Michael Entin · Accepted Answer · 2021-03-17T19:53:43.847

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.

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

1 Answers1