Feature: Spatial Aggregate Functions

[carterhughes]

It would be particularly helpful to implement spatial aggregate functions that are an aggregate variant of duckdb spatial supported geometry construction functions. Here are examples outlined by PostGIS: https://postgis.net/docs/PostGIS_Special_Functions_Index.html#PostGIS_Aggregate_Functions. Also consideration for an aggregate variant of St_Intersection, which is not implemented in PostGIS.

[Maxxen]

Hi! Thanks for the suggestion. There are some remnants of some old implementations of e.g st_union in the code already, but there's two issues why I hesitated to go further with them.

1. DuckDB cannot overload aggregates and scalars on name yet, so we would have to break standards and call them e.g. st_union_agg or st_kmeans_agg. We could of course add an alias in the future, so this is not really a big problem.
2. When I tested them they turned out to be very slow, since we don't have spatial indexes and can't do any efficient pruning yet.

DuckDB's aggregate code in general is pretty complicated since we need to be able to aggregate out-of-core, which limits flexibility in implementing new aggregates. But this is something we're aware of and are looking into.

Thanks for the suggestion on ST_Intersection as well. I'll see if I can get some "naive" aggregate implementations in next release.

[cboettig]

🎉 🎉 🎉

[aborruso]

Hi @Maxxen how to use these Aggregate Functions and Spatial Join Operator? Should I do the extension update?

Thank you so much for these wonderful news

[Maxxen]

Hey @aborruso I thought the nightly build just hadn't finished yet, but I just realized we haven't actually been deploying any development builds for v1.2.2 due to a misconfiguration on my part, I've fixed it in #550, as soon as this CI action turns green you should be able to force install spatial from core_nightly; to get the latest build with the new spatial join operator.

As for aggregates, I added a bunch of new coverage aggregates for v1.2.2, went through some old issues and figured I might as well close this since we've had most of the other common aggregates for quite a while now. There still some missing though, most notably ST_KMeans and ST_DBSCAN, but I'll look into adding them soon!