WL#13454: ST_Collect - Spatial aggregations

Affects: Server-8.0 — Status: Complete

ST_Collect, a spatial (GIS) aggregate function, with windowing support, transforming geometries into multigeometries. The inspiration for this worklog is taken from PostGIS. https://postgis.net/docs/ST_Collect.html

A use case for this is to make a single point cloud (multpoint) from a column of points.

Syntax: adding this to sum_expr where our aggregations live

sum_expr: 
...
        | ST_COLLECT_SYM '(' in_sum_expr ')' opt_windowing_clause
        | ST_COLLECT_SYM '(' DISTINCT in_sum_expr ')' opt_windowing_clause
...

Example gathering the positions where production happens from a table:

create table sales_with_location 
( running_number integer not null auto_increment, 
year integer, 
country varchar(256), 
product varchar(256), 
profit double, 
capital_position Geometry, 
primary key ( running_number));

insert into sales_with_location 
( year , country ,product , profit , capital_position ) values
( 2000 , "Finland" , "Computer"   ,   1500 ,st_geomfromtext('point(60 -24)',4326)),
( 2000 , "Finland" , "Phone"      ,    100 ,st_geomfromtext('point(60 -24)',4326)),
( 2001 , "Finland" , "Phone"      ,     10 ,st_geomfromtext('point(60 -24)',4326)),
( 2000 , "India"   , "Calculator" ,     75 ,st_geomfromtext('point(28 -77)',4326)),
( 2000 , "India"   , "Calculator" ,     75 ,st_geomfromtext('point(28 -77)',4326)),
( 2000 , "India"   , "Computer"   ,   1200 ,st_geomfromtext('point(28 -77)',4326)),
( 2000 , "USA"     , "Calculator" ,     75 ,st_geomfromtext('point(38  77)',4326)),
( 2000 , "USA"     , "Computer"   ,   1500 ,st_geomfromtext('point(38  77)',4326)),
( 2001 , "USA"     , "Calculator" ,     50 ,st_geomfromtext('point(38  77)',4326)),
( 2001 , "USA"     , "Computer"   ,   1500 ,st_geomfromtext('point(38  77)',4326)),
( 2001 , "USA"     , "Computer"   ,   1200 ,st_geomfromtext('point(38  77)',4326)),
( 2001 , "USA"     , "TV"         ,    150 ,st_geomfromtext('point(38  77)',4326)),
( 2001 , "USA"     , "TV"         ,    100 ,st_geomfromtext('point(38  77)',4326));
SELECT st_astext(st_collect(distinct capital_position) ) as t FROM sales_with_location;

+---------------------------------------+
| t                                     |
+---------------------------------------+
| MULTIPOINT((60 -24),(28 -77),(38 77)) |
+---------------------------------------+
1 row in set (0.00 sec)

If all the geometries are of the same type and this is points, linestrings or polygons the result will be a Multipoint, Multilinestring or a Multipolygon. Otherwise a Geometrycollection is returned.

F-1: ST_Collect SHALL only return NULL if all elements are NULL or the aggregate is empty.
F-2 a): If the elements in an aggregate is of different SRSs, ST_Collect MUST raise ER_GIS_DIFFERENT_SRIDS_AGGREGATION.
F-2 b): If all the elements in an aggregate is of same SRS, ST_Collect MUST return a result in that SRS.
F-3: If the geometry elements in an aggregate are in an undefined SRS, ST_Collect MUST raise ER_SRS_NOT_FOUND during function evaluation.
F-4: ST_Collect shall support simple table aggregations
F-5: ST_Collect shall support group by, which is given by aggregation machinery
F-6: ST_Collect shall support window functions
F-6 a): ST_Collect shall support ordering in window functions (Window function machinery gives support for the ordering in the windows, partitions, ranges and rows as long as the interface is done correctly.)
F-7: A Null in the field shall leave the value unaffected. Aggregating point1,NULL,point2 shall result in Multipoint(point1,point2).
F-8 a): Shall support DISTINCT in aggregates
F-8 b): Shall support DISTINCT in over windows
F-9: ST_Collect SHALL give the result as the narrowest Multigeometry or Geometrycollection it can.
F-9 a): If an aggregate is over only Points the result is Multipoint
F-9 b): If an aggregate is over only Linestrings the result is Mulitlinestring
F-9 c): If an aggregate is over only Polygons the result is Mulitpolygon
F-9 d): Otherwise the result is a Geometrycollection