WL#13454: ST_Collect - Spatial aggregations

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.