How big geometry are best for BigQuery performance

Question

I make a geospatial analysis, in Google BigQuery, which mostly includes making difference (I mean ST_Difference) between parcels geometry and some objects like woods, buildings etc. Some of objects I need to exclude are quite big, like 5 000 000 square meters. I used a grid (100mx100m) to cut them to smaller pieces, and that makes my analysis much quicker, about 50 times quicker. What is the best size of grid to use to gain best performance?

thats my query:

select 
w.id,
st_difference(st_union_agg(w.geom_zakwalifikowana), st_union_agg(st_buffer(g.geom, bufor_wewn))) geom_bdot 
from 
model.obszar_analizy w, 
bdot.polska_300m_siatka g 
where st_dwithin(w.geom_zakwalifikowana, g.geom, bufor_wewn )
and g.kod in unnest(lista_bdot)
group by w.id_dzialki;

model.obszar_analizy is a table with parcels covering a given area (500-1000 square km area).

bdot.polska_300m_siatka is a table with topographic objects (e.g. houses, roads, forests, rivers, etc.), some of them were large or long, so I made an intersection with a grid of 100 by 100 meters squares.

I want to remove from the parcels table those parts of the geometry that are too close to the topographic object.

Answer 1

I think this is not the right question to ask for two reasons:

1. Performance of spatial operations depends more on shape, complexity and relationship between objects rather than on their linear size,
2. I'm not sure the problem is even related to the performance of ST_Difference function.

First, on the second topic, I would look at the query performance stats, and also try to see what part of the query is taking most of the time. I suspect but obviously cannot verify without access to the data, that the bottleneck here is spatial join. Try removing ST_Difference and run simpler query, like

select w.id, count(g.geom)
from  
  model.obszar_analizy w, 
  bdot.polska_300m_siatka g 
where st_dwithin(w.geom_zakwalifikowana, g.geom, bufor_wewn )
and g.kod in unnest(lista_bdot)
group by w.id;

If it runs in about the same time, then it is about JOIN performance. If this simpler query is much faster, then it is actually ST_Difference or ST_Buffer or ST_Union issue - add functions one by one to see which one causes the performance drop.

Re JOIN the performance depends a lot on shape of the objects - approximately square or round shapes are the best, and long narrow objects are the worst and are worth cutting into shorter segments.

Also, the buffer size is a natural "good" size here, it does not make sense to cut into pieces smaller than the buffer size.