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 using ST_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 

