Is there an easy way to perform aggregation version of binary geospatial functions like ST_UNION?
If I have 1024 rows of data with geospatial column g, and I want to compute the overall ST_UNION (g) for all rows, I have two choices:
1) If g is a heterogeneous set of simple geometries, e.g. all POLYGON, then I can convert g to wkt, use LISTAGG to roll those into a huge MULTIPOLYGON wkt, then convert that MULTIPOLYGON to a new geometry "multipolygon_g", and finally run a self-union to simplify, e.g. ST_UNION (multipolygon_g,multipolygon_g)
2) (typically handled via script): Order records and number with N in {1..1024}, then join even N rows to odd N rows on even.N=odd.N+1, and compute 512 new rows with g=ST_UNION(even.g,odd.g) . Repeat for N of 512,256,128,... until just one row remains.
Both approaches are tedious, and anytime I need to solve this problem, I find myself lamenting the lack of:
SELECT STV_UNION_AGG(g) FROM t;
Is there a simpler approach that I'm overlooking? Might something like STV_UNION_AGG(g), an aggregate version of binary ST_UNION that works for N geometries, be on the Vertica roadmap?