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.