How to group-by to get MultiPoint/GeometryCollection in MySQL?

Question

I have a table x like this:

Columns:

SubjectID int(11)
Timestamp bigint(20) 
fix_geom point

When i try to group my fix_geom column to a MultiPoint (or GeometryCollection) by SubjectID, like:

SELECT SubjectID, ST_AsText(MultiPoint(fix_geom))
FROM x
WHERE SubjectID = 100
GROUP BY SubjectID;

i get a table with a multipoint geometry, but it contains just one single Point instead of the many i'd liked to see there...:

Can anybody tell me what i'm doing wrong?

Cheers and many thanks in advance, Olaf

Answer 1

As Evan Carroll said in a comment: Unlike PostGIS, spatial aggregates are not available for MySQL.

The workaround left in a comment by danblack works:

It looks like you want MultiPoint to be an aggregate function, which is isn't. You'll probably need something like ST_GeomFromText(CONCAT('MultiPoint(', GROUP_CONCAT(...), ')')), where ... gets an X Y string from fix_geom.

Answer 2

We can use aggregate function ST_Collect() since MySQL 8.0.24.

It supports DISTINCT and OVER clause, and generates automagically a MULTIPOINT :

SELECT ALL
    SubjectID,
    ST_Collect(fix_geom),
    ST_AsText(ST_Collect(fix_geom)) -- MULTIPOINT((...),(...),...)
FROM ...
WHERE ...
GROUP BY 1

If you store points separately and/or need a GeoJSON :

SELECT ALL
    SubjectID,
    ST_Collect(Point(latitude, longitude)),
    ST_AsGeoJSON(ST_Collect(Point(latitude, longitude))) -- {"type": "MultiPoint", "coordinates": [...]}
FROM ...
WHERE ...
GROUP BY 1

https://dev.mysql.com/doc/refman/8.0/en/spatial-aggregate-functions.html