search

duckdb / duckdb_spatial

MIT License
478 stars 35 forks source link

Feature: Spatial Aggregate Functions #71

Open carterhughes opened 1 year ago

carterhughes commented 1 year ago

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 commented 1 year ago

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.