Aggregate st_geometry and store the result

[yannmiquel]

Hi,

I need to perform an aggregation of polygons (ST_UnionAggr). The calculation takes a few seconds so I need to store the results.

Here are some thoughts :

• Calculation view + transaction-based cache invalidation : I don't see how to aggregate the st_geometry data type, and if the CV is based on a view, the transaction cache is not available.
• SQL View : I don't see the transaction-based cache invalidation in the alter view statement.

Right now, the only solution I came up to is to create a view with the group by, then put this view into a flowgraph to store the result, and sync. the FG with the transactions (1 batch per day).

Does anyone see a better way to do this ?

Regards,

Yann

[mfath · Accepted solution]

Hej Miguel,

if you are loading new data once a day and you have the pipeline under control, I'd say running the ST_UNIONAGGR() aggregation as part of the ingestion pipeline and materializing the aggregated data in a separate table is a good option.

Conceptually, the "dynamic result cache" would be the right option, but it only supports standard aggregations like sum, min, max etc.

Regards, Markus