Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 43.3Mb
PDF (A4) - 43.4Mb
Man Pages (TGZ) - 297.2Kb
Man Pages (Zip) - 402.4Kb
Info (Gzip) - 4.3Mb
Info (Zip) - 4.3Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  / ...  / Functions and Operators  / Spatial Analysis Functions  /  Spatial Aggregate Functions

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[over_clause], representing an optionalOVER clause.over_clause 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 theDISTINCT 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 ifover_clause is present.over_clause 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 ofover_clause together withDISTINCT.

    ST_Collect() handles its arguments as follows:

    • NULL arguments are ignored.

    • If all arguments areNULL 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), anER_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 narrowestMultiXxx orGeometryCollection value possible, with the result type determined from the non-NULL geometry arguments as follows:

      • If all arguments arePoint values, the result is aMultiPoint value.

      • If all arguments areLineString values, the result is aMultiLineString value.

      • If all arguments arePolygon 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 nothingmysql> 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)) |+-------------------------------+

    This function was added in MySQL 8.0.24.