How to group-by to get MultiPoint/GeometryCollection in MySQL? 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
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.