Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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?

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.