MySQL :: MySQL 8.4 Reference Manual :: 14.16.12 Spatial Aggregate Functions (original) (raw)

14.16.12 Spatial Aggregate Functions

MySQL supports aggregate functions that perform a calculation on a set of values. For general information about these functions, seeSection 14.19.1, “Aggregate Function Descriptions”. This section describes theST_Collect() spatial aggregate function.

ST_Collect() can be used as a window function, as signified in its syntax description by[_`overclause`_], representing an optional OVER clause.overclause is described inSection 14.20.2, “Window Function Concepts and Syntax”, which also includes other information about window function usage.

• ST_Collect([DISTINCT]g) [over_clause]
  Aggregates geometry values and returns a single geometry collection value. With the DISTINCT option, returns the aggregation of the distinct geometry arguments.
  As with other aggregate functions, GROUP BY may be used to group arguments into subsets.ST_Collect() returns an aggregate value for each subset.
  This function executes as a window function if_overclause_ is present.overclause is as described inSection 14.20.2, “Window Function Concepts and Syntax”. In contrast to most aggregate functions that support windowing,ST_Collect() permits use of_overclause_ together withDISTINCT.
  ST_Collect() handles its arguments as follows:
  • NULL arguments are ignored.
  • If all arguments are NULL or the aggregate result is empty, the return value isNULL.
  • If any geometry argument is not a syntactically well-formed geometry, anER_GIS_INVALID_DATA error occurs.
  • If any geometry argument is a syntactically well-formed geometry in an undefined spatial reference system (SRS), an ER_SRS_NOT_FOUND error occurs.
  • If there are multiple geometry arguments and those arguments are in the same SRS, the return value is in that SRS. If those arguments are not in the same SRS, anER_GIS_DIFFERENT_SRIDS_AGGREGATION error occurs.
  • The result is the narrowestMulti_`Xxx`_ orGeometryCollection value possible, with the result type determined from the non-NULL geometry arguments as follows:
    * If all arguments are Point values, the result is a MultiPoint value.
    * If all arguments are LineString values, the result is aMultiLineString value.
    * If all arguments are Polygon values, the result is aMultiPolygon value.
    * Otherwise, the arguments are a mix of geometry types and the result is aGeometryCollection value.

This example data set shows hypothetical products by year and location of manufacture:

CREATE TABLE product (  
  year INTEGER,  
  product VARCHAR(256),  
  location Geometry  
);  
INSERT INTO product  
(year,  product,     location) VALUES  
(2000, "Calculator", ST_GeomFromText('point(60 -24)',4326)),  
(2000, "Computer"  , ST_GeomFromText('point(28 -77)',4326)),  
(2000, "Abacus"    , ST_GeomFromText('point(28 -77)',4326)),  
(2000, "TV"        , ST_GeomFromText('point(38  60)',4326)),  
(2001, "Calculator", ST_GeomFromText('point(60 -24)',4326)),  
(2001, "Computer"  , ST_GeomFromText('point(28 -77)',4326));  

Some sample queries usingST_Collect() on the data set:

mysql> SELECT ST_AsText(ST_Collect(location)) AS result  
       FROM product;  
+------------------------------------------------------------------+  
| result                                                           |  
+------------------------------------------------------------------+  
| MULTIPOINT((60 -24),(28 -77),(28 -77),(38 60),(60 -24),(28 -77)) |  
+------------------------------------------------------------------+  
mysql> SELECT ST_AsText(ST_Collect(DISTINCT location)) AS result  
       FROM product;  
+---------------------------------------+  
| result                                |  
+---------------------------------------+  
| MULTIPOINT((60 -24),(28 -77),(38 60)) |  
+---------------------------------------+  
mysql> SELECT year, ST_AsText(ST_Collect(location)) AS result  
       FROM product GROUP BY year;  
+------+------------------------------------------------+  
| year | result                                         |  
+------+------------------------------------------------+  
| 2000 | MULTIPOINT((60 -24),(28 -77),(28 -77),(38 60)) |  
| 2001 | MULTIPOINT((60 -24),(28 -77))                  |  
+------+------------------------------------------------+  
mysql> SELECT year, ST_AsText(ST_Collect(DISTINCT location)) AS result  
       FROM product GROUP BY year;  
+------+---------------------------------------+  
| year | result                                |  
+------+---------------------------------------+  
| 2000 | MULTIPOINT((60 -24),(28 -77),(38 60)) |  
| 2001 | MULTIPOINT((60 -24),(28 -77))         |  
+------+---------------------------------------+  
# selects nothing  
mysql> SELECT ST_Collect(location) AS result  
       FROM product WHERE year = 1999;  
+--------+  
| result |  
+--------+  
| NULL   |  
+--------+  
mysql> SELECT ST_AsText(ST_Collect(location)  
         OVER (ORDER BY year, product ROWS BETWEEN 1 PRECEDING AND CURRENT ROW))  
         AS result  
       FROM product;  
+-------------------------------+  
| result                        |  
+-------------------------------+  
| MULTIPOINT((28 -77))          |  
| MULTIPOINT((28 -77),(60 -24)) |  
| MULTIPOINT((60 -24),(28 -77)) |  
| MULTIPOINT((28 -77),(38 60))  |  
| MULTIPOINT((38 60),(60 -24))  |  
| MULTIPOINT((60 -24),(28 -77)) |  
+-------------------------------+