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
I-1
No new files.
I-2
No new commands.
I-3
No new tools.
I-4
add aggregations for spatial points. Shall support normal (full table), group by and window functions.
I-5
New error for cases where SRS changes during aggregation.